Using Ajax with PHP/MySQL

Using Ajax with PHP/MySQL

Hi guys, in this tutorial, w’ll use Ajax with PHP/MySQL. A small administration page, with listing, adding and deleting members using Ajax with PHP/MySQL.

Download source code

This example displays a web page with “Add new member” form and the list of members in a table view with “Delete” link. When you click the “Add” button, a new member will be added dynamically to the database without refreshing the whole page. The same thing for the “delete” link.

Using Ajax with PHP/MySQL

1. Structure

/css : contain the CSS files
/js : contain the JavaScript files
/images : contain all images

2. Database

Create a new database named “listing_members”, and import the SQL script in main folder, or copy/paste the script bellow:

--
-- Table structure for table `members`
--

CREATE TABLE IF NOT EXISTS `members` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `full_name` varchar(100) COLLATE utf8_unicode_ci NOT NULL,
  `email` varchar(100) COLLATE utf8_unicode_ci NOT NULL,
  `age` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=7 ;

--
-- Dumping data for table `members`
--

INSERT INTO `members` (`id`, `full_name`, `email`, `age`) VALUES
(1, 'Ali Aboussebaba', 'bewebdeveloper@gmail.com', 27),
(2, 'Paul Xavier', 'paul@domain.com', 22),
(3, 'Rachid Kabbour', 'rachid@domain.com', 25),
(4, 'Christien Davinci', 'christien@domain.com', 32),
(5, 'Vladimir Richo', 'vladimir@domain.com', 40),
(6, 'Omar Alami', 'omar@domain.com', 28);

3. Files

index.php

This is the main file, it will be displayed on the first view

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>Using Ajax with PHP/MySQL</title>
<link rel="stylesheet" href="css/style.css" />
<script type="text/javascript" src="js/script.js"></script>
</head>

<body>
    <div class="container">
        <div class="header">
            <img src="images/BeWebDeveloper.png" />
        </div><!-- header -->
        <h1 class="main_title">Using Ajax with PHP/MySQL</h1>
        <div class="content">
            <fieldset class="field_container">
                <legend> <img src="images/plus.gif"> Add new member </legend>
                <form>
                    <input type="text" id="full_name" class="frm_input" placeholder="Full name">
                    <input type="text" id="email" class="frm_input" placeholder="Email">
                    <input type="text" id="age" class="frm_input" placeholder="Age">
                    <input type="button" class="frm_button" value="Add" onclick="add_member()">
                </form>
            </fieldset>
            <fieldset class="field_container">
                <legend> <img src="images/arrow.gif"> Members list </legend>
                <div id="list_container">
                    <?php 
                        // including the config file
                        include('config.php');
                        $pdo = connect();
                        // list_members : this file displays the list of members in a table view
                        include('list_members.php'); 
                    ?>
                </div><!-- list_container -->
            </fieldset>
        </div><!-- content -->    
        <div class="footer">
            Powered by <a href="#">bewebdeveloper.com</a>
        </div><!-- footer -->
    </div><!-- container -->
</body>
</html>

config.php

This is the config file. It contain the connection to the database

