生物資訊程式語言應用 Part 7

1. 生物資訊程式語言應用Part 7 Database Management by PHP

2. Database Management and data presentation • Database : MySql • Tools : phpmyadmin • Step 1 : Creating a database and tables in MySql • To create a database • To create a table and to define attributes • Insert data (By program-Perl or manual) • Step 2 : Develop a website for this database • Selection / insertion / deletion / updating • According to user requirements.

3. Perl MySql PHP Flow Chart

4. Management and presentation for PubMed data • Management • Selection • Insertion • Deletion • Updating • Link • Files • Index.php • abstract.php • abstractDel.php • abstractUpd.php • abstractUpdInt.php • abstractIns.php • abstractNew.php

5. Management and presentation for PubMed data • Management • Selection • Insertion • Deletion • Updating • Link • Files • Index.php • abstract.php • abstractDel.php • abstractUpd.php • abstractUpdInt.php • abstractIns.php • abstractNew.php

6. The query frame (index.php) SQL: SHOW COLUMNS FROM article FROM summer;

7. The query frame (index.php) Send two massages (choice, text_query) to article.php Example : To select abstract and key in “interaction”. Setup for DB link SQL Obtain records

8. Selection (article.php) • Select abstract and key in “interaction”. SELECT * FROM article where abstract like '%interaction%'

9. Selection (article.php) (con.)

10. Setup for DB link SQL Obtain records

11. Exercise • article.php • article_html.php • article_php.php • index.php • index_html.php • index_php.php • To build article.php and index.php by *_php.php and *_html.php

12. Manage database by Phpmyadmin. How do we manage the database by phpmyadmin? How do we manage the database by PHP?

15. Insertion Updating Deletiion

16. Insertion

17. Insertion (con.)

18. Updating

19. Deletion

20. Exercise • Use phpmyadmin to insert a record to article table, and write down their executing SQL. • Use phpmyadmin to update a record to article table, and write down their executing SQL. • Use phpmyadmin to delete a record to article table, and write down their executing SQL.

21. Build a new frame for insertion, deletion and updating. (article.php)

22. Exercise • Include insertion, updating, deletion buttons in article.php • You may apply article_IDUtag.php to finish you practice.

23. Management and presentation for PubMed data • Management • Selection • Insertion • Deletion • Updating • Link • Files • Index.php • abstract.php • abstractDel.php • abstractUpd.php • abstractUpdInt.php • abstractIns.php • abstractNew.php

24. Insertion-Abstract Insert frame (article_new.php) • Send two massages (pmid, year, title, abstract) to articleIns.php

25. Exercise • articleNew.php • <FORM action="" name="" method="post"> • What is the target file you want to send these massages?

26. Get massages SQL Setup for DB link Insertion - Insert into MySql(articleIns.php)

27. Exercise • Please to finish insertion step by modifying articleIns.php.

28. Management and presentation for PubMed data • Management • Selection • Insertion • Deletion • Updating • Link • Files • Index.php • abstract.php • abstractDel.php • abstractUpd.php • abstractUpdInt.php • abstractIns.php • abstractNew.php

29. Updating (article.php)

30. Updating (articleUpd.php)

31. Exercise • articleUpd.php • articleUpd_php.php • articleUpd_php.php • To build articleUpd.php and by articleUpd_php.php and articleUpd_php.php

32. Setup for DB link SQL Updating (con.) (articleUpdInt.php)

33. Exercise • Please to finish Updating step by modifying articleUpdIns.php.

34. Management and presentation for PubMed data • Management • Selection • Insertion • Deletion • Updating • Link • Files • Index.php • abstract.php • abstractDel.php • abstractUpd.php • abstractUpdInt.php • abstractIns.php • abstractNew.php

35. Deletion (article.php)

36. Setup for DB link SQL Deletion (con.) (articleDel.php)

37. Exercise • Please to finish Deletion step by modifying articleDel.php.

38. Extract sequences from the NCBI • By user query • http://www.ncbi.nlm.nih.gov/ • By FTP • ftp.ncbi.nlm.nih.gov/refseq/H_sapiens/H_sapiens/protein/protein.fa.gz • ftp.ncbi.nlm.nih.gov/refseq/H_sapiens/H_sapiens/RNA/rna.fa.gz

39. Insert the sequences to database • To construct the table for the sequences. • To use the program to insert the sequences in the table. • Perl code (practice)

40. Exercise • Use phpmyadmin to select , insert , update, delete a record to sequence table, and write down their executing SQL. • Modified the article%.php for the sequences table.

41. Combine article.php and articleNew.php article.php?option=add

42. 分頁

43. 分頁

44. To calculate particular information for sequences • Frequency • 語法 : string substr (string string, int start [, int length]) • 說明 :  • substr( )傳回 string的一部份字串，由參數 start和 length指定。 • 如果 start是正數，傳回的字串將會從 string的第 start個字元開始。 • Example : • <?php •     $rest = substr ("abcdef", 1);       // returns "bcdef"  •     $rest = substr ("abcdef", 1, 3);   // returns "bcd" • ?> • 如果 start是負數，傳回的字串將會從 string結尾的第 start個字開始。

45. To calculate particular information for sequences • CG% • Length • 語法 : int strlen (string str) • 說明 :  • 傳回字串 str的長度。 • Example : • <?php • $DNA_length=strlen($a); • ?>

46. Translate the nucleotide to amino acid • $codons = substr ($sequence, $first, $first+2);

47. Submit a sequence to blast by the interface • http://blast.ncbi.nlm.nih.gov/Blast.cgi?PROGRAM=blastn&BLAST_PROGRAMS=megaBlast&PAGE_TYPE=BlastSearch&SHOW_DEFAULTS=on&LINK_LOC=blasthome

48. Hyperlink for the sequence id • http://www.ncbi.nlm.nih.gov/sites/entrez?db=gene&term= • Example: http://www.ncbi.nlm.nih.gov/sites/entrez?db=gene&term=gi|194473734|ref|NM_181054.2| Homo sapiens hypoxia inducible factor 1, alpha subunit (basic helix-loop-helix transcription factor) (HIF1A), transcript variant 2, mRNA

49. Resources • MySql http://dev.MySql.com/ • Perl http://www.perl.com/ • phpmyadmin http://sourceforge.net/projects/phpmyadmin/ • php http://www.php.net/