php PDO and jQuery AJAX tutorial with example source code

php PDO tutorial with jQuery AJAX

PDO or PHP Data Objects is an extension of php that is used to access databases. In a previous post php MySQLi tutorial, we learned about mysqli extension. In this tutorial we are going to learn about php PDO library. mysqli extension is used with MySQL database only while PDO supports 12 different databases.

php pdo ajax jquery example

To use PDO you have to specify a database driver like PDO_MYSQL (MySQL), PDO_PGSQL (PostgreSQL), PDO_SQLITE (SQLite) or PDO_OCI (Oracle Call Interface) and so on. PDO provides an object oriented interface. It is an abstractions layer that provides same functions for all databases to perform queries. PDO is available after PHP version 5.1.

In this tutorial we are going to create an online store database to view, add, edit and delete records using php PDO and jQuery AJAX.

To explore PDO functions we are going to

1. Create a database onlinestore

2. Create a table for products

3. Display products from mysql database on view page.

4. Add records to MySQL database using jQuery AJAX

5. Edit records in database using php PDO and jQuery AJAX

6. Delete records from database using php PDO and jQuery AJAX

Create a database table and insert data

Open PHPMyAdmin and run queries given below to create a database onlinestore, a table tbl_products and insert data into the table.

CREATE DATABASE onlinestore;

