Ajax is a powerful web technology, it allows a web app’s communication with a server, without having to refresh the browser.
MySQL is a relational database that allows users to store and retrieve data. It is a free available open source Relational Database Management System (RDBMS) that uses Structured Query Language (SQL).
According to the official PHP website, PHP (recursive acronym for PHP: Hypertext Preprocessor ) is a widely-used open source general-purpose scripting language that is especially suited for web development and can be embedded into HTML. In this example we are going to create a web app that uses the three technologies mentioned above.
For this example we will use:
- A computer with PHP>= 5.5 installed
- Mysql server
- notepad++
- Phpmyadmin: This is not compulsory. (All the codes in this example will work correctly even without it). If you want a graphical user interface for manipulating your datbase, then you should install it.
1. Getting Started
Ajax is a favourite technology among web developers. AJAX stands for asynchronus javascript and XML. Asynchronus in ajax represents the fact that the browser does not need to refresh itself while communicating with the server. This particular nature of ajax makes it very popular among web developers.
Ajax uses the XMLHttpRequest object to communicate with the server. Ajax can process data in a variety of formats, some of which are JSON, XML and HTML.
Two major features of ajax are:
- Send requests to server side scripts without having to refresh the browser.
- Receive and work with the data in a preffered format.
Some popular use cases of AJAX are:
- Gaming: Ajax is very popular with web games. For example when a player gains a new super power or crosses to a new level, the game can easily update its new state with the server on the background – while the game play is not interrupted.
- Form Validation: When a new user is about to register on a website and he or she picks a new username- webmasters use Ajax to check if the username has already been picked(Ajax -> Server Script-> Database-> Server Script-> Ajax).
The above are only few use cases of ajax.
MySQL database is very popular among Web developers. Its arguably the most popular database used with PHP. Its free and open source nature has endeared to tons of web developers. With a few PHP commands you can get MySQL up and running.
1.1 Ajax Script
The script below is an example of ajax in action.
index.html
<!DOCTYPE html>
<html lang=en>
<head>
<style>
</style>
<meta charset="utf-8" />
<meta name="viewport" content="width=device-width, initial-scale=1.0, maximum-scale=1.0, user-scalable=no, target-densitydpi=device-dpi"/>
<title>chat</title>
</head>
<body>
<script>
alert("Ajax About To Start");
// code for IE6, IE5
if (window.XMLHttpRequest) {
// code for IE7+, Firefox, Chrome, Opera, Safari
xmlhttp =new XMLHttpRequest();
} else {//
xmlhttp =new ActiveXObject("Microsoft.XMLHTTP");
}
xmlhttp.onreadystatechange = function() {
if(this.readyState == 4 && this.status == 200) {//received full server response and check the server response
alert(this.responseText);
}
};
xmlhttp.open("GET", "index.php", true);
xmlhttp.send();
</script>
</body>
</html>
In the code above we use ajax to make an http request to our server side script and we handle the response. In line 16 we check if the users browser supports XMLHttpRequest object. If it does we create an XMLHttpRequest object. If it doesnt we create try to create a ActiveXObject object.
In line 27 we call the method open. The first parameter of the call to open is the HTTP request method, it can be GET, POST or any other method you want to use and that is supported by your server. Always make sure the method is capitalized as per HTTP standard.
The second parameter is the URL of our server side script. As a security measure, you cannot call pages on 3rd-party domains.
The third and optional parameter sets whether our request is asynchronous. If TRUE- which is the default- the execution of the JavaScript function will continue while the response of the server has not yet arrived.
index.php
<?php echo "Ajax Working"; ?>
1.2 Preparing The Database
We need to get our database up and running, since PHP will be talking to it. Downloading and installing Mysql database can be a hassle, if you are running this example on your localhost, i advice you to download xampp or wamp. It comes with PHP, MYSQL and other useful packages. Most of them are free and very easy to install.
db.php
<?php
$dsn = "mysql:dbname=authentication";
$username = "root";
$password = "";
try{
$conn = new PDO( $dsn, $username, $password );
$conn->setAttribute(PDO::ATTR_ERRMODE,PDO::ERRMODE_EXCEPTION);
}
catch(PDOException $pd){
echo $pd->getMessage();
}
?>
The script above creates a connection to our database using PDO (PHP Data Object). The connection is stored in the $conn object and it is made available to manipulate the database.
We create a new PDO object in line 6 and surround it with a try/catch statement, so as to catch any error that might occur while trying to connect to the database.
init.php
<?php
require_once("db.php");
try{
$sql="create Table record(id smallint unsigned not null AUTO_INCREMENT PRIMARY KEY, name VARCHAR(265) NOT NULL,email VARCHAR(265) NOT NULL,department VARCHAR(265) NOT NULL,position varchar(255) NOT NULL)";
$conn->exec($sql);
echo "TABLE CREATED";
}
catch(PDOException $pd){
echo "Error Creating Table: " . $pd->getMessage();
}
$conn=null;//close the database connection
?>
Our web app needs to pull data out of the database, so we create a table in the database that will hold that data. Before we start loading data into our database,we should load this script(init.php) into the browser (just once). The script creates a table that is called “record” in the database, this table will contain all the data that we will need.
In line 3 we include the “db.php” script to login to the database. In line 9 we create a table in our database by calling the $conn->exec($sql) method, which takes a string as a parameter (the string holds the sql query).
insertdata.php
<?php
require_once("db.php");
try{
//begin the transaction
$conn->beginTransaction();
//our sql statements
$conn->exec("INSERT INTO record(name,email,department,position)VALUES('John Jack','e@example.com','accounting','accountant')");
$conn->exec("INSERT INTO record(name,email,department,position)VALUES('Mary Daniel','e@example.com','accounting','accountant')");
$conn->exec("INSERT INTO record(name,email,department,position)VALUES('Peace Daniel','e@example.com','accounting','accountant')");
$conn->exec("INSERT INTO record(name,email,department,position)VALUES('Sean Micheal','e@example.com','accounting','accountant')");
$conn->exec("INSERT INTO record(name,email,department,position)VALUES('Olive Sarah','e@example.com','accounting','accountant')");
$conn->exec("INSERT INTO record(name,email,department,position)VALUES('Micheal pater','e@example.com','I.T','Web Developer')");
$conn->exec("INSERT INTO record(name,email,department,position)VALUES('Ayo Brooks','e@example.com','I.T','Senior Web And Mobile Developer')");
$conn->exec("INSERT INTO record(name,email,department,position)VALUES('Bran Judge','e@example.com','I.T','Senior Web And Mobile Developer')");
//we commit the transaction
$conn->commit();
echo "New Record created successfully";
}
catch(PDOException $e){
//roll back the transaction
$conn->rollback();
echo "An error occured:".$e->getMessage();
}
$conn=null;//close the database connection
?>
Make sure to load the script above into your browser- it populates the database.
1.3 Connecting Everything
The script below makes a call to our ajax function and loads the details about each person in the drop down box every time we select a new name.
index1.html
<!DOCTYPE html>
<html lang=en>
<head>
<style>
</style>
<meta charset="utf-8" />
<meta name="viewport" content="width=device-width, initial-scale=1.0, maximum-scale=1.0, user-scalable=no, target-densitydpi=device-dpi"/>
<title> Ajax, Php and mysql example </title>
</head>
<body>
<script>
function connect(value){
// code for IE6, IE5
if (window.XMLHttpRequest) {
// code for IE7+, Firefox, Chrome, Opera, Safari
xmlhttp =new XMLHttpRequest();
} else {//
xmlhttp =new ActiveXObject("Microsoft.XMLHTTP");
}
xmlhttp.onreadystatechange = function() {
if(this.readyState == 4 && this.status == 200) {//received full server response and check the server response
document.getElementById("data").innerHTML=" ";
document.getElementById("data").innerHTML=this.responseText;
}
};
xmlhttp.open("GET", "index1.php?id="+value, true);
xmlhttp.send();
}
</script>
<select name='users' onchange=connect(this.value)>
<option value=0> Select an option </option>
<option value=1>John</option>
<option value=2>Mary</option>
<option value=3>peace</option>
<option value=4>Sean</option>
<option value=5>Olive</option>
<option value=6>Micheal</option>
</select>
<div id=data>
</div>
</body>
</html>
The script below is the server side script our ajax script talks too. It fetches the required information by matching the number passed to it with the primary key of each record.
index1.php
<?php
require_once("db.php");
if(isset($_GET['id'])){
$id=$_GET['id'];
$sql="select * from record where id=:id";
$st=$conn->prepare($sql);
$st->bindValue(":id",$id);
$res=$st->execute();
while($res = $st->fetchObject()){//loop through the returned rows
echo "<p>";
echo "<b>ID: </b>$res->id <br> <b> Names: </b> $res->name <br> <b> Department: </b> $res->department <br> <b> Position </b> $res->position <br> <b> Email: </b> $res->email <br>";
echo"</p>";
}
}
else{
echo "<h2> Wrong Request </h2> ";
}
?>
2. Summary
In this example we learnt about ajax, what it is and how we can effectively use it. We also developed a simple web app that manipulates a database from an ajax function call.
3. Download the source code
You can download the full source code of this example here: phpajaxmysqlexample
Nice tricks for using ajax and mysql in php very useful