1、生物資訊程式語言應用Part 7Part 7Database Management by PHP 1/49DatabaseManagementanddatapresentationDatabase:MySqlTools:phpmyadminStep1:CreatingadatabaseandtablesinMySqlTocreateadatabaseTocreateatableandtodefineattributesInsertdata(Byprogram-Perlormanual)Step2:DevelopawebsiteforthisdatabaseSelection/insertion
2、/deletion/updatingAccordingtouserrequirements.2/492 2FlowChartPerlPHPMySql3/493 3ManagementandpresentationforPubMeddataManagementSelectionInsertionDeletionUpdatingLinkFilesIndex.phpabstract.phpabstractDel.phpabstractUpd.phpabstractUpdInt.phpabstractIns.phpabstractNew.php4/494 4Managementandpresentat
3、ionforPubMeddataManagementSelectionInsertionDeletionUpdatingLinkFilesIndex.phpabstract.phpabstractDel.phpabstractUpd.phpabstractUpdInt.phpabstractIns.phpabstractNew.php5/495 5Thequeryframe(index.php)SQL:SHOWCOLUMNSFROMarticleFROMsummer;6/496 6Thequeryframe(index.php)Sendtwomassages(choice,text_query
4、)toarticle.phpExample:Toselectabstractandkeyin“interaction”.Setup for DB link SQLObtain records7/497 7Selection(article.php)Selectabstractandkeyin“interaction”.SELECT*FROM article where abstract like%interaction%8/498 8Selection(article.php)(con.)9/499 9Setup for DB link SQLObtain records10/491010Ex
5、ercisearticle.phparticle_html.phparticle_php.phpindex.phpindex_html.phpindex_php.phpTobuildarticle.phpandindex.phpby*_php.phpand*_html.php11/491111ManagedatabasebyPhpmyadmin.Howdowemanagethedatabasebyphpmyadmin?HowdowemanagethedatabasebyPHP?12/4913/49131314/491414UpdatingDeletiionInsertion15/491515I
6、nsertion16/491616Insertion(con.)17/491717Updating18/491818Deletion19/491919ExerciseUsephpmyadmintoinsertarecordtoarticletable,andwritedowntheirexecutingSQL.Usephpmyadmintoupdatearecordtoarticletable,andwritedowntheirexecutingSQL.Usephpmyadmintodeletearecordtoarticletable,andwritedowntheirexecutingSQ
7、L.20/492020Buildanew frameforinsertion,deletionandupdating.(article.php)21/492121ExerciseIncludeinsertion,updating,deletionbuttonsinarticle.phpYoumayapplyarticle_IDUtag.phptofinishyoupractice.22/492222ManagementandpresentationforPubMeddataManagementSelectionInsertionDeletionUpdatingLinkFilesIndex.ph
8、pabstract.phpabstractDel.phpabstractUpd.phpabstractUpdInt.phpabstractIns.phpabstractNew.php23/492323Insertion-AbstractInsertframe(article_new.php)Sendtwomassages(pmid,year,title,abstract)toarticleIns.php24/492424ExercisearticleNew.phpWhatisthetargetfileyouwanttosendthesemassages?25/492525Insertion-I
9、nsertintoMySql(articleIns.php)Setup for DB link SQLGet massages26/492626ExercisePleasetofinishinsertionstepbymodifyingarticleIns.php.27/492727ManagementandpresentationforPubMeddataManagementSelectionInsertionDeletionUpdatingLinkFilesIndex.phpabstract.phpabstractDel.phpabstractUpd.phpabstractUpdInt.p
10、hpabstractIns.phpabstractNew.php28/492828Updating(article.php)29/492929Updating(articleUpd.php)30/493030ExercisearticleUpd.phparticleUpd_php.phparticleUpd_php.phpTobuildarticleUpd.phpandbyarticleUpd_php.phpandarticleUpd_php.php31/493131Updating(con.)(articleUpdInt.php)Setup for DB link SQL32/493232E
11、xercisePleasetofinishUpdatingstepbymodifyingarticleUpdIns.php.33/493333ManagementandpresentationforPubMeddataManagementSelectionInsertionDeletionUpdatingLinkFilesIndex.phpabstract.phpabstractDel.phpabstractUpd.phpabstractUpdInt.phpabstractIns.phpabstractNew.php34/493434Deletion(article.php)35/493535
12、Deletion(con.)(articleDel.php)Setup for DB link SQL36/493636ExercisePleasetofinishDeletionstepbymodifyingarticleDel.php.37/493737ExtractsequencesfromtheNCBIByuserqueryhttp:/www.ncbi.nlm.nih.gov/ByFTPftp.ncbi.nlm.nih.gov/refseq/H_sapiens/H_sapiens/protein/protein.fa.gzftp.ncbi.nlm.nih.gov/refseq/H_sa
13、piens/H_sapiens/RNA/rna.fa.gz38/493838InsertthesequencestodatabaseToconstructthetableforthesequences.Tousetheprogramtoinsertthesequencesinthetable.Perlcode(practice)39/493939ExerciseUsephpmyadmintoselect,insert,update,deletearecordtosequencetable,andwritedowntheirexecutingSQL.Modifiedthearticle%.php
14、forthesequencestable.40/494040Combinearticle.phpandarticleNew.phparticle.php?option=add41/494141分頁42/494242分頁43/494343TocalculateparticularinformationforsequencesFrequency語法:stringsubstr(stringstring,intstart,intlength)說明:substr()傳回string一部份字串，由參數start和length指定。假如start是正數，傳回字串將會從string第start個字元開始。Ex
15、ample:假如start是負數，傳回字串將會從string結尾第start個字開始。44/494444TocalculateparticularinformationforsequencesCG%Length語法:intstrlen(stringstr)說明:傳回字串str長度。Example:45/494545Translatethenucleotidetoaminoacid$codons=substr($sequence,$first,$first+2);46/494646Submitasequencetoblastbytheinterfacehttp:/blast.ncbi.nlm.n
16、ih.gov/Blast.cgi?PROGRAM=blastn&BLAST_PROGRAMS=megaBlast&PAGE_TYPE=BlastSearch&SHOW_DEFAULTS=on&LINK_LOC=blasthome47/494747Hyperlinkforthesequenceidhttp:/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|Homosapienshypoxiainduciblefactor1,alphasubunit(basichelix-loop-helixtranscriptionfactor)(HIF1A),transcriptvariant2,mRNA48/494848ResourcesMySqlhttp:/dev.MyS
