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.


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:
  1. --
  2. -- Table structure for table `members`
  3. --
  4.  
  5. CREATE TABLE IF NOT EXISTS `members` (
  6. `id` int(11) NOT NULL AUTO_INCREMENT,
  7. `full_name` varchar(100) COLLATE utf8_unicode_ci NOT NULL,
  8. `email` varchar(100) COLLATE utf8_unicode_ci NOT NULL,
  9. `age` int(11) NOT NULL,
  10. PRIMARY KEY (`id`)
  11. ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=7 ;
  12.  
  13. --
  14. -- Dumping data for table `members`
  15. --
  16.  
  17. INSERT INTO `members` (`id`, `full_name`, `email`, `age`) VALUES
  18. (1, 'Ali Aboussebaba', 'bewebdeveloper@gmail.com', 27),
  19. (2, 'Paul Xavier', 'paul@domain.com', 22),
  20. (3, 'Rachid Kabbour', 'rachid@domain.com', 25),
  21. (4, 'Christien Davinci', 'christien@domain.com', 32),
  22. (5, 'Vladimir Richo', 'vladimir@domain.com', 40),
  23. (6, 'Omar Alami', 'omar@domain.com', 28);

3. Files

index.php

This is the main file, it will be displayed on the first view
  1. <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
  2. <html xmlns="http://www.w3.org/1999/xhtml">
  3. <head>
  4. <meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
  5. <title>Using Ajax with PHP/MySQL</title>
  6. <link rel="stylesheet" href="css/style.css" />
  7. <script type="text/javascript" src="js/script.js"></script>
  8. </head>
  9.  
  10. <body>
  11. <div class="container">
  12. <div class="header">
  13. <img src="images/BeWebDeveloper.png" />
  14. </div><!-- header -->
  15. <h1 class="main_title">Using Ajax with PHP/MySQL</h1>
  16. <div class="content">
  17. <fieldset class="field_container">
  18. <legend> <img src="images/plus.gif"> Add new member </legend>
  19. <form>
  20. <input type="text" id="full_name" class="frm_input" placeholder="Full name">
  21. <input type="text" id="email" class="frm_input" placeholder="Email">
  22. <input type="text" id="age" class="frm_input" placeholder="Age">
  23. <input type="button" class="frm_button" value="Add" onclick="add_member()">
  24. </form>
  25. </fieldset>
  26. <fieldset class="field_container">
  27. <legend> <img src="images/arrow.gif"> Members list </legend>
  28. <div id="list_container">
  29. <?php
  30. // including the config file
  31. include('config.php');
  32. $pdo = connect();
  33. // list_members : this file displays the list of members in a table view
  34. include('list_members.php');
  35. ?>
  36. </div><!-- list_container -->
  37. </fieldset>
  38. </div><!-- content -->
  39. <div class="footer">
  40. Powered by <a href="#">bewebdeveloper.com</a>
  41. </div><!-- footer -->
  42. </div><!-- container -->
  43. </body>
  44. </html>

config.php

This is the config file. It contain the connection to the database
  1. <?php
  2. // PDO connect *********
  3. function connect() {
  4. $host = 'localhost';
  5. $db_name = 'listing_members';
  6. $db_user = 'root';
  7. $db_password = '';
  8. 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"));
  9. }
  10. ?>

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.
  1. <table class="table_list" cellspacing="2" cellpadding="0">
  2. <tr class="bg_h">
  3. <th>Full name</th>
  4. <th>Email</th>
  5. <th>Age</th>
  6. <th>Delete member</th>
  7. </tr>
  8. <?php
  9. // display the list of all members in table view
  10. $sql = "SELECT * FROM members ORDER BY id ASC";
  11. $query = $pdo->prepare($sql);
  12. $query->execute();
  13. $list = $query->fetchAll();
  14. $bg = 'bg_1';
  15. foreach ($list as $rs) {
  16. ?>
  17. <tr class="<?php echo $bg; ?>">
  18. <td><?php echo $rs['full_name']; ?></td>
  19. <td><?php echo $rs['email']; ?></td>
  20. <td><?php echo $rs['age']; ?></td>
  21. <td><a href="#" class="delete_m" onclick="delete_member(<?php echo $rs['id']; ?>)"><img src="images/delete.png"> Delete</a></td>
  22. </tr>
  23. <?php
  24. if ($bg == 'bg_1') {
  25. $bg = 'bg_2';
  26. } else {
  27. $bg = 'bg_1';
  28. }
  29. }
  30. ?>
  31. </table>

add_member.php

This script will be executed by Ajax to add new member
  1. <?php
  2. include('config.php');
  3. $pdo = connect();
  4. // adding new member using PDO with try/catch to escape the exceptions
  5. try {
  6. $sql = "INSERT INTO members (full_name, email, age) VALUES (:full_name, :email, :age)";
  7. $query = $pdo->prepare($sql);
  8. $query->bindParam(':full_name', $_POST['full_name'], PDO::PARAM_STR);
  9. $query->bindParam(':email', $_POST['email'], PDO::PARAM_STR);
  10. $query->bindParam(':age', $_POST['age'], PDO::PARAM_STR);
  11. $query->execute();
  12. } catch (PDOException $e) {
  13. echo 'PDOException : '. $e->getMessage();
  14. }
  15.  
  16. // list_members : this file displays the list of members in a table view
  17. include('list_members.php');
  18. ?>

delete_member.php

This script will be executed by Ajax to delete a specific member
  1. <?php
  2. include('config.php');
  3. $pdo = connect();
  4. // deleting a member using PDO with try/catch to escape the exceptions
  5. try {
  6. $sql = "DELETE FROM members WHERE id = :id";
  7. $query = $pdo->prepare($sql);
  8. $query->bindParam(':id', $_POST['id'], PDO::PARAM_INT);
  9. $query->execute();
  10. } catch (PDOException $e) {
  11. echo 'PDOException : '. $e->getMessage();
  12. }
  13.  
  14. // list_members : this file displays the list of members in a table view
  15. include('list_members.php');
  16. ?>

script.js

This is the JavaScript file, so Ajax is there
  1. // add_member function
  2. function add_member() {
  3. // initialisation
  4. var url = 'add_member.php';
  5. var method = 'POST';
  6. var params = 'full_name='+document.getElementById('full_name').value;
  7. params += '&email='+document.getElementById('email').value;
  8. params += '&age='+document.getElementById('age').value;
  9. var container_id = 'list_container' ;
  10. var loading_text = '<img src="images/ajax_loader.gif">' ;
  11. // call ajax function
  12. ajax (url, method, params, container_id, loading_text) ;
  13. }
  14.  
  15. // delete_member function
  16. function delete_member(id) {
  17. if (confirm('Are you sur to delete this member ?')) {
  18. // initialisation
  19. var url = 'delete_member.php';
  20. var method = 'POST';
  21. var params = 'id='+id;
  22. var container_id = 'list_container' ;
  23. var loading_text = '<img src="images/ajax_loader.gif">' ;
  24. // call ajax function
  25. ajax (url, method, params, container_id, loading_text) ;
  26. }
  27. }
  28.  
  29. // ajax : basic function for using ajax easily
  30. function ajax (url, method, params, container_id, loading_text) {
  31. try { // For: chrome, firefox, safari, opera, yandex, ...
  32. xhr = new XMLHttpRequest();
  33. } catch(e) {
  34. try{ // for: IE6+
  35. xhr = new ActiveXObject("Microsoft.XMLHTTP");
  36. } catch(e1) { // if not supported or disabled
  37. alert("Not supported!");
  38. }
  39. }
  40. xhr.onreadystatechange = function() {
  41. if(xhr.readyState == 4) { // when result is ready
  42. document.getElementById(container_id).innerHTML = xhr.responseText;
  43. } else { // waiting for result
  44. document.getElementById(container_id).innerHTML = loading_text;
  45. }
  46. }
  47. xhr.open(method, url, true);
  48. xhr.setRequestHeader("Content-Type","application/x-www-form-urlencoded");
  49. xhr.send(params);
  50. }

style.css

This is the CSS file, it contain the design styles
  1. * {
  2. margin: 0;
  3. padding: 0;
  4. }
  5. body {
  6. padding: 10px;
  7. background: #eaeaea;
  8. text-align: center;
  9. font-family: arial;
  10. font-size: 12px;
  11. color: #333333;
  12. }
  13. .container {
  14. width: 1000px;
  15. height: auto;
  16. background: #ffffff;
  17. border: 1px solid #cccccc;
  18. border-radius: 10px;
  19. margin: auto;
  20. text-align: left;
  21. }
  22. .header {
  23. padding: 10px;
  24. }
  25. .main_title {
  26. background: #cccccc;
  27. color: #ffffff;
  28. padding: 10px;
  29. font-size: 20px;
  30. line-height: 20px;
  31. }
  32. .content {
  33. padding: 10px;
  34. min-height: 100px;
  35. }
  36. .footer {
  37. padding: 10px;
  38. text-align: right;
  39. }
  40. .footer a {
  41. color: #999999;
  42. text-decoration: none;
  43. }
  44. .footer a:hover {
  45. text-decoration: underline;
  46. }
  47. .field_container {
  48. padding: 10px;
  49. margin: 0 0 10px 0;
  50. border: 1px solid #cccccc;
  51. border-radius: 10px;
  52. }
  53. .field_container legend {
  54. padding: 0 5px 0 5px;
  55. font-size: 14px;
  56. font-weight: bold;
  57. }
  58. .input_container {
  59. height: 30px;
  60. float: left;
  61. }
  62. .frm_input {
  63. height: 20px;
  64. width: 200px;
  65. padding: 3px;
  66. border: 1px solid #cccccc;
  67. border-radius: 5px;
  68. margin: 0 5px 0 0;
  69. }
  70. .frm_button {
  71. height: 28px;
  72. width: 100px;
  73. padding: 3px;
  74. border: 1px solid #cccccc;
  75. border-radius: 5px;
  76. background: #999999;
  77. color: #ffffff;
  78. font-weight: bold;
  79. cursor: pointer;
  80. }
  81. .frm_button:hover {
  82. background: #cccccc;
  83. }
  84. .table_list {
  85. width: 100%;
  86. border: 0;
  87. }
  88. .table_list td, .table_list th {
  89. padding: 2px;
  90. }
  91. .delete_m {
  92. color: #666666;
  93. text-decoration: none;
  94. font-weight: bold;
  95. }
  96. .delete_m:hover {
  97. color: #999999;
  98. }
  99. .delete_m img {
  100. height: 12px;
  101. }
  102. .bg_h {
  103. background: #8d8b8b;
  104. color: #ffffff;
  105. text-align: center;
  106. }
  107. .bg_1 {
  108. background: #e3e2e2;
  109. text-align: center;
  110. }
  111. .bg_2 {
  112. background: #c0bfbf;
  113. text-align: center;
  114. }
  115. #list_container {
  116. text-align: center;
  117. }
source : http://www.bewebdeveloper.com/tutorial-about-using-ajax-with-phpmysql

Post a Comment