Best Tech Blog For Programming Articles And Video Tutorials Code Is Easy
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.
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.
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.
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_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 category. rowCount 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:
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 />
<input type='text' id='price' placeholder='Price' required />
<input type='text' id='category' placeholder='Category' required />
<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:
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.
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 the database. You can download the example source code used in this tutorial from the link given below. Please leave your comments below. Stay tuned for more tutorials.
You can download source code from GitHub repository:
Related Articles:
- jQuery AJAX example with PHP MySQL – download source code
- How to create a jQuery drag and drop todo list with PHP MySQL
- PHP MongoDB tutorial – find, insert, update and delete records
- PHP MySQL tutorial for beginners with example source code
