| Let us take an example of ajax with the use of database, how can we interact with database while using ajax in php, may be this question arises in your mind, so here we explain each and every thing through this example. |
| Example: |
| The example shown below is having two php page . First page is for display the field, having HTML tags and the java script, the text field which is in a div is for receiving the result of the user input, text field is for user input a user puts his customer id and while clicks on the check button the request send to the server and the process will occur using the second page. |
| Actually the other page is meant for the php script and mysql query.Which is used for communicating the server. And responsible for processing the query (id) in the database. |
| Actually the theme of the example is when a user filled his customer id in the html page, the page will checks whether the customer exists or not if it exist then it will give the user name, address, city and phone no and if the id enter by the user/customer is not exist in the database then a message will come “Customer with the ID= XXXX doesn’t exist.” |
| Page name: findUser.php |
1.<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
2.<html>
3.<head>
4. <title>Customer Report</title>
5. <script type="text/javascript">
6. var url = "customerDetl.php?id="; // The server-side script
7. function handleHttpResponse() {
8. if (http.readyState == 4) {
9. if(http.status==200) {
10. var results=http.responseText;
11. document.getElementById('divCustInfo').innerHTML = results;
12. }
13. }
14 . }
15. function requestCustInfo() {
16. var sId = document.getElementById("txtId").value;
17. http.open("GET", url + escape(sId), true);
18. http.onreadystatechange = handleHttpResponse;
19. http.send(null);
20. }
21. function getHTTPObject() {
22. var xmlhttp;
23. if(window.XMLHttpRequest){
24. xmlhttp = new XMLHttpRequest();
25. }
26. else if (window.ActiveXObject){
27. xmlhttp=new ActiveXObject("Microsoft.XMLHTTP");
28. if (!xmlhttp){
29. xmlhttp=new ActiveXObject("Msxml2.XMLHTTP");
30. }
31. }
32. return xmlhttp;
33.}
34. var http = getHTTPObject(); // creating HTTP Object
35. </script>
36. </head>
37. <body>
38. <p>Please enter your customer ID to retrieve information:</p>
39. <p>Customer ID: <input type="text" id="txtId" value="" /></p>
40. <p><input type="button" value="check" onClick="requestCustInfo()" /></p>
41. <div id="divCustInfo"></div>
42. </body>
43. </html
|
| Explanation: |
| Line 1: First line is as you know the defifnes foe html.
Line 2:<html>Html tag starts. Line 3:<head> starts. Line 4:<title > tag starts with a title. Line 5: it start a script name javascript. Line 6: var url = “customerDetl.php?id=”; Crreating a variable to store the url(php script page url), with the id appended as a query string. |
| In theline 7 a function is created with the name handle HttpResponse. This function is used for checking the condition for readyState == 4 (This is the state when the completion of the process would occur.) and the status (This returns the HTTP status code from the server such as 200 for OK or 404 for not found.) |
| The result of the response .Text (response. Text) this is used for returning the response to the server, the response text contain the text of the http response.) is stores in a variable called results. And return to the div id which is present in the HTML page named as <div id=”divCustInfo”>. |
| Line 15-20: |
| function requestCustomerInfo() is created here, initialize the open function using the argument as div id url and the condition. |
| Onreadystatechange Event handler for an event that fires at every state change. |
| Before we send the HTTP request, we specify the onreadystatechange property to be the name of our new function handleHttpResponse(). This means that every time the HTTP ready state has changed, our function handleHttpResponse() is called. |
| Line 21-32: We have already describe these lines, taking as an example above so there are no need to describe this, it simply for creation of the XMLHttpRequest object. |
| Line 38-41: |
<p>Please enter your customer ID to retrieve information:</p> <p>Customer ID: <input type="text" id="txtId" value="" /></p> <p><input type="button" value="check" onClick="requestCustInfo()" /></p> <div id="divCustInfo"></div> |
| This is the html tags to display the field for input “Please enter….” Is simply a text message and the field where the user puts its input is <input type=”text” id=”txtId” value=”” /> this is the field where user puts his input. |
| <div id=”divCustInfo”></div> This is used for storing the server response. |
| Page name: customerDetl.php |
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<html>
<head>
<title>Customer Report</title>
<?php
//customer ID
1. $ID = $_GET["id"];
2. //variable to hold customer info
3. $Info = "";
4. //database information
5. $sDBServer = "localhost"; ## Database Host Name
6. $sDBName = "user"; ## Database Name
7. $sDBUsername = "root"; ## Database User
8. $sDBPassword = ""; ## Database User Password
## Creating database connection
9. $Link = mysql_connect($sDBServer,$sDBUsername,$sDBPassword);
10. @mysql_select_db($sDBName, $Link) or $Info = "Unable to open database";
## Mysql Query for fetching the data from the database from the tabe customers
11. $sQuery = "Select * from Customers where id=".$ID;
12. if($Info == '') {
13. if($Result = mysql_query($sQuery) and mysql_num_rows($Result) > 0) {
14. $Values = mysql_fetch_array($Result,MYSQL_ASSOC);
15. $Info = "Name: ".$Values['name'].
"<br />"."Add: ".$Values['address']."<br />".
16. "city: ".$Values['city'].
"<br />"."Phone: ".$Values['phone_no']."<br />";
17. } else {
18. $Info = "Customer with the ID= $ID doesn't exist.";
19. }
20. }
20. mysql_close($Link);
?>
</head>
<body>
21. <div id="divInfoToReturn"> <?php echo $Info ?> </div>
</body>
</html>
|
| Explanation |
| This page is business login page of the example, pages having the php script and the mysql query which directly communicates with the server the line by line explanation of the code is: |
| Line1: Storing the value of id with the help of $_GET in a variable $ID. Line 2-3: Creating a variable to store user information. |
| 4. //database information
5. $sDBServer = “local host”; ## Database Host Name 6. $sDBName = “user”; ## Database Name 7. $sDBUsername = “root”; ## Database User 8. $sDBPassword = “”; ## Database User Password |
| This is used to for database connection: |
| $sDBServer is used for server name (In this example we take it a local host server).
$sDBName Is used for Database. $sDBUsername is for the database user name; $DBPassword is for the database password. |
| Line 9-10: |
| 9. $Link = mysql_connect($sDBServer,$sDBUsername,$sDBPassword);
10. @mysql_select_db($sDBName, $Link) or $Info = “Unable to open database”; |
| It Is used for connection to the database. |
| Line 11-21: |
| 11. $sQuery = “Select * from Customers where id=”.$ID;
12. if($Info == ”) { 13. if($Result = mysql_query($sQuery) and mysql_num_rows($Result) > 0) { 14. $Values = mysql_fetch_array($Result,MYSQL_ASSOC); 15. $Info = “Name: “.$Values[‘name’].”<br />”.”Add: “.$Values[‘address’].”<br />”. 16. “city: “.$Values[‘city’].”<br />”.”Phone: “.$Values[‘phone_no’].”<br />”; 17. } else { 18. $Info = “Customer with the ID= $ID doesn’t exist.”; 19. } 20. } 21.mysql_close($Link); |
| 11. $query is used a for keeping the simple select query which is acted on the table customer and fetches its all data. |
| 12-13. Process the above query with mysql function and stores it in the var. $Result and then fetch the result set with the mysql function mysql_fetch_array and the result would comes with this is final result which is being stores in the $Value variable. |
| 14-15: Display the result data comeming from the $Value. |
| 16-20 if this would not be occurred then the message is displayed “Customer with the ID =XXXX doesn’t exist”. |
| 21: close the connections of the database. |