Part 7 Database Management by PHP Database Management

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

Database Management and data presentation Database : My. Sql Tools : phpmyadmin Step 1 : Creating a database and tables in My. Sql 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. 2

Flow Chart Perl My. Sql PHP 3

Management and presentation for Pub. Med data Management Selection Insertion Deletion Updating Link Files Index. php abstract. Del. php abstract. Upd. Int. php abstract. Ins. php abstract. New. php 4

Management and presentation for Pub. Med data Management Selection Insertion Deletion Updating Link Files Index. php abstract. Del. php abstract. Upd. Int. php abstract. Ins. php abstract. New. php 5

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

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

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

Selection (article. php) (con. ) 9

Setup for DB link SQL Obtain records 10

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

Manage database by Phpmyadmin. How do we manage the database by phpmyadmin? How do we manage the database by PHP? I K M

13

14

Insertion Updating Deletiion 15

Insertion 16

Insertion (con. ) 17

Updating 18

Deletion 19

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. 20

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

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

Management and presentation for Pub. Med data Management Files Selection Index. php Insertion abstract. php Deletion Updating abstract. Del. php abstract. Upd. Int. php Link abstract. Ins. php abstract. New. php 23

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

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

Insertion - Insert into My. Sql (article. Ins. php) Get massages SQL Setup for DB link 26

Exercise Please to finish insertion step by modifying article. Ins. php. 27

Management and presentation for Pub. Med data Management Files Selection Insertion Deletion Index. php Updating abstract. Upd. php abstract. Upd. Int. php Link abstract. php abstract. Del. php abstract. Ins. php abstract. New. php 28

Updating (article. php) 29

Updating (article. Upd. php) 30

Exercise article. Upd. php article. Upd_php. php To build article. Upd. php and by article. Upd_php. php and article. Upd_php. php 31

Updating (con. ) (article. Upd. Int. php) Setup for DB link SQL 32

Exercise Please to finish Updating step by modifying article. Upd. Ins. php. 33

Management and presentation for Pub. Med data Management Selection Insertion Deletion Updating Link Files Index. php abstract. Del. php abstract. Upd. Int. php abstract. Ins. php abstract. New. php 34

Deletion (article. php) 35

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

Exercise Please to finish Deletion step by modifying article. Del. php. 37

Extract sequences from the NCBI By user query http: //www. ncbi. nlm. nih. gov/ By FTP ftp. ncbi. nlm. nih. gov/refseq/H_sapiens/protein/prot ein. fa. gz ftp. ncbi. nlm. nih. gov/refseq/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

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. 40

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

To calculate particular information for sequences Frequency 語法 : string substr (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個字開始。 44

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

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

Submit a sequence to blast by the interface http: //blast. ncbi. nlm. nih. gov/Blast. cgi? PROGRAM=blastn&BLAST_PRO GRAMS=mega. Blast&PAGE_TYPE=Blast. Search&SHOW_DEFAULTS= on&LINK_LOC=blasthome 47

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|19 4473734|ref|NM_181054. 2| Homo sapiens hypoxia inducible factor 1, alpha subunit (basic helix-loop-helix transcription factor) (HIF 1 A), transcript variant 2, m. RNA 48

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