第21章PHPMySQL專用接口動(dòng)態(tài)網(wǎng)頁設(shè)計(jì)課件

21.1概述PHP訪問MySQL數(shù)據(jù)庫的步驟建立與MySQL數(shù)據(jù)庫服務(wù)器的連接選擇要訪問的數(shù)據(jù)庫執(zhí)行SQL語句存儲(chǔ)查詢結(jié)果顯示結(jié)果數(shù)據(jù)關(guān)閉MySQL數(shù)據(jù)庫服務(wù)器的連接121.2.PHP與MySQL的連結(jié)與關(guān)閉非永久性連接：

intmysql_connect(string[hostname][:port],string[username],string[password]);非永久性連接關(guān)閉

intmysql_close(連接句柄);永久性連接：可以返回以前連接的ID;無須關(guān)閉；首次建立的連接一直有效。

intmysql_pconnect(string[hostname][:port],string[username],string[password]);2<?php$Server="localhost";$user="root";$pass="your_password";$db_cnd=mysql_connect($Server,$user,$pass);……mysql_close($db_cnd);?>321.3PHP對(duì)MySQL數(shù)據(jù)庫操作步驟執(zhí)行SQL語句讀取存儲(chǔ)查詢結(jié)果集顯示、操作結(jié)果記錄數(shù)據(jù)int

mysql_query(stringSQL查詢字串，int[連接句柄]);注釋：SQL語句對(duì)大小寫不敏感。CREATEDATABASE數(shù)據(jù)庫名DROPDATABASEdatabase_name4新建數(shù)據(jù)庫<?php$con=mysql_connect("localhost","root","passw");if(mysql_query("CREATEDATABASEmy_db",$con)) {echo"Databasecreated";}else {echomysql_errno().":".mysql_error();}mysql_close($con);?>刪除數(shù)據(jù)庫<?php$con=mysql_connect("localhost","root","");mysql_query("DROPDATABASEmy_db1",$con);mysql_close($con);?>選中數(shù)據(jù)庫int

mysql_db_query(數(shù)據(jù)庫名,查詢語句,連接句柄);對(duì)查詢結(jié)果的存儲(chǔ)的方式有兩種：使用數(shù)組的方式使用類的方式。78intmysql_list_dbs(連接句柄);返回指定連接中的所有能用的數(shù)據(jù)庫名稱查看數(shù)據(jù)庫21.4PHP對(duì)MySQL數(shù)據(jù)表操作用mysql_query()函數(shù)CREATETABLEtable_name(column_name1data_type,column_name2data_type,column_name3data_type,)DROPTABLEtable_nameSHOWCOLUMNSFROM表名查詢數(shù)據(jù)表名mysql_list_tables()和mysql_tablename()。9創(chuàng)建表<?php$con=mysql_connect("localhost","root","");if(!$con) {die('Couldnotconnect:'.mysql_error());}mysql_select_db("my_db",$con);$sql="CREATETABLEperson(Namevarchar(15),Gendervarchar(15),Ageint)";mysql_query($sql,$con);mysql_close($con);?><$con=mysql_connect("localhost","root","");if(!$con) {die('Couldnotconnect:'.mysql_error());}mysql_select_db("my_db",$con);$sql="dropTABLEteacher";mysql_query($sql,$con);mysql_close($con);?>顯示表結(jié)構(gòu)mysql_select_db("my_db",$con);$sql="SHOWCOLUMNSFROMperson1";$result=mysql_query($sql,$con);while($row=mysql_fetch_array($result)){echo$row[0]."\n";echo$row[1]."\n";echo$row[2]."\n";echo"<br>";}1221.5PHP對(duì)MySQL記錄操作添加記錄INSERTINTOtable_nameVALUES(value1,value2,)刪除記錄DELETEFROMtable_nameWHEREcolumn_name=some_value修改記錄UPDATEtable_nameSETcolumn_name=new_valueWHEREcolumn_name=some_value查詢記錄SELECTcolumn_name(s)FROMtable_name13添加記錄<?php$con=mysql_connect("localhost","root","passw");if(!$con){die('Couldnotconnect:'.mysql_error());}mysql_select_db("my_db",$con);mysql_query("INSERTINTOperson(Name,Gender,Age)VALUES('Peter','male','35')");mysql_close($con);?>交互界面$sql="INSERTINTOperson(Name,Gender,Age)VALUES('$_POST[Name]','$_POST[Gender]','$_POST[age]')";if(!mysql_query($sql,$con)){die('Error:'.mysql_error());}echo"姓名:".$_POST[Name]."<br><br>";echo"性別:".$_POST[Gender]."<br><br>";echo"年齡:".$_POST[age]."<br><br>";echo"1recordadded";瀏覽數(shù)據(jù)SELECTcolumn_name(s)FROMtable_name對(duì)查詢結(jié)果的存儲(chǔ)的方式有兩種：使用數(shù)組的方式和使用類的方式。數(shù)組存儲(chǔ)格式：

