Interactive PHP Applications Using JQuery.AJAX and MySQL

Using AJAX can be tricky and frustrating do to common pitfalls and misunderstandings about how it should be implemented. If this is your first experience with it then I hope you will find this post beneficial and I also hope that I provided adequate enough example and explanation that you won’t have to go on an epic Google search to accomplish what you are trying to do.

For this post I am going to jump right into an example. This example is a small  application that will demonstrate how a web page can exchange information with a MySQL database using JQuery, AJAX and a PHP API.  At a high level this application can be described by the following synopsis.

SYNOPSIS
Build a single web page where I can interactively do the following:


1. Select a userid from a drop down list and dynamically view information about that user without refreshing the client page.
2. Add a new user and update the select list without refreshing the client page
3. Delete a user

Considering the synopsis above, this application will effectively demonstrate how to do the following:

  • Use JQuery.ajax to fetch data from a table in a MySQL database and dynamically load a drop down select list with userids when the page initially loads.
  • Use JQuery.ajax to dynamically submit a form by converting the form data to a serialized JSON object, post this data to a PHP API which decodes the JSON data and facilitates the insert into the MySQL database then sends a response to the client web page and dynamically updates the drop down select list without refreshing the client web page.
  • Use JQuery.ajax to make a call to fetch a database record and dynamically populate an HTML table when the value of a select list is changed by the user, without refreshing the whole client web page.
  • Use a hyperlink to send a delete command to the PHP API which will delete the selected user record from the database and refresh the client.

In this scenario, PHP takes a “behind the scenes” position to handle the data processing. The  front end will be a a simple HTML page that uses JQuery and AJAX to facilitate any actions initiated by the user and CSS for its presentation.

*** DOWNLOAD THE SOURCE CODE FOR THIS APPLICATION HERE ***

Source Files Include:

  • db.sql
  • mysql_api.php
  • client.php
  • initialize.js
  • interact.js
  • presentation.css

Let’s start by creating a database called “test” and a table called “users” and insert an initial record to get us started. As I did in my book, I’m using MySQL Workbench 5.2 CE as my database console (I provided detailed instruction in my book on how to download and set this up along with a full LAMP stack).  After you set up a connection to your localhost or wherever, simply open the db.sql file in the SQL Editor and execute (by clicking the yellow lightening bolt on the menu bar).

MySQL

Your database is now set up and contains a couple of records.

For this example I’m using my old Windows 7  Pro machine and I’m running this application out of my C:\Program Files\Zend\Apache2\htdocs folder. When you extract the source code files please put them in the root directory of whatever web server you are using.

Let’s move on to the client.php file. This is the only file that the user will interact with in this sample app. For the purpose of discussion throughout this tutorial, I put divided the Javascript up into two different files and I put all of my CSS presentation code in presentation.css. I simply pull these three files into my HTML file by including them where I want them.

The HTML

client.php
client_php

The JAVASCRIPT

initialize.js
ajax_initialize_js

interact.js
ajax_interact_js
ajax_interact_js
ajax_interact_js

The PHP & MySQL

mysql_api.php
ajax_mysql_api_php
ajax_mysql_api_php
ajax_mysql_api_php