Our tutorial for today is about creating a simple database application with a PHP Object Oriented CRUD Example in mind. We use Bootstrap so that our application will have a decent UI.
If you don’t know what Bootstrap is, and you want to learn how to use it in few steps, I highly recommend following my previous tutorial first.
There are so many PHP OOP tutorial on the web today, they have different examples and implementations. Some might be completely correct, some maybe not.
I’m writing this tutorial with a clear goal: to give the best PHP Object Oriented CRUD Example for beginners. I welcome your comments and suggestions to help me achieve this.
We want to learn the correct PHP OOP implementation. There are PHP frameworks such as CakePHP, CodeIgniter and Laravel that correctly do it.
Those things are one step higher, for now, we will learn the basic PHP Object Oriented Programming. Working with a PHP framework should be easy after following this tutorial.
So here it is, this post will include the following contents:
1.0 Code’s Final Output
1.1 Video
1.2 Screenshots
2.0 Database Table Structure
2.1 Products Table
2.2 Categories Table
3.0 Create the Template Files
3.1 Header Code with header.php
3.2 Footer Code with footer.php
4.0 Creating Record in PHP the OOP Way
4.1 Set the “Create Product” Headers in create_product.php
4.2 Set the Footer
4.3 Create a “Read Products” Button
4.4 Get a Database Connection
4.5 Create the Database Configuration Class
4.6 Create a Form in create_product.php
4.7 Loop Through the Categories Records to show as Drop-down
4.8 Create the Object Class for Categories
4.9 Code when the Form was Submitted
4.10 Create the Object Class for Products
5.0 Reading Record in PHP the OOP Way
5.1 Create index.php and Set the “Read Products” Headers
5.2 Set the Footer
5.3 Add a “Create Product” button
5.4 Configure Pagination Variables
5.5 Retrieve Records from the Database
5.6 readAll() Method in product.php
5.7 Put the Edit and Delete Action Buttons
5.8 Create paging_product.php for Paging Buttons
5.9 Add the countAll() method in product.php
5.10 Include paging_product.php in index.php
6.0 Updating Record in PHP the OOP Way
6.1 Create update_product.php and Set the “Update Product” Headers
6.2 Create a “Read Products” Button
6.3 Retrieve One Product Information Based on the Given ID.
6.4 Add readOne() method in the Product Object Class.
6.5 Put the Values in the Form.
6.6 Loop Through the Categories Records to show as Drop-down
6.7 Code When Form was Submitted
6.8 Update Code in the Product Class
7.0 Deleting Record in PHP the OOP Way
7.1 Put this JavaScript Code in index.php
7.2 Create delete_product.php
7.3 Delete Code in Product Class
8.0 Online Resources
8.1 Download Source Code
Alright, so let’s get started with the codes…
1.0 Code’s Final Output
I believe we have to know where we’re headed, so first, let’s take a look at our code’s final output!
1.1 Video
1.2 Screenshots
Coming Soon.
2.0 Database Table Structure
The files products.sql and categories.sql are also included in the code download, located at the sql/ folder.
2.1 Products Table
Yup, we will use products as objects to maintain in our object oriented CRUD example. Database table and dummy data were provided below, you can instantly run this in your PhpMyAdmin after creating your database.
--
-- Table structure for table `products`
--
CREATE TABLE `products` (
`id` int(11) NOT NULL auto_increment,
`name` varchar(32) NOT NULL,
`description` text NOT NULL,
`price` int(11) NOT NULL,
`category_id` int(11) NOT NULL,
`created` datetime NOT NULL,
`modified` timestamp NOT NULL default CURRENT_TIMESTAMP,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=14 ;
--
-- Dumping data for table `products`
--
INSERT INTO `products` VALUES(1, 'LG P880 4X HD', 'My first awesome phone!', 336, 3, '0000-00-00 00:00:00', '0000-00-00 00:00:00');
INSERT INTO `products` VALUES(2, 'Google Nexus 4', 'The most awesome phone of 2013!', 299, 2, '0000-00-00 00:00:00', '0000-00-00 00:00:00');
INSERT INTO `products` VALUES(3, 'Samsung Galaxy S4', 'How about no?', 600, 3, '0000-00-00 00:00:00', '0000-00-00 00:00:00');
INSERT INTO `products` VALUES(6, 'Bench Shirt', 'The best shirt!', 29, 1, '2014-06-01 01:12:26', '2014-05-31 10:12:21');
INSERT INTO `products` VALUES(7, 'Lenovo Laptop', 'My business partner.', 399, 2, '2014-06-01 01:13:45', '2014-05-31 10:13:39');
INSERT INTO `products` VALUES(8, 'Samsung Galaxy Tab 10.1', 'Good tablet.', 259, 2, '2014-06-01 01:14:13', '2014-05-31 10:14:08');
INSERT INTO `products` VALUES(9, 'Spalding Watch', 'My sports watch.', 199, 1, '2014-06-01 01:18:36', '2014-05-31 10:18:31');
INSERT INTO `products` VALUES(10, 'Sony Smart Watch', 'The coolest smart watch!', 300, 2, '2014-06-06 17:10:01', '2014-06-06 02:09:51');
INSERT INTO `products` VALUES(11, 'Huawei Y300', 'For testing purposes.', 100, 2, '2014-06-06 17:11:04', '2014-06-06 02:10:54');
INSERT INTO `products` VALUES(12, 'Abercrombie Lake Arnold Shirt', 'Perfect as gift!', 60, 1, '2014-06-06 17:12:21', '2014-06-06 02:12:11');
INSERT INTO `products` VALUES(13, 'Abercrombie Allen Brook Shirt', 'Cool red shirt!', 70, 1, '2014-06-06 17:12:59', '2014-06-06 02:12:49');
2.2 Categories Table
We are going to have “Fashion”, “Electronics” and “Motors” as categories in our example. I got those three category ideas from ebay, haha!
--
-- Table structure for table `categories`
--
CREATE TABLE `categories` (
`id` int(11) NOT NULL auto_increment,
`name` varchar(256) NOT NULL,
`created` datetime NOT NULL,
`modified` timestamp NOT NULL default CURRENT_TIMESTAMP,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=4 ;
--
-- Dumping data for table `categories`
--
INSERT INTO `categories` VALUES(1, 'Fashion', '2014-06-01 00:35:07', '2014-05-31 09:34:33');
INSERT INTO `categories` VALUES(2, 'Electronics', '2014-06-01 00:35:07', '2014-05-31 09:34:33');
INSERT INTO `categories` VALUES(3, 'Motors', '2014-06-01 00:35:07', '2014-05-31 09:34:54');
3.0 Create the Template Files
To reduce some mess from our code, we will create these two template files: header.php and footer.php.
I’m actually not sure what these files are called so for now we’ll call them “template files”. It wraps the main content of our web pages. We can imagine it like this:
<?php
include_once 'header.php';
?>
// main content of web page must be here!
<?php
include_once 'footer.php';
?>
3.1 Header Code with header.php
This header.php file will be included at the beginning of some of our core files so that we won’t have to write the same header codes every-time.
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="utf-8">
<meta http-equiv="X-UA-Compatible" content="IE=edge">
<meta name="viewport" content="width=device-width, initial-scale=1">
<title><?php echo $page_title; ?></title>
<!-- some custom CSS -->
<style>
.left-margin{
margin:0 .5em 0 0;
}
.right-button-margin{
margin: 0 0 1em 0;
overflow: hidden;
}
</style>
<!-- Bootstrap -->
<!-- Latest compiled and minified CSS -->
<link rel="stylesheet" href="//netdna.bootstrapcdn.com/bootstrap/3.1.1/css/bootstrap.min.css">
<!-- HTML5 Shiv and Respond.js IE8 support of HTML5 elements and media queries -->
<!-- WARNING: Respond.js doesn't work if you view the page via file:// -->
<!--[if lt IE 9]>
<script src="https://oss.maxcdn.com/libs/html5shiv/3.7.0/html5shiv.js"></script>
<script src="https://oss.maxcdn.com/libs/respond.js/1.4.2/respond.min.js"></script>
<![endif]-->
<!-- jQuery (necessary for Bootstrap's JavaScript plugins) -->
<script src="https://ajax.googleapis.com/ajax/libs/jquery/1.11.0/jquery.min.js"></script>
<!-- Include all compiled plugins (below), or include individual files as needed -->
<!-- Latest compiled and minified JavaScript -->
<script src="//netdna.bootstrapcdn.com/bootstrap/3.1.1/js/bootstrap.min.js"></script>
</head>
<body>
<!-- container -->
<div class="container">
<?php
// show page header
echo "<div class='page-header'>";
echo "<h1>{$page_title}</h1>";
echo "</div>";
?>
3.2 Footer Code with footer.php
This footer.php file will be included at the end of some of our core files so that we won’t have to write the same footer codes every-time.
</div>
<!-- /container -->
</body>
</html>
4.0 Creating Record in PHP the OOP Way
4.1 Set the “Create Product” Headers in create_product.php
<?php
// set page headers
$page_title = "Create Product";
include_once "header.php";
?>
4.2 Set the Footer
<?php
include_once "footer.php";
?>
4.3 Create a “Read Products” Button
This will be put in between the header and footer. Under the “create product” header.
echo "<div class='right-button-margin'>";
echo "<a href='index.php' class='btn btn-default pull-right'>Read Products</a>";
echo "</div>";
4.4 Get a Database Connection
We can use it for retrieving categories or saving new product record later. Put the following code under 4.3 code.
// get database connection
include_once 'config/database.php';
$database = new Database();
$db = $database->getConnection();
4.5 Create the Database Configuration Class
Getting a database connection (4.3 above) will not work without this class. We will name the file database.php and put it inside the config folder.
<?php
class Database{
// specify your own database credentials
private $host = "change_to_your_db_host";
private $db_name = "change_to_your_db_name";
private $username = "change_to_your_db_username";
private $password = "change_to_your_db_password";
public $conn;
// get the database connection
public function getConnection(){
$this->conn = null;
try{
$this->conn = new PDO("mysql:host=" . $this->host . ";dbname=" . $this->db_name, $this->username, $this->password);
}catch(PDOException $exception){
echo "Connection error: " . $exception->getMessage();
}
return $this->conn;
}
}
?>
4.6 Create a Form in create_product.php
<!-- HTML form for creating a product -->
<form action='create_product.php' method='post'>
<table class='table table-hover table-responsive table-bordered'>
<tr>
<td>Name</td>
<td><input type='text' name='name' class='form-control' required></td>
</tr>
<tr>
<td>Price</td>
<td><input type='text' name='price' class='form-control' required></td>
</tr>
<tr>
<td>Description</td>
<td><textarea name='description' class='form-control'></textarea></td>
</tr>
<tr>
<td>Category</td>
<td>
<!-- categories from database will be here -->
</td>
</tr>
<tr>
<td></td>
<td>
<button type="submit" class="btn btn-primary">Create</button>
</td>
</tr>
</table>
</form>
4.7 Loop Through the Categories Records to show as Drop-down
Remember the form earlier? The category part must be like this now:
<tr>
<td>Category</td>
<td>
<?php
// read the product categories from the database
include_once 'objects/category.php';
$category = new Category($db);
$stmt = $category->read();
// put them in a select drop-down
echo "<select class='form-control' name='category_id'>";
echo "<option>Select category...</option>";
while ($row_category = $stmt->fetch(PDO::FETCH_ASSOC)){
extract($row_category);
echo "<option value='{$id}'>{$name}</option>";
}
echo "</select>";
?>
</td>
</tr>
4.8 Create the Object Class for Categories
Of course, 4.3 above won’t work without the category object class. We’ll call it category.php and put it inside objects/ folder.
<?php
class Category{
// database connection and table name
private $conn;
private $table_name = "categories";
// object properties
public $id;
public $name;
public function __construct($db){
$this->conn = $db;
}
// used by select drop-down list
function read(){
//select all data
$query = "SELECT
id, name
FROM
" . $this->table_name . "
ORDER BY
name";
$stmt = $this->conn->prepare( $query );
$stmt->execute();
return $stmt;
}
?>
4.9 Code when the Form was Submitted
This code should be put before the HTML form. User will enter the values in the HTML form and when the create (submit) button was clicked, values will be sent via POST request, code below will save it in the database.
// if the form was submitted
if($_POST){
// instantiate product object
include_once 'objects/product.php';
$product = new Product($db);
// set product property values
$product->name = $_POST['name'];
$product->price = $_POST['price'];
$product->description = $_POST['description'];
$product->category_id = $_POST['category_id'];
// create the product
if($product->create()){
echo "<div class=\"alert alert-success alert-dismissable\">";
echo "<button type=\"button\" class=\"close\" data-dismiss=\"alert\" aria-hidden=\"true\">×</button>";
echo "Product was created.";
echo "</div>";
}
// if unable to create the product, tell the user
else{
echo "<div class=\"alert alert-danger alert-dismissable\">";
echo "<button type=\"button\" class=\"close\" data-dismiss=\"alert\" aria-hidden=\"true\">×</button>";
echo "Unable to create product.";
echo "</div>";
}
}
4.10 Create the Object Class for Products
Of course, 4.9 will not work without the product object. We’ll name the file product.php and put it inside objects/ folder.
<?php
class Product{
// database connection and table name
private $conn;
private $table_name = "products";
// object properties
public $id;
public $name;
public $price;
public $description;
public $category_id;
public $timestamp;
public function __construct($db){
$this->conn = $db;
}
// create product
function create(){
// to get time-stamp for 'created' field
$this->getTimestamp();
//write query
$query = "INSERT INTO
" . $this->table_name . "
SET
name = ?, price = ?, description = ?, category_id = ?, created = ?";
$stmt = $this->conn->prepare($query);
$stmt->bindParam(1, $this->name);
$stmt->bindParam(2, $this->price);
$stmt->bindParam(3, $this->description);
$stmt->bindParam(4, $this->category_id);
$stmt->bindParam(5, $this->timestamp);
if($stmt->execute()){
return true;
}else{
return false;
}
}
}
?>
5.0 Reading Record in PHP the OOP Way
This time we will list the records from the database.
5.1 Create index.php and Set the “Read Products” Headers
<?php
$page_title = "Read Products";
include_once "header.php";
?>
5.2 Set the Footer
<?php
include_once "footer.php";
?>
5.3 Add a “Create Product” button
echo "<div class='right-button-margin'>";
echo "<a href='create_product.php' class='btn btn-default pull-right'>Create Product</a>";
echo "</div>";
5.4 Configure Pagination Variables
// page given in URL parameter, default page is one
$page = isset($_GET['page']) ? $_GET['page'] : 1;
// set number of records per page
$records_per_page = 3;
// calculate for the query LIMIT clause
$from_record_num = ($records_per_page * $page) - $records_per_page;
5.5 Retrieve Records from the Database
// include database and object files
include_once 'config/database.php';
include_once 'objects/product.php';
include_once 'objects/category.php';
// instantiate database and product object
$database = new Database();
$db = $database->getConnection();
$product = new Product($db);
// query products
$stmt = $product->readAll($page, $from_record_num, $records_per_page);
$num = $stmt->rowCount();
// display the products if there are any
if($num>0){
$category = new Category($db);
echo "<table class='table table-hover table-responsive table-bordered'>";
echo "<tr>";
echo "<th>Product</th>";
echo "<th>Price</th>";
echo "<th>Description</th>";
echo "<th>Category</th>";
echo "<th>Actions</th>";
echo "</tr>";
while ($row = $stmt->fetch(PDO::FETCH_ASSOC)){
extract($row);
echo "<tr>";
echo "<td>{$name}</td>";
echo "<td>{$price}</td>";
echo "<td>{$description}</td>";
echo "<td>";
$category->id = $category_id;
$category->readName();
echo $category->name;
echo "</td>";
echo "<td>";
// edit and delete button will be here
echo "</td>";
echo "</tr>";
$x++;
}
echo "</table>";
// paging buttons will be here
}
// tell the user there are no products
else{
echo "<div>No products found.</div>";
}
5.6 readAll() Method in product.php
Retrieving records in 5.5 won’t work without this method in the objects/product.php
function readAll($page, $from_record_num, $records_per_page){
$query = "SELECT
id, name, description, price, category_id
FROM
" . $this->table_name . "
ORDER BY
name ASC
LIMIT
{$from_record_num}, {$records_per_page}";
$stmt = $this->conn->prepare( $query );
$stmt->execute();
return $stmt;
}
5.7 Put the Edit and Delete Action Buttons
echo "<td>";
// edit and delete button is here
echo "<a href='update_product.php?id={$id}' class='btn btn-default left-margin'>Edit</a>";
echo "<a delete-id='{$id}' class='btn btn-default delete-object'>Delete</a>";
echo "</td>";
5.8 Create paging_product.php for Paging Buttons
<?php
// the page where this paging is used
$page_dom = "index.php";
echo "<ul class=\"pagination\">";
// button for first page
if($page>1){
echo "<li><a href='{$page_dom}' title='Go to the first page.'>";
echo "<<";
echo "</a></li>";
}
// count all products in the database to calculate total pages
$total_rows = $product->countAll();
$total_pages = ceil($total_rows / $records_per_page);
// range of links to show
$range = 2;
// display links to 'range of pages' around 'current page'
$initial_num = $page - $range;
$condition_limit_num = ($page + $range) + 1;
for ($x=$initial_num; $x<$condition_limit_num; $x++) {
// be sure '$x is greater than 0' AND 'less than or equal to the $total_pages'
if (($x > 0) && ($x <= $total_pages)) {
// current page
if ($x == $page) {
echo "<li class='active'><a href=\"#\">$x <span class=\"sr-only\">(current)</span></a></li>";
}
// not current page
else {
echo "<li><a href='{$page_dom}?page=$x'>$x</a></li>";
}
}
}
// button for last page
if($page<$total_pages){
echo "<li><a href='" .$page_dom . "?page={$total_pages}' title='Last page is {$total_pages}.'>";
echo ">>";
echo "</a></li>";
}
echo "</ul>";
?>
5.9 Add the countAll() method in objects/product.php
// used for paging products
public function countAll(){
$query = "SELECT id FROM " . $this->table_name . "";
$stmt = $this->conn->prepare( $query );
$stmt->execute();
$num = $stmt->rowCount();
return $num;
}
5.10 Include paging_product.php in index.php
// paging buttons here
include_once 'paging_product.php';
6.0 Updating Record in PHP the OOP Way
6.1 Create update_product.php and Set the “Update Product” Headers
<?php
$page_title = "Update Product";
include_once "header.php";
?>
6.2 Create a “Read Products” Button
echo "<div class='right-button-margin'>";
echo "<a href='index.php' class='btn btn-default pull-right'>Read Products</a>";
echo "</div>";
6.3 Retrieve One Product Information Based on the Given ID.
// get ID of the product to be edited
$id = isset($_GET['id']) ? $_GET['id'] : die('ERROR: missing ID.');
// include database and object files
include_once 'config/database.php';
include_once 'objects/product.php';
// get database connection
$database = new Database();
$db = $database->getConnection();
// prepare product object
$product = new Product($db);
// set ID property of product to be edited
$product->id = $id;
// read the details of product to be edited
$product->readOne();
6.4 Add readOne() method in the Product Object Class.
The readOne() method used in 6.3 above will not work without the following code inside objects/product.php
function readOne(){
$query = "SELECT
name, price, description, category_id
FROM
" . $this->table_name . "
WHERE
id = ?
LIMIT
0,1";
$stmt = $this->conn->prepare( $query );
$stmt->bindParam(1, $this->id);
$stmt->execute();
$row = $stmt->fetch(PDO::FETCH_ASSOC);
$this->name = $row['name'];
$this->price = $row['price'];
$this->description = $row['description'];
$this->category_id = $row['category_id'];
}
6.5 Put the Values in the Form.
Now we can put the latest values to each form elements.
<form action='update_product.php?id=<?php echo $id; ?>' method='post'>
<table class='table table-hover table-responsive table-bordered'>
<tr>
<td>Name</td>
<td><input type='text' name='name' value='<?php echo $product->name; ?>' class='form-control' required></td>
</tr>
<tr>
<td>Price</td>
<td><input type='text' name='price' value='<?php echo $product->price; ?>' class='form-control' required></td>
</tr>
<tr>
<td>Description</td>
<td><textarea name='description' class='form-control'><?php echo $product->description; ?></textarea></td>
</tr>
<tr>
<td>Category</td>
<td>
<!-- categories select drop-down will be here -->
</td>
</tr>
<tr>
<td></td>
<td>
<button type="submit" class="btn btn-primary">Update</button>
</td>
</tr>
</table>
</form>
6.6 Loop Through the Categories Records to show as Drop-down
Notice that we put an IF statement if($category_id==$id){… inside the while loop. This is to pre-select the option of the current record.
<tr>
<td>Category</td>
<td>
<?php
// read the product categories from the database
include_once 'objects/category.php';
$category = new Category($db);
$stmt = $category->read();
// put them in a select drop-down
echo "<select class='form-control' name='category_id'>";
echo "<option>Please select...</option>";
while ($row_category = $stmt->fetch(PDO::FETCH_ASSOC)){
extract($row_category);
// current category of the product must be selected
if($category_id==$id){
echo "<option value='$id' selected>";
}else{
echo "<option value='$id'>";
}
echo "$name</option>";
}
echo "</select>";
?>
</td>
</tr>
6.7 Code When Form was Submitted
// if the form was submitted
if($_POST){
// set product property values
$product->name = $_POST['name'];
$product->price = $_POST['price'];
$product->description = $_POST['description'];
$product->category_id = $_POST['category_id'];
// update the product
if($product->update()){
echo "<div class=\"alert alert-success alert-dismissable\">";
echo "<button type=\"button\" class=\"close\" data-dismiss=\"alert\" aria-hidden=\"true\">×</button>";
echo "Product was updated.";
echo "</div>";
}
// if unable to update the product, tell the user
else{
echo "<div class=\"alert alert-danger alert-dismissable\">";
echo "<button type=\"button\" class=\"close\" data-dismiss=\"alert\" aria-hidden=\"true\">×</button>";
echo "Unable to update product.";
echo "</div>";
}
}
6.8 Update Code in the Product Class
The update function used in the IF statement above will not work without adding the following code in objects/product.php
function update(){
$query = "UPDATE
" . $this->table_name . "
SET
name = :name,
price = :price,
description = :description,
category_id = :category_id
WHERE
id = :id";
$stmt = $this->conn->prepare($query);
$stmt->bindParam(':name', $this->name);
$stmt->bindParam(':price', $this->price);
$stmt->bindParam(':description', $this->description);
$stmt->bindParam(':category_id', $this->category_id);
$stmt->bindParam(':id', $this->id);
// execute the query
if($stmt->execute()){
return true;
}else{
return false;
}
}
7.0 Deleting Record in PHP the OOP Way
7.1 Put this JavaScript Code in index.php
Put this JavaScript code before footer.php
<script>
$(document).on('click', '.delete-object', function(){
var id = $(this).attr('delete-id');
var q = confirm("Are you sure?");
if (q == true){
$.post('delete_product.php', {
object_id: id
}, function(data){
location.reload();
}).fail(function() {
alert('Unable to delete.');
});
}
return false;
});
</script>
7.2 Create delete_product.php
In this file is where the ID was sent by the JavaScript code in 7.1.
<?php
// check if value was posted
if($_POST){
// include database and object file
include_once 'config/database.php';
include_once 'objects/product.php';
// get database connection
$database = new Database();
$db = $database->getConnection();
// prepare product object
$product = new Product($db);
// set product id to be deleted
$product->id = $_POST['object_id'];
// delete the product
if($product->delete()){
echo "Object was deleted.";
}
// if unable to delete the product
else{
echo "Unable to delete object.";
}
}
?>
7.3 Delete Code in Product Class
This is the delete() method inside the product object class used by delete_product.php
// delete the product
function delete(){
$query = "DELETE FROM " . $this->table_name . " WHERE id = ?";
$stmt = $this->conn->prepare($query);
$stmt->bindParam(1, $this->id);
if($result = $stmt->execute()){
return true;
}else{
return false;
}
}
8.0 Online Resources
8.1 Download Source Code
This is also one way to view the complete code. DOWNLOAD CODE HERE
That’s it for now, again please let us know in the comments section below if you have any problem with our code for today, or if you just want to put some additional explanation and examples.
Please note that this post is in continuous development, meaning I’ll update it every now and then.
If you have a friend, or know someone who need this PHP Object Oriented CRUD Example, please share this page to them! I know you will help them a lot by doing it. Thanks!