<?php
// PDO connect *********
function connect() {
	$host = 'localhost';
	$db_name = 'listing_members';
	$db_user = 'root';
	$db_password = '';
    return new PDO('mysql:host='.$host.';dbname='.$db_name, $db_user, $db_password, array(PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION, PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES utf8"));
}
?>

list_members.php

This file displays the list of members in a table view, it will be included in different scripts to refresh members list.

<table class="table_list" cellspacing="2" cellpadding="0">
    <tr class="bg_h">
        <th>Full name</th>
        <th>Email</th>
        <th>Age</th>
        <th>Delete member</th>
    </tr>
    <?php
		// display the list of all members in table view
        $sql = "SELECT * FROM members ORDER BY id ASC";
        $query = $pdo->prepare($sql);
        $query->execute();
        $list = $query->fetchAll();
        $bg = 'bg_1';
        foreach ($list as $rs) {
            ?>
            <tr class="<?php echo $bg; ?>">
                <td><?php echo $rs['full_name']; ?></td>
                <td><?php echo $rs['email']; ?></td>
                <td><?php echo $rs['age']; ?></td>
                <td><a href="#" class="delete_m" onclick="delete_member(<?php echo $rs['id']; ?>)"><img src="images/delete.png"> Delete</a></td>
            </tr>
            <?php
            if ($bg == 'bg_1') {
                $bg = 'bg_2';
            } else {
                $bg = 'bg_1';
            }
        }
    ?>
</table>

add_member.php

This script will be executed by Ajax to add new member

<?php
include('config.php');
$pdo = connect();
// adding new member using PDO with try/catch to escape the exceptions
try {
	$sql = "INSERT INTO members (full_name, email, age) VALUES (:full_name, :email, :age)";
	$query = $pdo->prepare($sql);
	$query->bindParam(':full_name', $_POST['full_name'], PDO::PARAM_STR);
	$query->bindParam(':email', $_POST['email'], PDO::PARAM_STR);
	$query->bindParam(':age', $_POST['age'], PDO::PARAM_STR);
	$query->execute();
} catch (PDOException $e) {
	echo 'PDOException : '.  $e->getMessage();
}

// list_members : this file displays the list of members in a table view
include('list_members.php');
?>

delete_member.php

This script will be executed by Ajax to delete a specific member

<?php
include('config.php');
$pdo = connect();
// deleting a member using PDO with try/catch to escape the exceptions
try {
	$sql = "DELETE FROM members WHERE id = :id";
	$query = $pdo->prepare($sql);
	$query->bindParam(':id', $_POST['id'], PDO::PARAM_INT);
	$query->execute();
} catch (PDOException $e) {
	echo 'PDOException : '.  $e->getMessage();
}

// list_members : this file displays the list of members in a table view
include('list_members.php');
?>

script.js

This is the JavaScript file, so Ajax is there

// add_member function
function add_member() {
	// initialisation
	var url = 'add_member.php';
	var method = 'POST';
	var params = 'full_name='+document.getElementById('full_name').value;
	params += '&email='+document.getElementById('email').value;
	params += '&age='+document.getElementById('age').value;
	var container_id = 'list_container' ;
	var loading_text = '<img src="images/ajax_loader.gif">' ;
	// call ajax function
	ajax (url, method, params, container_id, loading_text) ;
}

// delete_member function
function delete_member(id) {
	if (confirm('Are you sur to delete this member ?')) {
		// initialisation
		var url = 'delete_member.php';
		var method = 'POST';
		var params = 'id='+id;
		var container_id = 'list_container' ;
		var loading_text = '<img src="images/ajax_loader.gif">' ;
		// call ajax function
		ajax (url, method, params, container_id, loading_text) ;
	}
}

// ajax : basic function for using ajax easily
function ajax (url, method, params, container_id, loading_text) {
    try { // For: chrome, firefox, safari, opera, yandex, ...
    	xhr = new XMLHttpRequest();
    } catch(e) {
	    try{ // for: IE6+
	    	xhr = new ActiveXObject("Microsoft.XMLHTTP");
	    } catch(e1) { // if not supported or disabled
		    alert("Not supported!");
		}
	}
	xhr.onreadystatechange = function() {
						       if(xhr.readyState == 4) { // when result is ready
							       document.getElementById(container_id).innerHTML = xhr.responseText;
							   } else { // waiting for result 
							       document.getElementById(container_id).innerHTML = loading_text;
							   }
						   	}
	xhr.open(method, url, true);
	xhr.setRequestHeader("Content-Type","application/x-www-form-urlencoded");
	xhr.send(params);
}

style.css

This is the CSS file, it contain the design styles

* {
	margin: 0;
	padding: 0;
}
body {
	padding: 10px;
	background: #eaeaea;
	text-align: center;
	font-family: arial;
	font-size: 12px;
	color: #333333;
}
.container {
	width: 1000px;
	height: auto;
	background: #ffffff;
	border: 1px solid #cccccc;
	border-radius: 10px;
	margin: auto;
	text-align: left;
}
.header {
	padding: 10px;
}
.main_title {
	background: #cccccc;
	color: #ffffff;
	padding: 10px;
	font-size: 20px;
	line-height: 20px;
}
.content {
	padding: 10px;
	min-height: 100px;
}
.footer {
	padding: 10px;
	text-align: right;
}
.footer a {
	color: #999999;
	text-decoration: none;
}
.footer a:hover {
	text-decoration: underline;
}
.field_container {
	padding: 10px;
	margin: 0 0 10px 0;
	border: 1px solid #cccccc;
	border-radius: 10px;
}
.field_container legend {
	padding: 0 5px 0 5px;
	font-size: 14px;
	font-weight: bold;
}
.input_container {
	height: 30px;
	float: left;
}
.frm_input {
	height: 20px;
	width: 200px;
	padding: 3px;
	border: 1px solid #cccccc;
	border-radius: 5px;
	margin: 0 5px 0 0;
}
.frm_button {
	height: 28px;
	width: 100px;
	padding: 3px;
	border: 1px solid #cccccc;
	border-radius: 5px;
	background: #999999;
	color: #ffffff;
	font-weight: bold;
	cursor: pointer;
}
.frm_button:hover {
	background: #cccccc;
}
.table_list {
	width: 100%;
	border: 0;
}
.table_list td, .table_list th {
	padding: 2px;
}
.delete_m {
	color: #666666;
	text-decoration: none;
	font-weight: bold;
}
.delete_m:hover {
	color: #999999;
}
.delete_m img {
	height: 12px;
}
.bg_h {
	background: #8d8b8b;
	color: #ffffff;
	text-align: center;
}
.bg_1 {
	background: #e3e2e2;
	text-align: center;
}
.bg_2 {
	background: #c0bfbf;
	text-align: center;
}
#list_container {
	text-align: center;
}
Ajax, CSS, HTML, MySQL, PHP
comments powered by Disqus

Social Profiles

bewebdeveloper on facebook bewebdeveloper on twitter bewebdeveloper on google plus bewebdeveloper on vk bewebdeveloper on youtube bewebdeveloper on tumblr bewebdeveloper rss

Subscribe to our Newsletter

Facebook

Twitter

Google+

HTML Tutorials
CSS Tutorials

Advertising