CREATE TABLE `tbl_products` (
  `id` int(11) NOT NULL,
  `product_name` varchar(500) NOT NULL,
  `price` varchar(500) NOT NULL,
  `category` varchar(500) NOT NULL
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8;


INSERT INTO `tbl_products` (`id`, `product_name`, `price`, `category`) VALUES
(1, 'Samsung Galaxy S7 Edge', '$600', 'Mobile Phone'),
(2, 'Google nexus', '$450', 'Mobile Phone'),
(3, 'Apple IPhone 6', '$630', 'Mobile Phone'),
(4, 'Sony Vio', '$1200', 'Laptop'),
(5, 'Samsung T.V', '$900', 'T.V'),
(6, 'Apple IPAD', '$710', 'Tablet'),
(7, 'MacBook Pro', '$1000', 'Laptop'),
(8, 'Dell Laptop', '$950', 'Laptop'),
(9, 'Canon EOS 700D DSLR Camera', '$550', 'Camera'),
(10, 'Nikon D7100 DSLR Camera ', '$670', 'Camera');


ALTER TABLE `tbl_products`
  ADD PRIMARY KEY (`id`);

ALTER TABLE `tbl_products`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT,AUTO_INCREMENT=11;

View records in database table

After executing queries data can be viewed in database table.

view records php pdo database structure

Connect to MySQL database using PHP PDO

In order to connect to a database, we have to create an instance of PDO class. PDO class constructor  new PDO is called and database driver name, host name, username and password are passed.

If connection is successful an object of PDO class is returned otherwise an exception is thrown.

<?php

$username  = 'root';

$password  = '';

try {

    $dbconn = new PDO('mysql:host=localhost;dbname=onlinestore', $username, $password);
    

} catch (PDOException $e) {

    print "Error!: " . $e->getMessage() . "<br/>";

    die();

}

$dbconn = null;

?>

Fetch a data row using php PDO fetch method

fetch method fetches a single row of data from result set. You can specify  fetch style of records.  fetch style is used to fetch records using one of PDO::FETCH_*  constants. Commonly used  are

PDO::FETCH_ASSOC – To return a row as associative array. column names of result set are used as indexes of array.

PDO::FETCH_BOTH (default) – Returns a row from result set as array of column names and also a column number  array starting from 0.

PDO::FETCH_NUM – Returns a row from result set as column number from result set starting from 0.

First dbconn.php is included to get PDO database connection object. Secondly a query is passed to prepare method and fetch method returns data row from result set.

require_once('dbconn.php');

$sth = $dbconn->prepare("SELECT `id`, `product_name`, `price`, `category` FROM tbl_products");

$sth->execute();

/* PDOStatement::FETCH_ASSOC */
$row = $sth->fetch(PDO::FETCH_ASSOC);

$dbconn = null; 

?>

Display a single database record

$row contains data as an associative array. Data can be displayed by using column names.

<table>
     <tr>

       <th>#</th>
       <th>Product Name</th>
       <th>Price</th>
       <th>Category</th>

     </tr>
 
    <?php if($sth->rowCount()): ?>

      <tr>
        <td><?php echo $row['id']; ?></td>
        <td><?php echo $row['product_name']; ?></td>
        <td><?php echo $row['price']; ?></td>
        <td><?php echo $row['category']; ?></td>
      </tr>
 
   <?php endif; ?>

   </table>

Fetch all records using  fetch method

To display all records you have to use a while loop. After database connection is established, a query is prepared  and is ran using execute method.

<?php

/* Fetch records and display in a loop */

require_once('dbconn.php');

$sth = $dbconn->prepare("SELECT `id`, `product_name`, `price`, `category` FROM tbl_products");

$sth->execute();

?>

Display all records using a while loop

execute() method returns a result set. HTML table is created to display  #, name, price and categoryrowCount method is used to determine if there are records in result set.

Here fetch method is used with FETCH_ASSOC flag in a while loop.

<table>

      <tr>
        <th>#</th>
        <th>Product Name</th>
        <th>Price</th>
        <th>Category</th>
      </tr>

   <?php if($sth->rowCount()):

     while($row = $sth->fetch(PDO::FETCH_ASSOC)){ ?>

       <tr>
         <td><?php echo $row['id']; ?></td>
         <td><?php echo $row['product_name']; ?></td>
         <td><?php echo $row['price']; ?></td>
         <td><?php echo $row['category']; ?></td>
       </tr>

     <?php }  ?>

  <?php endif; ?>
   
</table>

Image:

php pdo fetch all records and display in while loop

Fetch data using php PDO fetch_all method

fetch_all method returns array of all result set rows. Rows fetch style can be set. If there are no records an empty array is returned.

<?php

require_once('dbconn.php');

$sth = $dbconn->prepare("SELECT id, product_name, price, category FROM tbl_products ");

$sth->execute();

$result = $sth->fetchAll();

?>

Display all records from database

If there are records in result set, all results are displayed using a foreach loop.

<table>
      <tr>
        <th>#</th>
        <th>Product Name</th>
        <th>Price</th>
        <th>Category</th>
      </tr>
 
  <?php

  if($sth->rowCount()):

     foreach($result as $row){     ?>

      <tr>
        <td><?php echo $row['id']; ?></td>
        <td><?php echo $row['product_name']; ?></td>
        <td><?php echo $row['price']; ?></td>
        <td><?php echo $row['category']; ?></td>
      </tr>

    <?php }  ?>

   <?php endif; ?>

  </table>

Insert records using php PDO

To insert records into database table we first create a form and add jQuery code to send an AJAX request to server. On server side we create a php page to insert data into database using php PDO methods.

HTML form and jQuery AJAX code

The form contains hidden input field named id, text fields for product name, price, category and a button to save records.

<form>

       <table>

        <tr>

          <td colspan="4" style="text-align: center">

          <input type="hidden" id ='prod_id' value='' />

          <input type='text' id='product_name' placeholder='Product' required />&nbsp;&nbsp;

          <input type='text' id='price' placeholder='Price' required />&nbsp;&nbsp;

          <input type='text' id='category' placeholder='Category' required />&nbsp;&nbsp;

          <input type='button' id='saverecords'  value ='Add Records' /></td>

        </tr>

      </table>

    </form>

The code below uses jQuery library. Using jQuery’s .on method click event of saverecords button is executed. Using jQuery’s .val() method we get values of all fields.

If there is no value for prod_id field then AJAX request is posted to add_records_ajax.php. $.post method of jQuery is used to to send AJAX request to server. This method takes URL of php page, form data and sends it to server.  Server processes the data and response is sent back to client.  .done method is a callback function that displays a success or error message.

<script src="https://code.jquery.com/jquery-3.1.1.min.js"></script>

<script>

 $(function(){
     
       /* Edit button ajax call */
       $('#saverecords').on( 'click', function(){
           
           var prod_id  = $('#prod_id').val();

           var product = $('#product_name').val();

           var price   = $('#price').val();

           var category = $('#category').val();
           
            if(!product || !price || !category){
             
                $('.error').show(3000).html("All fields are required.").delay(3200).fadeOut(3000);

           }else{

             if(prod_id){

                    var url = 'edit_record_ajax.php';

              }else{

                   var url = 'add_records_ajax.php';
              }

                $.post( url, {prod_id:prod_id, product: product, category: category, price: price  })
               .done(function( data ) {

                 if(data > 0){

                   $('.success').show(3000).html("Record saved successfully.").delay(2000).fadeOut(1000);

                 }else{

                   $('.error').show(3000).html("Record could not be saved. Please try again.").delay(2000).fadeOut(1000);
                 }

                 $("#saverecords").val('Add Records');

                 setTimeout(function(){
                     window.location.reload(1);

                 }, 15000);

             });
          }

       });

  });

php code to insert data to database

First dbconn.php is included for database connection. Product name, price and category values are in $_POST super global array. An insert query is prepared to insert records. In Insert query named placeholders :product, :price  and :category   are used in place of actual variables.

In bindParam method we pass two parameters, a named placeholder and value of field. If  execute method runs successfully $result is set to 1. else 0 is returned to client.

<?php

  require_once('dbconn.php');

  $result = 0;
  
  $product  = trim($_POST["product"]);

  $price       = trim($_POST["price"]);

  $category = trim($_POST["category"]);

//prepare sql and bind parameters
   $stmt = $dbconn->prepare("INSERT INTO tbl_products(product_name, price, category) VALUES (:product, :price, :category)");
    
    $stmt->bindParam(':product', $product);

    $stmt->bindParam(':price', $price);

    $stmt->bindParam(':category', $category);

    // insert a row
    if($stmt->execute()){

      $result =1;

    }

    echo $result;

    $dbconn = null;

Image:

php pdo add records using prepared statements

Update records using php PDO

In the image above you can see an Edit link in front of each record. Clicking on Edit link sends an AJAX GET request and fills in the data into form fields.

Edit link is created as shown below. A class editbtn is assigned to link, a data attribute data-pid is assigned a value $row[‘id’] .

<a data-pid = <?php echo $row['id']; ?> class='editbtn' href= 'javascript:void(0)'>Edit</a>

 AJAX code to send request to Server

Clicking on an edit link of a record runs the jQuery code given  below. It gets value of data attribute that was specified in data-pid attribute in link using jQuery .data method.  editbtn class is applied to all the links in records row $(this) represents the currently clicked link and get its id value.

    /* Edit button ajax call */
$('.editbtn').on( 'click', function(){

      var pid = $(this).data('pid');
     
      $.get( "getrecord_ajax.php", { id: pid })
           .done(function( product ) {
      
             data = $.parseJSON(product);

             if(data){
      
               $('#prod_id').val(data.id);
      
              $('#product_name').val(data.product_name);
     
             $('#price').val(data.price);
              
             $('#category').val(data.category);
             
             $("#saverecords").val('Save Records');
           
        }
        
      });
     
});

The code above sends an AJAX request to server with product id. Server receives request, prepares a query template, binds named placeholders values and executes the query. Data is retrieved from result set using fetch method as an associative array. Data array is JSON encoded and sent back to client.

require_once('dbconn.php');

  $id  = trim($_GET["id"]);

 // prepare sql and bind parameters
   $stmt = $dbconn->prepare("select * from tbl_products where id = :id");

   $stmt->bindParam(':id', $id);

   // insert a row
   $stmt->execute();

   $data = $stmt->fetch(PDO::FETCH_ASSOC);

   echo json_encode($data);

   $dbconn = null;

As data is filled into the form fields  ‘Add Records’ button value changes to ‘Save Records’.

AJAX request to update records using php PDO

When a user clicks on ‘Save Records’ button an AJAX request is fired and executes the code given below.  We get values of product id, product name, price and category from form. Form data is sent to edit_record_ajax.php page. If record is updated successfully a success message is displayed.

$('#saverecords').on( 'click', function(){

       var prod_id  = $('#prod_id').val();

       var product = $('#product_name').val();

       var price   = $('#price').val();

       var category = $('#category').val();

       if(!product || !price || !category){

         $('.error').show(3000).html("All fields are required.").delay(3200).fadeOut(3000);

       }else{

            if(prod_id){

            var url = 'edit_record_ajax.php';

          }else{

            var url = 'add_records_ajax.php';
          }

            $.post( url, {prod_id:prod_id, product: product, category: category, price: price  })

           .done(function( data ) {

             if(data > 0){

               $('.success').show(3000).html("Record saved successfully.").delay(2000).fadeOut(1000);

             }else{

               $('.error').show(3000).html("Record could not be saved. Please try again.").delay(2000).fadeOut(1000);

             }

             $("#saverecords").val('Add Records');
            
         });
      }

   });

php code to update data in in database

The code below receives data from client in $_POST super global array and assigns it to variables. An update query is prepared with named placeholders Next placeholders are bound using bindParam method. If query executes successfully then $result = 1 is sent to client.

<?php

   require_once('dbconn.php');
 
   $result =0; 
 
   $prod_id  = trim($_POST["prod_id"]);
   
   $product  = trim($_POST["product"]);
   
   $price    = trim($_POST["price"]);
   
   $category = trim($_POST["category"]);
   
    // prepare sql and bind parameters
    
    $stmt = $dbconn->prepare("UPDATE tbl_products 
    set product_name = :product, price = :price , category = :category
    where id = :id");
    
    $stmt->bindParam(':product', $product);
    
    $stmt->bindParam(':price', $price);
    
    $stmt->bindParam(':category', $category);
    
    $stmt->bindParam(':id', $prod_id);
   
    
   if($stmt->execute()){
    
      $result =1;

    }

    echo $result;

    $dbconn = null;

?>

Delete records from database using php PDO

In front of each record there is a delete link. Delete link has a class delbtn. A data attribute is also added as data-pid and is assigned a value $row[‘id’].

<a class='delbtn' data-pid=<?php echo $row['id']; ?> href='javascript:void(0)'>Delete</a>

 jQuery AJAX code to send a delete request to server

When a user clicks on delete link click event is fired and a confirmation messages is displayed.

php pdo record delete records using prepared statements

We get the value of product id from data attribute using .data method of jQuery. A post request is sent to delete_ajax.php page with pid as parameter. If record is deleted successfully from database a success message is displayed to the user on client side.

/* Delete button ajax call */

     $('.delbtn').on( 'click', function(){
        
        if(confirm('This action will delete this record. Are you sure?')){
          
          var pid = $(this).data('pid');
          
          $.post( "delete_ajax.php", { pid: pid })
          
          .done(function( data ) {
            
            if(data > 0){
            
              $('.success').show(3000).html("Record deleted successfully.").delay(3200).fadeOut(6000);
            
            }else{
            
              $('.error').show(3000).html("Record could not be deleted. Please try again.").delay(3200).fadeOut(6000);;
            
            }

            setTimeout(function(){
                window.location.reload(1);
            }, 5000);

          });
        }
     });

On server side first connect to database in dbconn.php. Value of product id is assigned to $id. After query is executed and record is deleted successfully, success flag is returned to client.

 require_once('dbconn.php');

 $result = 0;

 $id = intval($_POST['pid']);

if(intval($id)){

  $stmt = $dbconn->prepare("DELETE FROM tbl_products WHERE id = :id");

  $stmt->bindParam(':id', $id, PDO::PARAM_INT);

  if($stmt->execute()){

    $result = 1;
  }

}

echo $result;

$dbconn = null;

Summary

To sum up, in this tutorial we explored in detail how to use php PDO  class methods to select, add, edit, or delete records from database. You can download example source code used in this tutorial from the link given below. Please leave your comments below. Stay tuned for more tutorials.

Click to download tutorial source code