arraymysql_fetch_array(結(jié)果集,結(jié)果類型);類存儲(chǔ)格式：

objectmysql_fetch_object(結(jié)果集,結(jié)果類型);5.存儲(chǔ)查詢結(jié)果—數(shù)組實(shí)例5.存儲(chǔ)查詢結(jié)果—對(duì)象實(shí)例5.存儲(chǔ)查詢結(jié)果對(duì)查詢結(jié)果的當(dāng)前記錄進(jìn)行存儲(chǔ)arraymysql_fetch_row(int

結(jié)果);對(duì)查詢結(jié)果中的某個(gè)具體的數(shù)據(jù)進(jìn)行存儲(chǔ)，int

mysql_result(int

結(jié)果,int

記錄序號(hào),mixed字段);對(duì)查詢所獲取的結(jié)果的總數(shù)量進(jìn)行統(tǒng)計(jì)arraymysql_num_rows(int

結(jié)果);釋放查詢結(jié)果所占用的內(nèi)存boolean

mysql_free_result(intresult);

5.存儲(chǔ)查詢結(jié)果圖22.9將查詢到的記錄條數(shù)和指定字段數(shù)據(jù)顯示在網(wǎng)頁中表格顯示echo"<tableborder='1'><tr><th>姓名</th><th>性別</th><th>年齡</th></tr>";while($row=mysql_fetch_array($result)){ echo"<tr>"; echo"<td>".$row["Name"]."</td>"; echo"<td>".$row["Gender"]."</td>"; echo"<td>".$row["Age"]."</td>"; echo"</tr>";}echo"</table>";多頁顯示//用URL傳遞顯示的頁數(shù)if(isset($_GET['page'])) {$page=$_GET['page'];}Else {$page=1;}if($page==1) { echo"<tr>"; echo"<td></td>";echo"<td></td>"; echo"<td>"; echo"<ahref=\"index.php?page=".($page+1)."\">next</a>"; echo"</td>"; echo"<td>"; echo"<ahref=\"index.php?page=".$pagenum."\">endpage</a>"; echo"</td>"; echo"</tr>"; }$num=mysql_num_rows($result);$pagenum=ceil($num/4);$offset=($1)*4;$query="select*fromperson2limit$offset,4";$result=mysql_query($query,$con);

查詢記錄if(!$_POST[Name]) {$q="Name".$_POST[Name];}if(!$_POST[Gender]) {$q="Gender".$_POST[Gender];}if($_POST[age]) {$q="age".$_POST[age];}$sql="select*fromperson2where".$q;

$result=mysql_query($sql,$con);23.8修改記錄while($row=mysql_fetch_array($result)){ echo"<tr>"; echo"<td>".$row["Name"]."</td>"; echo"<td>".$row["Gender"]."</td>"; echo"<td>".$row["Age"]."</td>";

// 把記錄中id作為URL參數(shù)

echo"<td><ahref=\"update.php?id=";

echo$row["id"]; echo"\">"."修改"."</a></td>"; echo"</tr>";}29$query="select*fromperson2whereid=".$_GET["id"];$result=mysql_query($query,$con);$row=mysql_fetch_array($result);?><formaction="ok.php"method="get">Name:<inputtype="text"name="Name"value=<?phpecho$row["Name"];?>>$query="updateperson2setname='".$_GET["Name"]."',gender='".$_GET["Gender"]."',age='".$_GET["age"]."'whereid='".$_GET["nid"]."'";$result=mysql_query($query,$con);If($result) { echo"<script> alert('修改成功'); window.location.href='index.php'; </script>"; }else