Using Ajax with PHP/MySQL

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.

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;
}

source : http://www.bewebdeveloper.com/tutorial-about-using-ajax-with-phpmysql

Using Ajax with PHP/MySQL

1. Structure

2. Database

3. Files

index.php

config.php

list_members.php

add_member.php

delete_member.php

script.js

style.css

Post a Comment

youtube video

Featured Post

Twitter Unfollower - chrome extension

Categories

Random Posts

Popular Posts

Blog Archive

About Me

Total Pageviews