生物资讯程式语言应用Part 7.ppt

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 webs

2、ite for this database Selection / insertion / deletion / updating According to user requirements.,3,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 abstractI

3、ns.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

4、 summer;,7,The query frame (index.php),Send two massages (choice, text_query) to article.php Example : To select abstract and key in “interaction”.,8,Selection (article.php),Select abstract and key in “interaction”.,SELECT * FROM article where abstract like %interaction%,9,Selection (article.php) (c

5、on.),10,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,Manage database by Phpmyadmin.,How do we manage the database by phpmyadmin? How do we manage the database by PHP?,13,14,15,16,Inserti

6、on,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 dow

7、n 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 Insert

8、ion 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 What is the tar

9、get file you want to send these massages?,26,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 abstr

10、actDel.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,Updating (con.) (articleUp

11、dInt.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

12、,Deletion (article.php),36,Deletion (con.) (articleDel.php),37,Exercise,Please to finish Deletion step by modifying articleDel.php.,Extract sequences from the NCBI,By user query / By FTP /refseq/H_sapiens/H_sapiens/protein/protein.fa.gz ftp.ncbi.nlm.nih.

13、gov/refseq/H_sapiens/H_sapiens/RNA/rna.fa.gz,38,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),39,40,Exercise,Use phpmyadmin to select , insert , update, delete a record to sequence table, and w

14、rite 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,To calculate particular information for sequences,Frequency 語法 :string substr (string string, int start , int length) 說明 : substr( )傳回 string

15、的一部份字串，由參數 start和 length指定。 如果 start是正數，傳回的字串將會從 string的第 start個字元開始。 Example : 如果 start是負數，傳回的字串將會從 string結尾的第 start個字開始。,44,To calculate particular information for sequences,CG% Length 語法 :int strlen (string str) 說明 : 傳回字串 str的長度。 Example : ,45,Translate the nucleotide to amino acid,$codons = subs

16、tr ($sequence, $first, $first+2);,46,Submit a sequence to blast by the interface,/Blast.cgi?PROGRAM=blastn&BLAST_PROGRAMS=megaBlast&PAGE_TYPE=BlastSearch&SHOW_DEFAULTS=on&LINK_LOC=blasthome,47,Hyperlink for the sequence id,/sites/entrez?db=gene&term= Example: http:/www.ncbi.nlm.nih.