We now have a youtube channel. Subscribe!
Home
Ajax

AJAX | Database Operations

AJAX Database Operations


Hello folks! welcome back to a new section of our tutorial on AJAX. In this tutorial post, we are going to be discussing about AJAX Database Operations.

To clearly explain how easy it is to access information from a database using AJAX, we will build MySQL queries on the fly and show the results on "ajax.html". But before we proceed, let's do the ground work. First create a table using the command below.


Note: we are assuming you have sufficient privilege to carry out the following MySQL operations.

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')
)

Now dump the following data into this table using the following SQL statements -

INSERT INTO 'ajax_example' VALUES ('Kennedy', 100, 'm', 28);
INSERT INTO 'ajax_example' VALUES ('Paul', 80, 'm', 36);
INSERT INTO 'ajax_example' VALUES ('Frank', 45, 'm', 40);
INSERT INTO 'ajax_example' VALUES ('Joyce', 22, 'f', 72);
INSERT INTO 'ajax_example' VALUES ('Tracy', 27, 'f', 0);
INSERT INTO 'ajax_example' VALUES ('Julie', 35, 'f', 90);

Client Side HTML File

Now let us have our client side HTML file, which is ajax.html, and it is going to have the following code -

<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.innerHTML = 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>

Note: The mode of passing variables in the Query is according to HTTP standard and have formA.

URL?variable1 = value1;&variable2 = value2;

The above code will display to you a screen as given below -

Max Age:  

Max WPM: 

Sex: 


Your result will display here in this section after you have made all your entries.

Note: The above screen is a dummy screen and hance won't work.


Server Side PHP File

Our client-side script is ready. Now, we have to write our server-side script, which will be used to fetch age, wpm, and sex from the database and will send it back to the client.

Example

Put the following code into the file "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;
?>

Now try adding a valid value (e.g., 100) in Max Age or any other box and click on the Query MySQL button.

Max Age:  

Max WPM: 

Sex: 


Your result will display here in this section after you have made all the entries.

If you have successfully rounded up with this tutorial, then you know how to use PHP, MySQL, HTML, and JavaScript to write Ajax applications.


Alright guys! This is where we are going to be rounding up for this tutorial post. In our next tutorial, we are going to be discussing about AJAX Security.

Feel free to ask your questions where necessary and we will attend to them as soon as possible. If this tutorial was helpful to you, you can use the share button to share this tutorial.

Follow us on our various social media platforms to stay updated with our latest tutorials. You can also subscribe to our newsletter in order to get our tutorials delivered directly to your emails.

Thanks for reading and bye for now.

Post a Comment

Hello dear readers! Please kindly try your best to make sure your comments comply with our comment policy guidelines. You can visit our comment policy page to view these guidelines which are clearly stated. Thank you.
© 2023 ‧ WebDesignTutorialz. All rights reserved. Developed by Jago Desain