Ajax和數據庫操作

Ajax和數據庫操作。清楚地說明如何使用Ajax從一個數據庫中的信息訪問，我們要建立動態MySQL的查詢，並在“ajax.html”顯示結果。

要清楚地說明了訪問使用Ajax從一個數據庫中的信息是多麼容易，我們要建立動態的MySQL查詢結果顯示對“ajax.html”。但在我們繼續之前，讓做基礎工作。使用以下命令創建一個表.

注: 我們建議你有足夠的權限來執行下麵的MySQL操作

CREATE TABLE `ajax_example` (
  `name` varchar(50) NOT NULL,
  `age` int(11) NOT NULL,
  `sex` varchar(1) NOT NULL,
  `wpm` int(11) NOT NULL,
  PRIMARY KEY  (`name`)
)

現在轉儲到這個表中使用下麵的SQL語句的下列數據

INSERT INTO `ajax_example` VALUES ('Jerry', 120, 'm', 20);
INSERT INTO `ajax_example` VALUES ('Regis', 75, 'm', 44);
INSERT INTO `ajax_example` VALUES ('Frank', 45, 'm', 87);
INSERT INTO `ajax_example` VALUES ('Jill', 22, 'f', 72);
INSERT INTO `ajax_example` VALUES ('Tracy', 27, 'f', 0);
INSERT INTO `ajax_example` VALUES ('Julie', 35, 'f', 90);

客戶端的HTML文件

現在，讓我們有我們的客戶端的HTML文件ajax.html，有下麵的代碼

<html>
<body>
<script language="javascript" type="text/javascript">
<!-- //Browser Support Code function ajaxFunction(){
 var ajaxRequest;  // The variable that makes Ajax possible!
	
 try{
   // Opera 8.0+, Firefox, Safari
   ajaxRequest = new XMLHttpRequest();
 }catch (e){
   // Internet Explorer Browsers
   try{
      ajaxRequest = new ActiveXObject("Msxml2.XMLHTTP");
   }catch (e) {
      try{
         ajaxRequest = new ActiveXObject("Microsoft.XMLHTTP");
      }catch (e){
         // Something went wrong
         alert("Your browser broke!");
         return false;
      }
   }
 }
 // Create a function that will receive data 
 // sent from the server and will update
 // div section in the same page.
 ajaxRequest.onreadystatechange = function(){
   if(ajaxRequest.readyState == 4){
      var ajaxDisplay = document.getElementById('ajaxDiv');
      ajaxDisplay.value = ajaxRequest.responseText;
   }
 }
 // Now get the value from user and pass it to
 // server script.
 var age = document.getElementById('age').value;
 var wpm = document.getElementById('wpm').value;
 var sex = document.getElementById('sex').value;
 var queryString = "?age=" + age ;
 queryString +=  "&wpm=" + wpm + "&sex=" + sex;
 ajaxRequest.open("GET", "ajax-example.php" + 
                              queryString, true);
 ajaxRequest.send(null); 
} //--> </script>
<form name='myForm'>
Max Age: <input type='text' id='age' /> <br />
Max WPM: <input type='text' id='wpm' />
<br />
Sex: <select id='sex'>
<option value="m">m</option>
<option value="f">f</option>
</select>
<input type='button' onclick='ajaxFunction()' 
                              value='Query MySQL'/>
</form>
<div id='ajaxDiv'>Your result will display here</div>
</body>
</html>

注: 在查詢傳遞變量的方法是根據HTTP標準和表單

URL?variable1=value1;&variable2=value2;

服務器端PHP文件

所以，現在您的客戶端腳本已準備就緒。現在我們來寫我們的服務器端腳本，這會從數據庫中提取年齡，WPM和性彆將其發送回客戶端。 “ajax-example.php”文件放入下麵的代碼

<?php
$dbhost = "localhost";
$dbuser = "dbusername";
$dbpass = "dbpassword";
$dbname = "dbname";
	//Connect to MySQL Server
mysql_connect($dbhost, $dbuser, $dbpass);
	//Select Database
mysql_select_db($dbname) or die(mysql_error());
	// Retrieve data from Query String
$age = $_GET['age'];
$sex = $_GET['sex'];
$wpm = $_GET['wpm'];
	// Escape User Input to help prevent SQL Injection
$age = mysql_real_escape_string($age);
$sex = mysql_real_escape_string($sex);
$wpm = mysql_real_escape_string($wpm);
	//build query
$query = "SELECT * FROM ajax_example WHERE sex = '$sex'";
if(is_numeric($age))
	$query .= " AND age <= $age";
if(is_numeric($wpm))
	$query .= " AND wpm <= $wpm";
	//Execute query
$qry_result = mysql_query($query) or die(mysql_error());

	//Build Result String
$display_string = "<table>";
$display_string .= "<tr>";
$display_string .= "<th>Name</th>";
$display_string .= "<th>Age</th>";
$display_string .= "<th>Sex</th>";
$display_string .= "<th>WPM</th>";
$display_string .= "</tr>";

// Insert a new row in the table for each person returned
while($row = mysql_fetch_array($qry_result)){
	$display_string .= "<tr>";
	$display_string .= "<td>$row[name]</td>";
	$display_string .= "<td>$row[age]</td>";
	$display_string .= "<td>$row[sex]</td>";
	$display_string .= "<td>$row[wpm]</td>";
	$display_string .= "</tr>";
	
}
echo "Query: " . $query . "<br />";
$display_string .= "</table>";
echo $display_string;
?>

如果你已經成功地完成了這一課，那麼你知道如何使用串聯的MySQL，PHP，HTML和Javascript編寫Ajax應用程序.