PHP Object-oriented (OOP) CRUD Tutorial – Step By Step Guide!
Previously, we learned the basics from our PHP CRUD Tutorial for Beginners.
Today we welcome you to our PHP OOP CRUD Tutorial, a comprehensive guide on mastering PHP Object-Oriented Programming (OOP) for database management.
This tutorial will cover everything you need to know to create, read, update, delete, and search records in a MySQL database using Object-Oriented Programming (OOP) in PHP.
We will also show you how to upload files, making it an all-in-one resource for anyone looking to improve their OOP skills in PHP.
This tutorial will take you through the process step-by-step, providing you with a solid foundation in PHP OOP and database management. Let’s dive in!
First, we’re going to create our database and tables. We will also put sample data in our database.
The files “products.sql” and “categories.sql” are also included in the code download, located in the “dev” folder.
php_oop_crud_level_1 as the database name.Next, we will create the “products” table on the database we just created. The products table will store the product data.
We will create the products table using a SQL statement. You can copy it below. Here’s how to run an SQL statement using PhpMyAdmin.
php_oop_crud_level_1 database.-- Table structure for table `products` CREATE TABLE IF NOT EXISTS `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=38 ;
We have to put some database records. Run the following SQL statement using your PhpMyAdmin.
-- Dumping data for table `products` INSERT INTO `products` (`id`, `name`, `description`, `price`, `category_id`, `created`, `modified`) VALUES (1, 'LG P880 4X HD', 'My first awesome phone!', 336, 3, '2014-06-01 01:12:26', '2014-05-31 17:12:26'), (2, 'Google Nexus 4', 'The most awesome phone of 2013!', 299, 2, '2014-06-01 01:12:26', '2014-05-31 17:12:26'), (3, 'Samsung Galaxy S4', 'How about no?', 600, 3, '2014-06-01 01:12:26', '2014-05-31 17:12:26'), (6, 'Bench Shirt', 'The best shirt!', 29, 1, '2014-06-01 01:12:26', '2014-05-31 02:12:21'), (7, 'Lenovo Laptop', 'My business partner.', 399, 2, '2014-06-01 01:13:45', '2014-05-31 02:13:39'), (8, 'Samsung Galaxy Tab 10.1', 'Good tablet.', 259, 2, '2014-06-01 01:14:13', '2014-05-31 02:14:08'), (9, 'Spalding Watch', 'My sports watch.', 199, 1, '2014-06-01 01:18:36', '2014-05-31 02:18:31'), (10, 'Sony Smart Watch', 'The coolest smart watch!', 300, 2, '2014-06-06 17:10:01', '2014-06-05 18:09:51'), (11, 'Huawei Y300', 'For testing purposes.', 100, 2, '2014-06-06 17:11:04', '2014-06-05 18:10:54'), (12, 'Abercrombie Lake Arnold Shirt', 'Perfect as gift!', 60, 1, '2014-06-06 17:12:21', '2014-06-05 18:12:11'), (13, 'Abercrombie Allen Brook Shirt', 'Cool red shirt!', 70, 1, '2014-06-06 17:12:59', '2014-06-05 18:12:49'), (25, 'Abercrombie Allen Anew Shirt', 'Awesome new shirt!', 999, 1, '2014-11-22 18:42:13', '2014-11-21 19:42:13'), (26, 'Another product', 'Awesome product!', 555, 2, '2014-11-22 19:07:34', '2014-11-21 20:07:34'), (27, 'Bag', 'Awesome bag for you!', 999, 1, '2014-12-04 21:11:36', '2014-12-03 22:11:36'), (28, 'Wallet', 'You can absolutely use this one!', 799, 1, '2014-12-04 21:12:03', '2014-12-03 22:12:03'), (30, 'Wal-mart Shirt', '', 555, 2, '2014-12-13 00:52:29', '2014-12-12 01:52:29'), (31, 'Amanda Waller Shirt', 'New awesome shirt!', 333, 1, '2014-12-13 00:52:54', '2014-12-12 01:52:54'), (32, 'Washing Machine Model PTRR', 'Some new product.', 999, 1, '2015-01-08 22:44:15', '2015-01-07 23:44:15');
The “categories” table is used to store product categories. Run the following SQL statement using your PhpMyAdmin.
-- Table structure for table `categories` CREATE TABLE IF NOT EXISTS `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 ;
We are going to have “Fashion,” “Electronics,” and “Motors” as categories in our project. I got those three category ideas from eBay, haha! Run the following SQL statement using your PhpMyAdmin.
-- Dumping data for table `categories` INSERT INTO `categories` (`id`, `name`, `created`, `modified`) VALUES (1, 'Fashion', '2014-06-01 00:35:07', '2014-05-30 17:34:33'), (2, 'Electronics', '2014-06-01 00:35:07', '2014-05-30 17:34:33'), (3, 'Motors', '2014-06-01 00:35:07', '2014-05-30 17:34:54');
In this section, we set up our database using PhpMyAdmin. It should look like the image below.
We don’t have a PHP program output yet. Let’s continue to the next section to achieve more output.
The purpose of layout files is to have a reusable code for the header and footer of our application. This will make it easier for us to make our application look good.
This “layout_header.php” file will be included at the beginning of the PHP files that will need it. This way, we won’t have to write the same header codes every time.
We use the Bootstrap framework to make our project look good. If you’re not yet familiar with you, please learn our Bootstrap tutorial here first.
Bootstrap CSS asset will be included inside the head tags.
layout_header.php file.<!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>
<!-- Latest compiled and minified Bootstrap CSS -->
<link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/css/bootstrap.min.css" />
<!-- our custom CSS -->
<link rel="stylesheet" href="assets/css/custom.css" />
</head>
<body>
<!-- container -->
<div class="container">
<?php
// show page title
echo "<div class='page-header'>
<h1>{$page_title}</h1>
</div>";
?>
This layout_footer.php will be included at the end of each PHP file that need it. This way, we won’t have to write the same footer codes every time.
The assets used in this file are:
Let’s go on and create the footer layout file.
layout_footer.php file. </div>
<!-- /container -->
<!-- jQuery (necessary for Bootstrap's JavaScript plugins) -->
<script src="https://code.jquery.com/jquery-3.2.1.min.js"></script>
<!-- Latest compiled and minified Bootstrap JavaScript -->
<script src="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/js/bootstrap.min.js"></script>
<!-- bootbox library -->
<script src="https://cdnjs.cloudflare.com/ajax/libs/bootbox.js/4.4.0/bootbox.min.js"></script>
</body>
</html>
This file is used to change any style we want on our web page. It is also used to override the default style given by Bootstrap.
folder..left-margin{
margin:0 .5em 0 0;
}
.right-button-margin{
margin: 0 0 1em 0;
overflow: hidden;
}
/* some changes in bootstrap modal */
.modal-body {
padding: 20px 20px 0px 20px !important;
text-align: center !important;
}
.modal-footer{
text-align: center !important;
}
The layout files we created in this section is meant to be used inside another PHP file. If we try to run the layout files alone, we won’t get any meaningful output.
If you run layout_header.php, it will look like this on the browser.
The custom.css file will look like this.
The layout_footer.php is blank. Let’s continue on the next section to see a more meaningful output.
Go back to the “PhpOopCrudLevel1” folder, create a file with a name create_product.php and put the following code inside it.
<?php // set page headers $page_title = "Create Product"; include_once "layout_header.php"; // contents will be here // footer include_once "layout_footer.php"; ?>
The following code will render a button. Replace the comments // contents will be here of the previous section with the following.
echo "<div class='right-button-margin'>
<a href='index.php' class='btn btn-default pull-right'>Read Products</a>
</div>";
?>
<!-- 'create product' html form will be here -->
<?php
We can use it for retrieving categories or saving new product record later. Put the following code before // set page headers comment of create_product.php file.
// include database and object files include_once 'config/database.php'; include_once 'objects/product.php'; include_once 'objects/category.php'; // get database connection $database = new Database(); $db = $database->getConnection(); // pass connection to objects $product = new Product($db); $category = new Category($db);
Getting a database connection will not work without this class. This class file will be included in most PHP files of our PHP OOP CRUD Tutorial.
Create a config folder and inside that folder, create a database.php file. Open that file and put the following code.
<?php
class Database{
// specify your own database credentials
private $host = "localhost";
private $db_name = "php_oop_crud_level_1";
private $username = "root";
private $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;
}
}
?>
The following code will render an HTML form. Open create_product.php file.
Replace <!-- 'create product' html form will be here --> comment with the following code.
<!-- PHP post code will be here -->
<!-- HTML form for creating a product -->
<form action="<?php echo htmlspecialchars($_SERVER["PHP_SELF"]);?>" method="post">
<table class='table table-hover table-responsive table-bordered'>
<tr>
<td>Name</td>
<td><input type='text' name='name' class='form-control' /></td>
</tr>
<tr>
<td>Price</td>
<td><input type='text' name='price' class='form-control' /></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>
The following code will retrieve categories and put it in a “select” drop-down.
Replace <!-- categories from database will be here --> comment of the previous section with the following code.
<?php
// read the product categories from the database
$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>";
?>
Of course, the previous section won’t work without the category object class. Create objects folder. Create category.php file. Place the following code.
<?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;
}
}
?>
It will get the category name instead of showing just an ID. Add the following code inside our category.php, you will see this method used in the next few sections.
// used to read category name by its ID
function readName(){
$query = "SELECT name 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'];
}
The user will enter the values in the HTML form and when the create (submit) button was clicked, values will be sent via POST request, the code below will save it in the database.
Open create_product.php file. Replace <!-- PHP post code will be here --> comment with the following code.
<?php
// if the form was submitted - PHP OOP CRUD Tutorial
if($_POST){
// 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'>Product was created.</div>";
}
// if unable to create the product, tell the user
else{
echo "<div class='alert alert-danger'>Unable to create product.</div>";
}
}
?>
The previous section will not work without the product object. Open objects folder. Create product.php file. Open that file and put the following code.
<?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(){
//write query
$query = "INSERT INTO
" . $this->table_name . "
SET
name=:name, price=:price, description=:description, category_id=:category_id, created=:created";
$stmt = $this->conn->prepare($query);
// posted values
$this->name=htmlspecialchars(strip_tags($this->name));
$this->price=htmlspecialchars(strip_tags($this->price));
$this->description=htmlspecialchars(strip_tags($this->description));
$this->category_id=htmlspecialchars(strip_tags($this->category_id));
// to get time-stamp for 'created' field
$this->timestamp = date('Y-m-d H:i:s');
// bind values
$stmt->bindParam(":name", $this->name);
$stmt->bindParam(":price", $this->price);
$stmt->bindParam(":description", $this->description);
$stmt->bindParam(":category_id", $this->category_id);
$stmt->bindParam(":created", $this->timestamp);
if($stmt->execute()){
return true;
}else{
return false;
}
}
}
?>
Form to create product.
Categories drop down in the form.
When you fill out the form and clicked the “Create” button.
Changes in the database.
In this part of our PHP OOP CRUD tutorial, we will list the records from the database.
Create a new file and name it index.php. This file will show the main page of our web app. Put the following code inside it.
<?php // set page header $page_title = "Read Products"; include_once "layout_header.php"; // contents will be here // set page footer include_once "layout_footer.php"; ?>
The following code will render a button. When this button was clicked, it will show us a page where we can create a record. Replace the // contents will be here comments in the previous section with the following code.
echo "<div class='right-button-margin'>
<a href='create_product.php' class='btn btn-default pull-right'>Create Product</a>
</div>";
Pagination is very important if you have thousands of data from the database. Put the following code before the set page header comment of index.php file.
// 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 = 5; // calculate for the query LIMIT clause $from_record_num = ($records_per_page * $page) - $records_per_page; // retrieve records here
Now we will retrieve data from the database. Replace // retrieve records here comment of index.php with the following code.
// include database and object files include_once 'config/database.php'; include_once 'objects/product.php'; include_once 'objects/category.php'; // instantiate database and objects $database = new Database(); $db = $database->getConnection(); $product = new Product($db); $category = new Category($db); // query products $stmt = $product->readAll($from_record_num, $records_per_page); $num = $stmt->rowCount();
Retrieving records in the previous section won’t work without this method. Put the following code inside our “product.php” file which is inside the “objects” folder.
function readAll($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;
}
This time, we will show the list of records to the user. An HTML table will hold our data. Put the following code after the section 6.2 code.
// display the products if there are any
if($num>0){
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>";
// read one, edit and delete button will be here
echo "</td>";
echo "</tr>";
}
echo "</table>";
// paging buttons will be here
}
// tell the user there are no products
else{
echo "<div class='alert alert-info'>No products found.</div>";
}
The following code will render three buttons: Read, Edit and Delete button.
Inside the “while” loop of the previous section, there is a comment “read one, edit and delete button will be here“, replace that with the following code.
// read, edit and delete buttons
echo "<a href='read_one.php?id={$id}' class='btn btn-primary left-margin'>
<span class='glyphicon glyphicon-list'></span> Read
</a>
<a href='update_product.php?id={$id}' class='btn btn-info left-margin'>
<span class='glyphicon glyphicon-edit'></span> Edit
</a>
<a delete-id='{$id}' class='btn btn-danger delete-object'>
<span class='glyphicon glyphicon-remove'></span> Delete
</a>";
The following code will show our pagination buttons. Create a new file and name it “paging.php“. Open that file and put the following code.
<?php
echo "<ul class='pagination'>";
// button for first page
if($page>1){
echo "<li><a href='{$page_url}' title='Go to the first page.'>
First
</a></li>";
}
// calculate total pages
$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_url}page=$x'>$x</a></li>";
}
}
}
// button for last page
if($page<$total_pages){
echo "<li><a href='" .$page_url. "page={$total_pages}' title='Last page is {$total_pages}.'>
Last
</a></li>";
}
echo "</ul>";
?>
The following code will be used to count the total number of records in the database. This will be used for pagination.
Open your product.php file which is inside the “objects” folder. Add the following method in the class.
// 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;
}
The following code will show our pagination buttons under our records list. Put the following code after the closing “table” tag of section 6.6 above.
// the page where this paging is used $page_url = "index.php?"; // count all products in the database to calculate total pages $total_rows = $product->countAll(); // paging buttons here include_once 'paging.php';
Run http://localhost/PhpOopCrudLevel1/index.php on your browser, you should see something like the image below.
List of records, page 1.
List of records, page 2.
I know our PHP OOP CRUD tutorial is kinda long. Please take a break or drink some coffee first!
Create update_product.php file, open that file and put the following code.
<?php // retrieve one product will be here // set page header $page_title = "Update Product"; include_once "layout_header.php"; // contents will be here // set page footer include_once "layout_footer.php"; ?>
The following code will render a button. This button, when clicked, will let us go back to the records list. Replace the previous section’s “contents will be here” comments with the following code.
echo "<div class='right-button-margin'>
<a href='index.php' class='btn btn-default pull-right'>Read Products</a>
</div>";
?>
<!-- 'update product' form will be here -->
The following code will retrieve data that will populate our HTML form. This is important because this will let the user know what exactly the record he is updating.
Open update_product.php file. Replace “// retrieve one product will be here” comment with the following code.
// 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';
include_once 'objects/category.php';
// get database connection
$database = new Database();
$db = $database->getConnection();
// prepare objects
$product = new Product($db);
$category = new Category($db);
// set ID property of product to be edited
$product->id = $id;
// read the details of product to be edited
$product->readOne();
The readOne() method used in the previous section will not work without the following code inside /objects/product.php file.
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'];
}
Now we can put the latest values to each form elements. Replace “<!-- 'update product' form will be here -->” comment of update_product.php with the following code.
<!-- post code will be here -->
<form action="<?php echo htmlspecialchars($_SERVER["PHP_SELF"] . "?id={$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' /></td>
</tr>
<tr>
<td>Price</td>
<td><input type='text' name='price' value='<?php echo $product->price; ?>' class='form-control' /></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>
The following code will list the categories in a drop-down.
Notice that we put “if($product->category_id==$category_id){…” inside the while loop. This is to pre-select the option of the current record.
Replace the previous section’s comments “categories select drop-down will be here” with the following code.
<?php
$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)){
$category_id=$row_category['id'];
$category_name = $row_category['name'];
// current category of the product must be selected
if($product->category_id==$category_id){
echo "<option value='$category_id' selected>";
}else{
echo "<option value='$category_id'>";
}
echo "$category_name</option>";
}
echo "</select>";
?>
The following code will assign the “posted” values to the object properties. Once assigned, it will update the database with those values using the update() method.
Open update_product.php file. Replace “<!-- post code will be here -->“ comment with the following code.
<?php
// 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'>
Product was updated.
</div>";
}
// if unable to update the product, tell the user
else{
echo "<div class='alert alert-danger alert-dismissable'>
Unable to update product.
</div>";
}
}
?>
The following code will make the previous section’s “$product->update()” method work. Open our “product.php” which is inside the “objects” folder and add the following code.
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);
// posted values
$this->name=htmlspecialchars(strip_tags($this->name));
$this->price=htmlspecialchars(strip_tags($this->price));
$this->description=htmlspecialchars(strip_tags($this->description));
$this->category_id=htmlspecialchars(strip_tags($this->category_id));
$this->id=htmlspecialchars(strip_tags($this->id));
// bind parameters
$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;
}
return false;
}
Click any “Edit” button in the index page. The update record form should look like the following.
When you submit the form, a message will be shown.
A record was changed in the database.
We previously made the code for “update record”, this section for reading one record from a database will be easier to do.
This is the page where the data of a single record will be displayed. Create a new file and name it “read_one.php“, open that file and put the following code.
<?php
// set page headers
$page_title = "Read One Product";
include_once "layout_header.php";
// read products button
echo "<div class='right-button-margin'>
<a href='index.php' class='btn btn-primary pull-right'>
<span class='glyphicon glyphicon-list'></span> Read Products
</a>
</div>";
// set footer
include_once "layout_footer.php";
?>
The following code will read a single record from the database. Put the following code before the “set page headers” comments of the previous section.
// get ID of the product to be read
$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';
include_once 'objects/category.php';
// get database connection
$database = new Database();
$db = $database->getConnection();
// prepare objects
$product = new Product($db);
$category = new Category($db);
// set ID property of product to be read
$product->id = $id;
// read the details of product to be read
$product->readOne();
This time, we will display the record details on an HTML table. Put the following code under the closing “div” tag of “Read Products” button.
// HTML table for displaying a product details
echo "<table class='table table-hover table-responsive table-bordered'>
<tr>
<td>Name</td>
<td>{$product->name}</td>
</tr>
<tr>
<td>Price</td>
<td>${$product->price}</td>
</tr>
<tr>
<td>Description</td>
<td>{$product->description}</td>
</tr>
<tr>
<td>Category</td>
<td>";
// display category name
$category->id=$product->category_id;
$category->readName();
echo $category->name;
echo "</td>
</tr>
</table>";
Click any “Read” button in the index page, you should see something like the image below.
This is the last coding part of our PHP OOP CRUD Tutorial. Enjoy every code!
Put the following JavaScript code before the closing “body” tag in layout_footer.php file. We used Bootbox.js to make a Bootstrap-style confirm dialog box.
<script>
// JavaScript for deleting product
$(document).on('click', '.delete-object', function(){
var id = $(this).attr('delete-id');
bootbox.confirm({
message: "<h4>Are you sure?</h4>",
buttons: {
confirm: {
label: '<span class="glyphicon glyphicon-ok"></span> Yes',
className: 'btn-danger'
},
cancel: {
label: '<span class="glyphicon glyphicon-remove"></span> No',
className: 'btn-primary'
}
},
callback: function (result) {
if(result==true){
$.post('delete_product.php', {
object_id: id
}, function(data){
location.reload();
}).fail(function() {
alert('Unable to delete.');
});
}
}
});
return false;
});
</script>
Create a new file and name it “delete_product.php“. This file accepts the ID posted by the JavaScript code in the previous section. A record will be deleted from the database based on posted ID.
Open delete_product.php and put the following code.
<?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.";
}
}
?>
The previous section will not work with the “delete()” method in the product object. Open “product.php” which is inside the “objects” folder and put the following code.
// 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;
}
}
Click any “Delete” button in the index page. A pop up confirmation will be shown.
If the user clicks “OK” the record will be deleted and gone in the table.
A record was deleted in the database.
We’ll continue by adding the search feature. This will answer the question: How to search data from database in php? This is a very useful feature because you enable your users to easily search a certain data from our MySQL database.
Please note that this is a bonus section. The code in this section is not included in our LEVEL 1 source code download.
We have to change index.php because we are adding a “search” feature and we want our code to be short. Our index.php will now look like the following code.
<?php // core.php holds pagination variables include_once 'config/core.php'; // 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); $category = new Category($db); $page_title = "Read Products"; include_once "layout_header.php"; // query products $stmt = $product->readAll($from_record_num, $records_per_page); // specify the page where paging is used $page_url = "index.php?"; // count total rows - used for pagination $total_rows=$product->countAll(); // read_template.php controls how the product list will be rendered include_once "read_template.php"; // layout_footer.php holds our javascript and closing html tags include_once "layout_footer.php"; ?>
Why do we need this template? We need it because exactly the same code can be used by index.php and search.php for displaying a list of records. Using a template means lesser code.
This template holds our search form as well.
<?php
// search form
echo "<form role='search' action='search.php'>
<div class='input-group col-md-3 pull-left margin-right-1em'>";
$search_value=isset($search_term) ? "value='{$search_term}'" : "";
echo "<input type='text' class='form-control' placeholder='Type product name or description...' name='s' id='srch-term' required {$search_value} />
<div class='input-group-btn'>
<button class='btn btn-primary' type='submit'><i class='glyphicon glyphicon-search'></i></button>
</div>
</div>
</form>";
// create product button
echo "<div class='right-button-margin'>
<a href='create_product.php' class='btn btn-primary pull-right'>
<span class='glyphicon glyphicon-plus'></span> Create Product
</a>
</div>";
// display the products if there are any
if($total_rows>0){
echo "<table class='table table-hover table-responsive table-bordered'>
<tr>
<th>Product</th>
<th>Price</th>
<th>Description</th>
<th>Category</th>
<th>Actions</th>
</tr>";
while ($row = $stmt->fetch(PDO::FETCH_ASSOC)){
extract($row);
echo "<tr>
<td>{$name}</td>
<td>{$price}</td>
<td>{$description}</td>
<td>";
$category->id = $category_id;
$category->readName();
echo $category->name;
echo "</td>";
echo "<td>";
// read product button
echo "<a href='read_one.php?id={$id}' class='btn btn-primary left-margin'>
<span class='glyphicon glyphicon-list'></span> Read
</a>";
// edit product button
echo "<a href='update_product.php?id={$id}' class='btn btn-info left-margin'>
<span class='glyphicon glyphicon-edit'></span> Edit
</a>";
// delete product button
echo "<a delete-id='{$id}' class='btn btn-danger delete-object'>
<span class='glyphicon glyphicon-remove'></span> Delete
</a>";
echo "</td>";
echo "</tr>";
}
echo "</table>";
// paging buttons
include_once 'paging.php';
}
// tell the user there are no products
else{
echo "<div class='alert alert-danger'>No products found.</div>";
}
?>
Create a new folder and name it “config“. Inside that folder, create a new file and name it “core.php“.
This file will hold our pagination variables. Using a core.php file is a good practice, it can be used to hold other configuration values that you might need in the future.
Open core.php and put the following code.
<?php // 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 = 5; // calculate for the query LIMIT clause $from_record_num = ($records_per_page * $page) - $records_per_page; ?>
The new paging.php code will look like the following.
<?php
echo "<ul class=\"pagination\">";
// button for first page
if($page>1){
echo "<li><a href='{$page_url}' title='Go to the first page.'>
First Page
</a></li>";
}
// count all products in the database to calculate total pages
$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_url}page=$x'>$x</a></li>";
}
}
}
// button for last page
if($page<$total_pages){
echo "<li><a href='" .$page_url . "page={$total_pages}' title='Last page is {$total_pages}.'>
Last Page
</a></li>";
}
echo "</ul>";
?>
The core.php file will be included at the beginning of index.php file. The read_template.php will be included before the layout_footer.php inclusion. The new index.php will look like the following code
<?php // core.php holds pagination variables include_once 'config/core.php'; // 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); $category = new Category($db); $page_title = "Read Products"; include_once "layout_header.php"; // query products $stmt = $product->readAll($from_record_num, $records_per_page); // specify the page where paging is used $page_url = "index.php?"; // count total rows - used for pagination $total_rows=$product->countAll(); // read_template.php controls how the product list will be rendered include_once "read_template.php"; // layout_footer.php holds our javascript and closing html tags include_once "layout_footer.php"; ?>
This is the most important file of this section. This file will display the records based on a user’s search term.
Create a new file and name it “search.php“. Open that file and put the following code.
<?php
// core.php holds pagination variables
include_once 'config/core.php';
// 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);
$category = new Category($db);
// get search term
$search_term=isset($_GET['s']) ? $_GET['s'] : '';
$page_title = "You searched for \"{$search_term}\"";
include_once "layout_header.php";
// query products
$stmt = $product->search($search_term, $from_record_num, $records_per_page);
// specify the page where paging is used
$page_url="search.php?s={$search_term}&";
// count total rows - used for pagination
$total_rows=$product->countAll_BySearch($search_term);
// read_template.php controls how the product list will be rendered
include_once "read_template.php";
// layout_footer.php holds our javascript and closing html tags
include_once "layout_footer.php";
?>
Open “product.php” file which is inside the “objects” folder. Add the following methods in the class.
// read products by search term
public function search($search_term, $from_record_num, $records_per_page){
// select query
$query = "SELECT
c.name as category_name, p.id, p.name, p.description, p.price, p.category_id, p.created
FROM
" . $this->table_name . " p
LEFT JOIN
categories c
ON p.category_id = c.id
WHERE
p.name LIKE ? OR p.description LIKE ?
ORDER BY
p.name ASC
LIMIT
?, ?";
// prepare query statement
$stmt = $this->conn->prepare( $query );
// bind variable values
$search_term = "%{$search_term}%";
$stmt->bindParam(1, $search_term);
$stmt->bindParam(2, $search_term);
$stmt->bindParam(3, $from_record_num, PDO::PARAM_INT);
$stmt->bindParam(4, $records_per_page, PDO::PARAM_INT);
// execute query
$stmt->execute();
// return values from database
return $stmt;
}
public function countAll_BySearch($search_term){
// select query
$query = "SELECT
COUNT(*) as total_rows
FROM
" . $this->table_name . " p
WHERE
p.name LIKE ? OR p.description LIKE ?";
// prepare query statement
$stmt = $this->conn->prepare( $query );
// bind variable values
$search_term = "%{$search_term}%";
$stmt->bindParam(1, $search_term);
$stmt->bindParam(2, $search_term);
$stmt->execute();
$row = $stmt->fetch(PDO::FETCH_ASSOC);
return $row['total_rows'];
}
In this section, we will add a “file upload” feature. This feature is included in the PRO source code download.
Open create_product.php and find the “form” tag. Change that line to the following code. The “enctype” enables the form to submit a file to the server.
<form action="<?php echo htmlspecialchars($_SERVER["PHP_SELF"]);?>" method="post" enctype="multipart/form-data">
On the same HTML table, find the closing “tr” tag of the “Category” field. Add the following code. This adds an input field where the user can browse the file he wants to upload.
<tr>
<td>Photo</td>
<td><input type="file" name="image" /></td>
</tr>
Open create_product.php and add the new “image” field. The value will be the file name of the submitted file. We used the built-in sha1_file() function the make the file name unique.
Open create_product.php file. Place the following code under $product->category_id = $_POST[‘category_id’]; code.
$image=!empty($_FILES["image"]["name"])
? sha1_file($_FILES['image']['tmp_name']) . "-" . basename($_FILES["image"]["name"]) : "";
$product->image = $image;
Open “objects” folder and open the “product.php” file inside it. Find the “create()” method.
Add the “image” field by changing the query to:
// insert query
$query = "INSERT INTO " . $this->table_name . "
SET name=:name, price=:price, description=:description,
category_id=:category_id, image=:image, created=:created";
On the sanitize section, it will be:
$this->image=htmlspecialchars(strip_tags($this->image));
Then bind the value.
$stmt->bindParam(":image", $this->image);
Add the “image” property at the top of the class, maybe after public $category_id;
public $image;
Using the PhpMyAdmin, add an “image” field in the products table. Set the type to VARCHAR with 512 in length.
Open create_product.php and find this line.
// product was created in database echo "<div class='alert alert-success'>Product was created.</div>";
Put the following code under the code above. This will call the uploadPhoto() method to try uploading the file to the server.
// try to upload the submitted file // uploadPhoto() method will return an error message, if any. echo $product->uploadPhoto();
The previous section will not work without the complete code of uploadPhoto() method.
Open “objects” folder and open the “product.php” file inside it. Add the following method inside the class.
// will upload image file to server
function uploadPhoto(){
$result_message="";
// now, if image is not empty, try to upload the image
if($this->image){
// sha1_file() function is used to make a unique file name
$target_directory = "uploads/";
$target_file = $target_directory . $this->image;
$file_type = pathinfo($target_file, PATHINFO_EXTENSION);
// error message is empty
$file_upload_error_messages="";
}
return $result_message;
}
Now we will validate the submitted file by:
Add the following code after $file_upload_error_messages=””; of the previous section.
// make sure that file is a real image
$check = getimagesize($_FILES["image"]["tmp_name"]);
if($check!==false){
// submitted file is an image
}else{
$file_upload_error_messages.="<div>Submitted file is not an image.</div>";
}
// make sure certain file types are allowed
$allowed_file_types=array("jpg", "jpeg", "png", "gif");
if(!in_array($file_type, $allowed_file_types)){
$file_upload_error_messages.="<div>Only JPG, JPEG, PNG, GIF files are allowed.</div>";
}
// make sure file does not exist
if(file_exists($target_file)){
$file_upload_error_messages.="<div>Image already exists. Try to change file name.</div>";
}
// make sure submitted file is not too large, can't be larger than 1 MB
if($_FILES['image']['size'] > (1024000)){
$file_upload_error_messages.="<div>Image must be less than 1 MB in size.</div>";
}
// make sure the 'uploads' folder exists
// if not, create it
if(!is_dir($target_directory)){
mkdir($target_directory, 0777, true);
}
If the file is valid, we will upload the file to the server, specifically in the “uploads” folder. If there’s any error, we will return it to be shown to the user.
Place the following code after the previous section’s code.
// if $file_upload_error_messages is still empty
if(empty($file_upload_error_messages)){
// it means there are no errors, so try to upload the file
if(move_uploaded_file($_FILES["image"]["tmp_name"], $target_file)){
// it means photo was uploaded
}else{
$result_message.="<div class='alert alert-danger'>";
$result_message.="<div>Unable to upload photo.</div>";
$result_message.="<div>Update the record to upload photo.</div>";
$result_message.="</div>";
}
}
// if $file_upload_error_messages is NOT empty
else{
// it means there are some errors, so show them to user
$result_message.="<div class='alert alert-danger'>";
$result_message.="{$file_upload_error_messages}";
$result_message.="<div>Update the record to upload photo.</div>";
$result_message.="</div>";
}
Open “objects” folder and open “product.php” file. Find readOne() method. Add the “image” field in the method. The new method should look like the following.
function readOne(){
$query = "SELECT name, price, description, category_id, image
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'];
$this->image = $row['image'];
}
Open read_one.php file and find the closing “tr” tag of the “Category” field in the HTML table. Add the following code. This will show the uploaded image.
echo "<tr>";
echo "<td>Image</td>";
echo "<td>";
echo $product->image ? "<img src='uploads/{$product->image}' style='width:300px;' />" : "No image found.";
echo "</td>";
echo "</tr>";
Click the “Create” button. You will see something like the image below.
When you submit the form, it will show a message prompt.
If you tried to upload an invalid image file, for example a PDF file. It will show an error message.
If you click the “Read” button of a record with an image, it will look like the following.
If the record has no image, it will say the “No image found.” message.
You have a choice to download the BASIC or PRO source codes.
You can get the BASIC source code free if you follow our tutorial above. But if you think you can learn faster by looking at the complete code, you may choose to download the source codes.
Your download should depend on what you want to learn or your needed features. See the list of features below.
| FEATURES | BASIC | PRO |
|---|---|---|
| Object Oriented Programming Source Code | ✔ | ✔ |
| PDO extension used | ✔ | ✔ |
| Create product | ✔ | ✔ |
| Read product | ✔ | ✔ |
| Update product | ✔ | ✔ |
| Delete product | ✔ | ✔ |
| Price display with a dollar sign | ✔ | ✔ |
| Pagination | ✔ | ✔ |
| Bootstrap UI | ✔ | ✔ |
| SQL file in the “dev” folder | ✔ | ✔ |
| HTML5 (font-end) validation for creating and updating the product | – | ✔ |
| Category selection for creating and updating products. | – | ✔ |
| Buttons with Glyphicons | – | ✔ |
| Search products by name or description | – | ✔ |
| HTML5 (font-end) validation for search product | – | ✔ |
| Pagination in search | – | ✔ |
| Allow the user to input the page number (read and search list) | – | ✔ |
| Export/download records to CSV | – | ✔ |
| Price display with a dollar sign, comma, and decimal point | – | ✔ |
| Multiple delete | – | ✔ |
| File upload field when creating or updating record | – | ✔ |
| Bootstrap navigation bar | – | ✔ |
| Select a category in the navigation | – | ✔ |
| Highlight the category in the navigation | – | ✔ |
| Create, read, update, delete, and search a category | – | ✔ |
| View products by category with pagination | – | ✔ |
| Pagination for category search | – | ✔ |
| Server-side validation for creating and updating product & category | – | ✔ |
| Sorting by fields with pagination | – | ✔ |
| Search product by date range – record date created | – | ✔ |
| Pagination for each product by date range | – | ✔ |
| jQuery UI calendar for picking a date | – | ✔ |
| Use the buttons below to download. ↓ | BASIC | PRO |
Here are more reasons to download our source codes.
Ready to put your new source code to the test? Awesome! Here’s how to get started:
If you have any questions or run into any issues, our support team is here to help. Comment below, and we’ll be happy to assist you.
Thank you for supporting codeofaninja.com and our projects. We’re excited to see what you can do with your new source code!
I hope you learned a lot from our PHP OOP CRUD Tutorial! Learning PHP Object Oriented Programming is fun and can dramatically improve your career.
For our next tutorial, let’s learn how to let a user log in, log out, and register in our system. Learn our PHP Login Script with Session Tutorial – Step-by-Step Guide!
[adinserter block=”3″]