PHP與MySQL通信分為以下步驟
1.連接到MySQL服務(wù)器
2.發(fā)送SQL查詢
使用mysqli_connect打開連接
$cxn = mysqli_connect("host","acct","password","dbname")
or die ("message");
$host="localhost";
$user="phpuser";
$password="";
$dbname = "Customer";
$cxn = mysqli_connect($host,$user,$password,$dbname)
or die("Couldn't connect to server.");
$query = "SELECT * FROM Customer";
$result = mysqli_query($cxn,$query)
or die ("Couldn't execute query.");
對(duì)于不返回?cái)?shù)據(jù)的查詢，$result中包含查詢語句是否執(zhí)行成功的信息焙蹭，true或false林束；
對(duì)于返回?cái)?shù)據(jù)的查詢粤铭，$result包含指明返回?cái)?shù)據(jù)位置的標(biāo)識(shí)符乃戈。
為減少出錯(cuò)，一般情況下按以下規(guī)則使用單雙引號(hào)：
在字符串前后使用雙引號(hào)矿瘦；
在變量名前后使用單引號(hào)鳄逾；
在字面量前后使用單引號(hào)。
例子：
$query = "SELECT firstName FROM Customer";
$query = "SELECT firstName FROM Customer WHERE lastName='Smith'";
$query = "UPDATE Customer SET lastName='$last_name'";
使用mysqli_multi_query可以同時(shí)執(zhí)行多個(gè)查詢語句
$query = "SELECT * FROM Cust;SELECT * FROM OldCust";
mysqli_multi_query($cxn,$query);
但是使用多個(gè)語句會(huì)導(dǎo)致不安全励翼。如果使用外部數(shù)據(jù)創(chuàng)建查詢語句蜈敢，一定要進(jìn)行驗(yàn)證。例如讓用戶輸入一個(gè)查詢的表明汽抚，如果用戶輸入Friend抓狭，則$query = "SELECT * FROM Friend";但是如果用戶惡意輸入Friend;DELETE TABLE Friend，則$query = "SELECT * FROM Friend;DELETE TABLE Friend";這就會(huì)刪除數(shù)據(jù)表全部數(shù)據(jù)造烁。
關(guān)閉連接
mysqli_close($cxn);
選擇一個(gè)數(shù)據(jù)庫
mysqli_select_db($cxn,"databasename")
or die ("message");
當(dāng)mysqli_query()失敗時(shí)會(huì)返回錯(cuò)誤信息否过，但是默認(rèn)不顯示，需通過mysqli_error($cxn)顯示錯(cuò)誤惭蟋。
例子：
$query = "SELECT * FROM Cust";
$result = mysqli_query($cxn,$query)
or die ("Error: ".mysqli_error($cxn));
獲取返回記錄個(gè)數(shù)
$query = "SELECT * FROM ValidUser
WHERE acct = '$_POST[userID]'
AND password = '$password'";
$result = mysqli_query($cxn,$query);
$n = $mysql_num_rows($result);
if($n < 1)
{
echo "User name and password are not valid";
exit();
}
獲取最后一個(gè)記錄的auto_increment字段
$query = "INSERT INTO CustomerOrder (customerID,orderDate) VALUES ($customerID,$date)";
$result = mysqli_query($cxn,$query);
$orderID = mysqli_insert_id($cxn);
$query = "INSERT INTO OrderItem (orderID,color,size,price) VALUES ($orderID,$color,$size,$price)";
$result = mysqli_query($cxn,$query);
獲取影響的行數(shù)
$query = "UPDATE Student SET status='pass' WHERE score > 50";
$result = mysqli_query($cxn,$query);
$passed = mysqli_affected_rows($cxn);
echo "$passed students passed";
字符轉(zhuǎn)義
php.ini中magic_quotes_gpc在PHP4和PHP5中被默認(rèn)開啟苗桂，但在PHP6中不再支持。建議關(guān)閉告组。
$lastName = mysqli_real_escape_string($lastName);
$lastName = mysqli_real_escape_string($_POST['lastName']);
