Ajax mysql examples

MySQL

It is possible to use AJAX to pull data from a MySQL database. The HTML file that was created in the earlier part of this tutorial can be used to demonstrate this with a few updates and with the creation of the new database.

First some MySQL queries must be created.  Either a new database can be created or an existing table can be used.  For the sake of this example, let’s say that ajax_sample.sql was imported to a database and it creates the table ajax-sample and inserts all of the necessary data rows.  This table has the following columns:

  • aj_name – name of person
  • aj_age – age of person
  • aj_sex – gender of person

In order to build queries from the HTML file, more elements need to be added. The additional inputs that are necessary are the following:

1. Maximum Age – This is a text input that allows the maximum age to be returned to be set.

2. Gender– This is a selection input that allows the user  to select the person’s gender.

 

The following example shows this added to the HTML and JavaScript:

Example: <html>

<body>

 

<script language=”javascript” type=”text/javascript”>

<!–

//Support Code for Browser

function ajaxFunction(){

var ajaxRequest;  // This is necessary for AJAX

 

try{

// Firefox, Opera, Safari

ajaxRequest = new XMLHttpRequest();

} catch (e){

// Internet Explorer

try{

ajaxRequest = new ActiveXObject(“Msxml2.XMLHTTP”);

} catch (e) {

try{

ajaxRequest = new ActiveXObject(“Microsoft.XMLHTTP”);

} catch (e){

// Something is incorrect

alert(“Current browser does not work.”);

return false;

}

}

}

// Function to receive data from server

ajaxRequest.onreadystatechange = somefunction(){

if(ajaxRequest.readyState == 4){

document.someForm.date.value = ajaxRequest.responseText;

}

}

var age = document.getElementById(‘age’).value;

var sex = document.getElementById(‘sex’).value;

var queryString = “?age=” + age + “&sex=” + sex;

ajaxRequest.open(“GET”, “ajax-example.php” + queryString, true);

ajaxRequest.send(null);

}

 

//–>

</script>

 

<form name=’someForm’>

Max Age: <input type=’text’ id=’age’ /> <br />

Sex: <select id=’sex’>

<option>m</option>

<option>f</option>

</select>

<input type=’button’ onclick=’ajaxFunction()’ value=’Query MySQL’ />

</form>

</body>

</html>

 

In this new piece of JavaScript, a query string has been built (var queryString = “?age=” + age +  “&sex=” + sex;) to pass the information from the HTML form to the PHP script.  The query string allows information to be passed by appending data onto the URL. In the query string, notice that each variable is separated by and ampersand (&).

The next step is to build a PHP script that will use these variables to run a MySQL query. To prevent harmful user input, a special function called mysql_real_escape_string will be used.  The following example contains the code for the new ajax-sample.php file.

Example: <?php

$dbhost = “localhost”;

$dbuser = “dbusername”;

$dbpass = “dbpassword”;

$dbname = “dbname”;

//Connect to server

mysql_connect($dbhost, $dbuser, $dbpass);

//Choose database

mysql_select_db($dbname) or die(mysql_error());

// Get data from query string

$age = $_GET[‘age’];

$sex = $_GET[‘sex’];

// Escape User Input to help prevent SQL Injection

$age = mysql_real_escape_string($age);

$sex = mysql_real_escape_string($sex);

//Build query

$query = “SELECT * FROM ajax_example WHERE ae_sex = ‘$sex'”;

if(is_numeric($age))

$query .= ” AND ae_age <= $age”;

//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 .= “</tr>”;

 

// Insert row in table for each person returned

while($row = mysql_fetch_array($qry_result)){

$display_string .= “<tr>”;

$display_string .= “<td>$row[ae_name]</td>”;

$display_string .= “<td>$row[ae_age]</td>”;

$display_string .= “<td>$row[ae_sex]</td>”;

$display_string .= “</tr>”;

 

}

echo “Query: ” . $query . “<br />”;

$display_string .= “</table>”;

echo $display_string;

?>

 

This completes the initial HTML and PHP file setup.  For the next step the HTML page must update correctly when the query is returned.  This means that a segment of the HTML file will be updated with the MySQL result display_string.

This entry was posted in Ajax Tutorials and tagged , , , , , , , . Bookmark the permalink.

Leave a Reply

Your email address will not be published.