How to use AJAX with MySQL

How to use AJAX with MySQL

a href=”http://www.ajax.org/”>AJAX, which stands for asynchronous JavaScript and XML, works by sending server requests in the background and then updating a part of the page, without having to reload the entire page.  This creates a richer and more dynamic user experience.  Using it with MySQL, however, can be more challenging, since you will have to transfer variables from JavaScript to PHP.  This tutorial will teach you how to do so.

Using JQuery

In this tutorial we’ll be using jQuery, a JavaScript library.  It’s not necessary, but it simplifies AJAX calls for us.  If you don’t already have jQuery, you can put this code in your page’s <head> section to use it:

<script language=”JavaScript” src=”http://code.jquery.com/jquery-1.4.2.min.js“> </script>

You can also download the latest version of jQuery from http://www.jquery.com and include that file instead.

Creating an AJAX call

Before using AJAX to interact with our MySQL database, we need to create a table to hold some information.  Create a table named ‘user_info’ with two columns:  username and password, then add in a row with whatever username and password you want.  You can use this code to create the table quickly (by using it in PhpMyAdmin or on a PHP page):

CREATE TABLE IF NOT EXISTS `user_info` (
`username` text NOT NULL,
`password` text NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
INSERT INTO `user_info` (`username`, `password`) VALUES (‘John’, ‘john233′);

Next, let’s make an html page with a form.  You can copy this section and save it as index.php. Notice that you don’t have to use the <form> tags, as our jQuery code will handle the form instead.

<html>

<head>

<title>AJAX call</title>

<script language=”JavaScript” src=”http://code.jquery.com/jquery-1.4.2.min.js“></script>

<script language=”JavaScript” src=”ajax.js”></script>

</head>

<body>

This text will stay the same.

<div id=”replace”>

Enter your username :<input />

<input id=”button” value=”Click here to load new text” />

</div>

</body>

</html>

Next, we’ll create the JavaScript file we included in index.php, ajax.js, and save it in the same directory as our html page.

$(document).ready(function(){

$(“#button”).click(function(){

var user = $(“#username”).val();

$.post(“ajax.php”, { username: user }, function(data) {

$(“#replace”).html(user + ‘, your ‘ + ‘ password ‘ + ‘ is ‘ + data);

});

});

});

In case you’re not familiar with JQuery, I’ll break this down for you. $() is used to select elements, as well as classes and IDs.  On the first line we are selecting the entire document.  .ready() is a function that will wait until the entire page is loaded before executing the code inside.  Next we have function(){}.  We will put all our code in between the two brackets.

Inside the function, we select #button, the ID of the button we put in our html page.  The .click() afterwards means it will wait until our button is clicked before using the code inside.

Once the button has been clicked, a new variable is created called user, with the value of our input ID username.  Next is the AJAX call; .post() creates an AJAX call with the post method, then sends the data to ajax.php.  Username: user creates a post variable named username holding the name we typed in our form.  Once the call is completed and ajax.php has been executed, the information sent back is held in a variable named ‘data’.

Next, we replace the <div> holding the form in our main page with the string, using the .html() function.  The actual words are placed within single quotes, but not the variables that will be filled in.  Our ‘user’ and ‘password’ variables are replaced with our MySQL results we get after sending the information to ajax.php .

Now we have to create ajax.php.  This is the code that send a query to MySQL and returns our password.  If the code seems unfamiliar, it’s because we’re using PDO (PHP data objects) instead of mysql functions.  You’ll have to replace the connection details with your own.

<?php
$host = 'your_hostname';
$port = 3306; //Default MySQL port
$database = 'your_database';
$username = 'your_username';
$password = 'your_password';
$dsn = "mysql:host=$host;port=$port;dbname=$database";
$db = new PDO($dsn, $username, $password);
        if (isset($_POST['username'])) {

                $username = $_POST['username'];

                $statement = $db->prepare("SELECT password FROM `user_info` WHERE `username` = '$username'");

                $statement->execute();

                $result = $statement->fetchObject();
                echo $result->password;

        }

?>

This checks if the username field has been filled, then sends a query to the MySQL table

‘user_info’ asking for our user’s password.  Our “echo” function is what gives the result to ajax.php to be used on the main page.

You should now have three files,  index.html, ajax,js, and ajaxtest.html, in the same directory.  Go to index.php, typed in the username you used in MySQL (if you copied & pasted the code, it’s ‘John’), and click the button.  The form should now be replaced with your password, without having to reload the page!  You can use this technique to create dynamic web sites.