Home » PHP » PHP, Ajax and Mysql Example

About Olayemi Odunayo

Olayemi Odunayo
I am a programmer and web developer, who has experience in developing websites and writing desktop and mobile applications. I have worked with both schematic and schemaless databases. I am also familiar with third party API and working with cloud servers. I do programming on the client side with Java, JavaScript, html, Ajax and CSS while I use PHP for server side programming.

PHP, Ajax and Mysql Example

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

Want to be a PHP master?

Subscribe to our newsletter and download the PHP programming Cookbook right now!

In order to get you familiarized with PHP development, we have compiled a comprehensive guide about all major aspects of PHP programming. The perfect tool to boost your WEB development career. Besides reading them online you may download the eBook in PDF format!

 

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',[email protected]','accounting','accountant')");
$conn->exec("INSERT INTO record(name,email,department,position)VALUES('Mary Daniel',[email protected]','accounting','accountant')");
$conn->exec("INSERT INTO record(name,email,department,position)VALUES('Peace Daniel',[email protected]','accounting','accountant')");
$conn->exec("INSERT INTO record(name,email,department,position)VALUES('Sean Micheal',[email protected]','accounting','accountant')");
$conn->exec("INSERT INTO record(name,email,department,position)VALUES('Olive Sarah',[email protected]','accounting','accountant')");
$conn->exec("INSERT INTO record(name,email,department,position)VALUES('Micheal pater',[email protected]','I.T','Web Developer')");
$conn->exec("INSERT INTO record(name,email,department,position)VALUES('Ayo Brooks',[email protected]','I.T','Senior Web And Mobile Developer')");
$conn->exec("INSERT INTO record(name,email,department,position)VALUES('Bran Judge',[email protected]','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

Download
You can download the full source code of this example here: phpajaxmysqlexample

Do you want to know how to develop your skillset to become a Web Rockstar?

Subscribe to our newsletter to start Rocking right now!

To get you started we give you our best selling eBooks for FREE!

 

1. Building web apps with Node.js

2. HTML5 Programming Cookbook

3. CSS Programming Cookbook

4. AngularJS Programming Cookbook

5. jQuery Programming Cookbook

6. Bootstrap Programming Cookbook

 

and many more ....

 

 

Leave a Reply

Your email address will not be published. Required fields are marked *

*

Want to take your WEB dev skills to the next level?

Grab our programming books for FREE!

Here are some of the eBooks you will get:

  • PHP Programming Cookbook
  • jQuery Programming Cookbook
  • Bootstrap Programming Cookbook
  • Building WEB Apps with Node.js
  • CSS Programming Cookbook
  • HTML5 Programming Cookbook
  • AngularJS Programming Cookbook