Previously, we learned how to make our application look good using the Bootstrap CSS framework. This time, we will learn CRUD operations with PHP and MySQL. CRUD stands for Create, Read, Update and Delete database records.
[adinserter block=”33″]
Overview
This tutorial is for your if:
- You need a high quality and updated reference for a PHP CRUD tutorial.
- You need to learn how to do CRUD operations in PHP and MySQL.
- You are beginner in this kind of PHP web programming.
Coding CRUD with PHP and MySQL is one of the basics. PHP web programmers must be able to code it with less effort. We can perform this task using any of the three PHP Database extensions:
- Using the MySQL extension.
- Using the MySQLi extension.
- Using the PDO extension.
PHP 5.5 deprecated the MySQL extension. It is not recommended to use these days.
If you are programming with PHP, you’ll have to use either MySQLi (i means improved) or PDO extension.
With that in mind, we will use the PDO extension. It is the newest and actively developed way of programming these CRUD grids.
Project file structure
Our PHP CRUD tutorial will contain the following main files.
- dev/products.sql – contains the database table structure and sample data used in this project. Once you created your database in PhpMyAdmin, you can import this file.
- config/database.php – used for database connection and configuration.
- create.php – used for creating a new record. It contains an HTML form where the user can enter details for a new record.
- index.php – used for reading records from the database. It uses an HTML table to display the data retrieved from the MySQL database.
- read_one.php – used for reading one or single record from database. It uses an HTML table to display the data retrieved from the MySQL database.
- update.php – used for updating a record. It uses an HTML form which will be filled out with data based on the given “id” parameter.
- delete.php – used for deleting a record. It accepts an “id” parameter and deletes the record with it. Once it execute the delete query, it will redirect the user to the index.php page.
Prepare the database
Create the database
On your PhpMyAdmin, create a database named “php_beginner_crud_level_1”.
If you’re not sure how to do it, please take a look at the following example. Follow only the “create database” part.
Create the database table
Next, run the following SQL code. This is to create our products database table. If you’re not sure how to do this, take a look at this resource.
-- -- Table structure for table `products` -- CREATE TABLE IF NOT EXISTS `products` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(128) NOT NULL, `description` text NOT NULL, `price` double NOT NULL, `created` datetime NOT NULL, `modified` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=9 ;
Dump sample data on the table
Again, run the following SQL code on your PhpMyAdmin. This will insert the sample data or record it on our products database table.
-- -- Dumping data for table `products` -- INSERT INTO `products` (`id`, `name`, `description`, `price`, `created`, `modified`) VALUES (1, 'Basketball', 'A ball used in the NBA.', 49.99, '2015-08-02 12:04:03', '2015-08-06 06:59:18'), (3, 'Gatorade', 'This is a very good drink for athletes.', 1.99, '2015-08-02 12:14:29', '2015-08-06 06:59:18'), (4, 'Eye Glasses', 'It will make you read better.', 6, '2015-08-02 12:15:04', '2015-08-06 06:59:18'), (5, 'Trash Can', 'It will help you maintain cleanliness.', 3.95, '2015-08-02 12:16:08', '2015-08-06 06:59:18'), (6, 'Mouse', 'Very useful if you love your computer.', 11.35, '2015-08-02 12:17:58', '2015-08-06 06:59:18'), (7, 'Earphone', 'You need this one if you love music.', 7, '2015-08-02 12:18:21', '2015-08-06 06:59:18'), (8, 'Pillow', 'Sleeping well is important.', 8.99, '2015-08-02 12:18:56', '2015-08-06 06:59:18');
As you may have noticed, steps 1 and 2 are both SQL queries. Yes, they can run at the same time. But I wanted it to be on separate steps to emphasize those SQL queries’ purpose.
Create database connection file
This section will answer the question: how to connect to MySQL database with PDO?
- Create php-beginner-crud-level-1 folder and open it.
- Create config folder and open it.
- Create
database.phpfile. - Place the following code inside it.
<?php
// used to connect to the database
$host = "localhost";
$db_name = "php_beginner_crud_level_1";
$username = "root";
$password = "";
try {
$con = new PDO("mysql:host={$host};dbname={$db_name}", $username, $password);
}
// show error
catch(PDOException $exception){
echo "Connection error: " . $exception->getMessage();
}
?>
Output
We have set up the database successfully! The only output we have so far is the database, database table, and sample records we set up via PhpMyAdmin.
Let’s proceed to the next section below.
Create or insert record in PHP
HTML5 boilerplate for create.php
We use the Bootstrap user interface for this project. If you are not familiar with Bootstrap, please learn our Bootstrap Tutorial for Beginners.
- Go back to
php-beginner-crud-level-1folder. - Create a new
create.phpfile. - Place the code following code inside the
create.phpfile.
<!DOCTYPE HTML>
<html>
<head>
<title>PDO - Create a Record - PHP CRUD Tutorial</title>
<!-- Latest compiled and minified Bootstrap CSS -->
<link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/css/bootstrap.min.css" />
</head>
<body>
<!-- container -->
<div class="container">
<div class="page-header">
<h1>Create Product</h1>
</div>
<!-- html form to create product will be here -->
</div> <!-- end .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>
</body>
</html>
HTML form to input new record data
Now we are going to start answering the question: how to create a record with PDO?
The code below will create an HTML form with input fields that matches the fields in the database. Replace <!-- html form to create product will be here --> comment of the previous section with the following code.
<!-- PHP insert code will be here --> <!-- html form here where the product information will be entered --> <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>Description</td> <td><textarea name='description' class='form-control'></textarea></td> </tr> <tr> <td>Price</td> <td><input type='text' name='price' class='form-control' /></td> </tr> <tr> <td></td> <td> <input type='submit' value='Save' class='btn btn-primary' /> <a href='index.php' class='btn btn-danger'>Back to read products</a> </td> </tr> </table> </form>
Code to create a new record
We are still working on the create.php file. Once the user filled out the form and clicked the save button, the code below will save it to the MySQL database.
Replace <!-- PHP insert code will be here --> comment of the previous section with the following code.
<?php
if($_POST){
// include database connection
include 'config/database.php';
try{
// insert query
$query = "INSERT INTO products SET name=:name, description=:description, price=:price, created=:created";
// prepare query for execution
$stmt = $con->prepare($query);
// posted values
$name=htmlspecialchars(strip_tags($_POST['name']));
$description=htmlspecialchars(strip_tags($_POST['description']));
$price=htmlspecialchars(strip_tags($_POST['price']));
// bind the parameters
$stmt->bindParam(':name', $name);
$stmt->bindParam(':description', $description);
$stmt->bindParam(':price', $price);
// specify when this record was inserted to the database
$created=date('Y-m-d H:i:s');
$stmt->bindParam(':created', $created);
// Execute the query
if($stmt->execute()){
echo "<div class='alert alert-success'>Record was saved.</div>";
}else{
echo "<div class='alert alert-danger'>Unable to save record.</div>";
}
}
// show error
catch(PDOException $exception){
die('ERROR: ' . $exception->getMessage());
}
}
?>
Output
Congrats! For the first time, we can now see an output on a web page. Go to this URL:
http://localhost/php-beginner-crud-level-1/create.php
You will see the output that looks like the following images.
When the user fills out the form.
When the user submitted the form.
A new record added to the database.
Read records in PHP
Basic HTML code for index.php
Create a new index.php file. We prepare this to read records from the database. It answers the question: how to read records with PDO?
Place the following code inside the index.php file.
<!DOCTYPE HTML>
<html>
<head>
<title>PDO - Read Records - PHP CRUD Tutorial</title>
<!-- Latest compiled and minified Bootstrap CSS -->
<link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/css/bootstrap.min.css" />
<!-- custom css -->
<style>
.m-r-1em{ margin-right:1em; }
.m-b-1em{ margin-bottom:1em; }
.m-l-1em{ margin-left:1em; }
.mt0{ margin-top:0; }
</style>
</head>
<body>
<!-- container -->
<div class="container">
<div class="page-header">
<h1>Read Products</h1>
</div>
<!-- PHP code to read records will be here -->
</div> <!-- end .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>
<!-- confirm delete record will be here -->
</body>
</html>
Read records from the database
This time we will read records from the database.
Replace <!-- PHP code to read records will be here --> comment of the previous section with the following code.
<?php
// include database connection
include 'config/database.php';
// delete message prompt will be here
// select all data
$query = "SELECT id, name, description, price FROM products ORDER BY id DESC";
$stmt = $con->prepare($query);
$stmt->execute();
// this is how to get number of rows returned
$num = $stmt->rowCount();
// link to create record form
echo "<a href='create.php' class='btn btn-primary m-b-1em'>Create New Product</a>";
//check if more than 0 record found
if($num>0){
// data from database will be here
}
// if no records found
else{
echo "<div class='alert alert-danger'>No records found.</div>";
}
?>
Add HTML table with heading
This is the HTML table that will hold and display data from the database.
Replace // data from database will be here comment of the previous section with the following code.
//start table echo "<table class='table table-hover table-responsive table-bordered'>"; //creating our table heading echo "<tr> <th>ID</th> <th>Name</th> <th>Description</th> <th>Price</th> <th>Action</th> </tr>"; // table body will be here // end table echo "</table>";
Add HTML table body
This part is where we will loop through the list of records from the database. This loop will create the rows of data on our HTML table.
Replace // table body will be here comment of the previous section with the following code.
// retrieve our table contents
// fetch() is faster than fetchAll()
// http://stackoverflow.com/questions/2770630/pdofetchall-vs-pdofetch-in-a-loop
while ($row = $stmt->fetch(PDO::FETCH_ASSOC)){
// extract row
// this will make $row['firstname'] to
// just $firstname only
extract($row);
// creating new table row per record
echo "<tr>
<td>{$id}</td>
<td>{$name}</td>
<td>{$description}</td>
<td>${$price}</td>
<td>";
// read one record
echo "<a href='read_one.php?id={$id}' class='btn btn-info m-r-1em'>Read</a>";
// we will use this links on next part of this post
echo "<a href='update.php?id={$id}' class='btn btn-primary m-r-1em'>Edit</a>";
// we will use this links on next part of this post
echo "<a href='#' onclick='delete_user({$id});' class='btn btn-danger'>Delete</a>";
echo "</td>";
echo "</tr>";
}
Output
Go to this URL:
http://localhost/php-beginner-crud-level-1/index.php
You will see the records retrieved from the database. It will look like the image below.
Read one record in PHP
Basic HTML code for read_one.php
Create a new read_one.php file. This is where we will read and display the details of a single database record.
Place the following code inside the read_one.php file.
<!DOCTYPE HTML>
<html>
<head>
<title>PDO - Read One Record - PHP CRUD Tutorial</title>
<!-- Latest compiled and minified Bootstrap CSS -->
<link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/css/bootstrap.min.css" />
</head>
<body>
<!-- container -->
<div class="container">
<div class="page-header">
<h1>Read Product</h1>
</div>
<!-- PHP read one record will be here -->
<!-- HTML read one record table will be here -->
</div> <!-- end .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>
</body>
</html>
Read one record from the database
The following code is how we retrieve a single database record.
Replace <!-- PHP read one record will be here --> comment of the previous section with the following code.
<?php
// get passed parameter value, in this case, the record ID
// isset() is a PHP function used to verify if a value is there or not
$id=isset($_GET['id']) ? $_GET['id'] : die('ERROR: Record ID not found.');
//include database connection
include 'config/database.php';
// read current record's data
try {
// prepare select query
$query = "SELECT id, name, description, price FROM products WHERE id = ? LIMIT 0,1";
$stmt = $con->prepare( $query );
// this is the first question mark
$stmt->bindParam(1, $id);
// execute our query
$stmt->execute();
// store retrieved row to a variable
$row = $stmt->fetch(PDO::FETCH_ASSOC);
// values to fill up our form
$name = $row['name'];
$description = $row['description'];
$price = $row['price'];
}
// show error
catch(PDOException $exception){
die('ERROR: ' . $exception->getMessage());
}
?>
Display record details
The following HTML table will hold and display the details of a single database record.
Open read_one.php file.
Replace <!-- HTML read one record table will be here --> comment with the following code.
<!--we have our html table here where the record will be displayed--> <table class='table table-hover table-responsive table-bordered'> <tr> <td>Name</td> <td><?php echo htmlspecialchars($name, ENT_QUOTES); ?></td> </tr> <tr> <td>Description</td> <td><?php echo htmlspecialchars($description, ENT_QUOTES); ?></td> </tr> <tr> <td>Price</td> <td><?php echo htmlspecialchars($price, ENT_QUOTES); ?></td> </tr> <tr> <td></td> <td> <a href='index.php' class='btn btn-danger'>Back to read products</a> </td> </tr> </table>
Output
To read one record from the database, try to click any Read button from our index.php file.
You can also go to this URL:
http://localhost/php-beginner-crud-level-1/read_one.php?id=9
You will see an output like the image below.
Update record in PHP
Basic HTML code for udpate.php
Create a new update.php file. We are preparing to update a selected record from the database.
This will answer the question: how to update a record with PDO?
Place the following code inside the new update.php file.
<!DOCTYPE HTML>
<html>
<head>
<title>PDO - Update a Record - PHP CRUD Tutorial</title>
<!-- Latest compiled and minified Bootstrap CSS -->
<link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/css/bootstrap.min.css" />
</head>
<body>
<!-- container -->
<div class="container">
<div class="page-header">
<h1>Update Product</h1>
</div>
<!-- PHP read record by ID will be here -->
<!-- HTML form to update record will be here -->
</div> <!-- end .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>
</body>
</html>
Read a record by ID parameter
We have to get the record ID and store it in the $id variable. We access the $_GET[‘id’] variable to do it.
What we are trying to do here is to get the related data based on the given record ID. This is the way to auto-fill the HTML form with existing data from the database.
Replace <!-- PHP read record by ID will be here --> comment of the previous section with the following code.
<?php
// get passed parameter value, in this case, the record ID
// isset() is a PHP function used to verify if a value is there or not
$id=isset($_GET['id']) ? $_GET['id'] : die('ERROR: Record ID not found.');
//include database connection
include 'config/database.php';
// read current record's data
try {
// prepare select query
$query = "SELECT id, name, description, price FROM products WHERE id = ? LIMIT 0,1";
$stmt = $con->prepare( $query );
// this is the first question mark
$stmt->bindParam(1, $id);
// execute our query
$stmt->execute();
// store retrieved row to a variable
$row = $stmt->fetch(PDO::FETCH_ASSOC);
// values to fill up our form
$name = $row['name'];
$description = $row['description'];
$price = $row['price'];
}
// show error
catch(PDOException $exception){
die('ERROR: ' . $exception->getMessage());
}
?>
HTML form to update a record
This form will show the data retrieved using the previous section’s code.
We read a single record from the database, based on the given ID parameter.
Open update.php file. Replace <!-- HTML form to update record will be here --> comment with the following code.
<!-- PHP post to update record will be here -->
<!--we have our html form here where new record information can be updated-->
<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 htmlspecialchars($name, ENT_QUOTES); ?>" class='form-control' /></td>
</tr>
<tr>
<td>Description</td>
<td><textarea name='description' class='form-control'><?php echo htmlspecialchars($description, ENT_QUOTES); ?></textarea></td>
</tr>
<tr>
<td>Price</td>
<td><input type='text' name='price' value="<?php echo htmlspecialchars($price, ENT_QUOTES); ?>" class='form-control' /></td>
</tr>
<tr>
<td></td>
<td>
<input type='submit' value='Save Changes' class='btn btn-primary' />
<a href='index.php' class='btn btn-danger'>Back to read products</a>
</td>
</tr>
</table>
</form>
Code to update the record
The following code will save the changes in the database.
That is if the user changes some value on the form and hits the Save Changes button.
Replace <!-- PHP post to update record will be here --> comment of the previous section with the following code.
<?php
// check if form was submitted
if($_POST){
try{
// write update query
// in this case, it seemed like we have so many fields to pass and
// it is better to label them and not use question marks
$query = "UPDATE products
SET name=:name, description=:description, price=:price
WHERE id = :id";
// prepare query for excecution
$stmt = $con->prepare($query);
// posted values
$name=htmlspecialchars(strip_tags($_POST['name']));
$description=htmlspecialchars(strip_tags($_POST['description']));
$price=htmlspecialchars(strip_tags($_POST['price']));
// bind the parameters
$stmt->bindParam(':name', $name);
$stmt->bindParam(':description', $description);
$stmt->bindParam(':price', $price);
$stmt->bindParam(':id', $id);
// Execute the query
if($stmt->execute()){
echo "<div class='alert alert-success'>Record was updated.</div>";
}else{
echo "<div class='alert alert-danger'>Unable to update record. Please try again.</div>";
}
}
// show errors
catch(PDOException $exception){
die('ERROR: ' . $exception->getMessage());
}
}
?>
Output
To update a database record, run the index.php file and click any Edit button.
Or, go to this URL:
http://localhost/php-beginner-crud-level-1/update.php?id=9
You will see the result like the images below.
Update record form.
Submitted form.
Changes in the database.
Delete record in PHP
Tell the user if record was deleted
This will tell the user if there is a deleted record after clicking the delete button and OK in the pop-up.
Open index.php file. Replace // delete message prompt will be here comment with the following code.
$action = isset($_GET['action']) ? $_GET['action'] : "";
// if it was redirected from delete.php
if($action=='deleted'){
echo "<div class='alert alert-success'>Record was deleted.</div>";
}
JavaScript to confirm record deletion
The user clicks on the Delete button in index.php.
Next, he will verify the deletion by clicking OK on the pop-up.
That user activity will execute the following JavaScript code.
Open index.php file. Replace <!-- confirm delete record will be here --> comment with the following code.
<script type='text/javascript'>
// confirm record deletion
function delete_user( id ){
var answer = confirm('Are you sure?');
if (answer){
// if user clicked ok,
// pass the id to delete.php and execute the delete query
window.location = 'delete.php?id=' + id;
}
}
</script>
Delete record from the database
The code below will delete a record from the database using the given ID parameter.
This answers the question: how to delete a record with PDO?
Create a new delete.php file, place the following code and save it.
<?php
// include database connection
include 'config/database.php';
try {
// get record ID
// isset() is a PHP function used to verify if a value is there or not
$id=isset($_GET['id']) ? $_GET['id'] : die('ERROR: Record ID not found.');
// delete query
$query = "DELETE FROM products WHERE id = ?";
$stmt = $con->prepare($query);
$stmt->bindParam(1, $id);
if($stmt->execute()){
// redirect to read records page and
// tell the user record was deleted
header('Location: index.php?action=deleted');
}else{
die('Unable to delete record.');
}
}
// show error
catch(PDOException $exception){
die('ERROR: ' . $exception->getMessage());
}
?>
Output
Once the user clicks any Delete button, it will show a confirmation pop-up.
If the user clicks the “OK” button, the record will be deleted from the database. It will tell the user via message prompt that the record was deleted.
The record is gone in the database as well.
Pagination in PHP
Please note that this is a bonus section and is not included in the LEVEL 1 source code download. We will have to add or remove some codes we’ve done above so that pagination will work.
Set pagination variables
The following variables are used to calculate the correct numbers for the LIMIT clause of our SELECT query.
We will see how our SELECT query will change later.
Place the following code below include ‘config/database.php‘; line of index.php file.
// PAGINATION VARIABLES // page is the current page, if there's nothing set, default is page 1 $page = isset($_GET['page']) ? $_GET['page'] : 1; // set records or rows of data per page $records_per_page = 5; // calculate for the query LIMIT clause $from_record_num = ($records_per_page * $page) - $records_per_page;
Add LIMIT clause in SELECT query
This will enable paginated requests to the database. Still on the index.php file, change the following code from:
$query = "SELECT id, name, description, price FROM products ORDER BY id DESC"; $stmt = $con->prepare($query); $stmt->execute();
to:
// select data for current page
$query = "SELECT id, name, description, price FROM products ORDER BY id DESC
LIMIT :from_record_num, :records_per_page";
$stmt = $con->prepare($query);
$stmt->bindParam(":from_record_num", $from_record_num, PDO::PARAM_INT);
$stmt->bindParam(":records_per_page", $records_per_page, PDO::PARAM_INT);
$stmt->execute();
Count total number of records
Counting the total number of records will help calculate the correct pagination numbers.
Below the closing table tag in the index.php file, add the following code.
// PAGINATION // count total number of rows $query = "SELECT COUNT(*) as total_rows FROM products"; $stmt = $con->prepare($query); // execute query $stmt->execute(); // get total rows $row = $stmt->fetch(PDO::FETCH_ASSOC); $total_rows = $row['total_rows'];
Include paging file
Add the following code after the previous section’s code.
// paginate records $page_url="index.php?"; include_once "paging.php";
Why a $page_url variable is needed? Because we made paging.php re-usable. You can use it for other objects you want to paginate.
For example, you’re trying to paginate your read_categories.php, you will need to do:
$page_url=”read_categories.php?”;
You will have to follow the code pattern of sections 10.1 to 10.3 when you use the paging.php file.
Create paging.php
Create a new paging.php file. Place the following code and save it.
<?php echo "<ul class='pagination pull-left margin-zero mt0'>"; // first page button will be here // clickable page numbers will be here // last page button will be here echo "</ul>"; ?>
Add first page button
Replace // first page button will be here comment of the previous section with the following code.
// first page button
if($page>1){
$prev_page = $page - 1;
echo "<li>
<a href='{$page_url}page={$prev_page}'>
<span style='margin:0 .5em;'>«</span>
</a>
</li>";
}
Add clickable page numbers
Open paging.php file.
Replace // clickable page numbers will be here comment with the following code.
// clickable page numbers
// find out total pages
$total_pages = ceil($total_rows / $records_per_page);
// range of num links to show
$range = 1;
// 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='javascript::void();'>{$x}</a>
</li>";
}
// not current page
else {
echo "<li>
<a href='{$page_url}page={$x}'>{$x}</a>
</li>";
}
}
}
Add last page button
Open paging.php file.
Replace // last page button will be here comment with the following code.
// last page button
if($page<$total_pages){
$next_page = $page + 1;
echo "<li>
<a href='{$page_url}page={$next_page}'>
<span style='margin:0 .5em;'>»</span>
</a>
</li>";
}
Output
Run index.php file on the browser:
http://localhost/php-beginner-crud-level-1/index.php
You should see the pagination buttons like the images below.
Read records page 1.
Read records page 2.
File upload in PHP
Now we are going to add a file upload feature when creating a record.
Add HTML “file” field
Open the create.php file and scroll down to the form. Find the opening “form tag and enable the file upload by changing it to:
<form action="<?php echo htmlspecialchars($_SERVER["PHP_SELF"]);?>" method="post" enctype="multipart/form-data">
Find the closing tr tag of the price field. Once found, add the following code after it.
<tr> <td>Photo</td> <td><input type="file" name="image" /></td> </tr>
Add “image” field
Still in create.php file. Scroll up and change the insert SQL query.
It should look like the following code. The new image field will store the file name of the submitted file.
// insert query
$query = "INSERT INTO products
SET name=:name, description=:description,
price=:price, image=:image, created=:created";
// prepare query for execution
$stmt = $con->prepare($query);
$name=htmlspecialchars(strip_tags($_POST['name']));
$description=htmlspecialchars(strip_tags($_POST['description']));
$price=htmlspecialchars(strip_tags($_POST['price']));
// new 'image' field
$image=!empty($_FILES["image"]["name"])
? sha1_file($_FILES['image']['tmp_name']) . "-" . basename($_FILES["image"]["name"])
: "";
$image=htmlspecialchars(strip_tags($image));
// bind the parameters
$stmt->bindParam(':name', $name);
$stmt->bindParam(':description', $description);
$stmt->bindParam(':price', $price);
$stmt->bindParam(':image', $image);
// specify when this record was inserted to the database
$created=date('Y-m-d H:i:s');
$stmt->bindParam(':created', $created);
Using PhpMyAdmin, add an “image” field in the products table as well.
Set variables for file upload
We will start the code for the file upload feature. Find the following line in the create.php file.
echo "<div class='alert alert-success'>Record was saved.</div>";
Under the code above, we will add the following code.
The if($image){ code will verify if there’s an uploaded image.
If there is, inside the if statement, we will set the initial variables needed for the file upload.
// now, if image is not empty, try to upload the image
if($image){
// sha1_file() function is used to make a unique file name
$target_directory = "uploads/";
$target_file = $target_directory . $image;
$file_type = pathinfo($target_file, PATHINFO_EXTENSION);
// error message is empty
$file_upload_error_messages="";
}
Make sure submitted file is a real image
Now we will start validating the submitted file. The code below will identify if the submitted file is a real or fake image.
Place the following code under $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
The following code will limit the allowed file types. Place it under the code of the previous section.
// 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
There’s a very small chance that the submitted file name will be the same as the one that exists in the server. This is because of the sha1_file() method we used in section 10.2 above.
But just in case there’s a file with the same name, tell the user. Place the following code after the previous section’s code.
// 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
Uploading a very large photo is not recommended in this case. So we will set the file size limit to less than 1 MB. Place the following code after the code of the previous section.
// 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
The “uploads” folder is where we will put the submitted file. Make sure it exists by using the following code. Place it under the code of the previous section.
// make sure the 'uploads' folder exists
// if not, create it
if(!is_dir($target_directory)){
mkdir($target_directory, 0777, true);
}
Try to upload the file
The move_uploaded_file built-in PHP function will place the uploaded file on the server directory.
Place the following code under 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{
echo "<div class='alert alert-danger'>
<div>Unable to upload photo.</div>
<div>Update the record to upload photo.</div>
</div>";
}
}
// if $file_upload_error_messages is NOT empty
else{
// it means there are some errors, so show them to user
echo "<div class='alert alert-danger'>
<div>{$file_upload_error_messages}</div>
<div>Update the record to upload photo.</div>
</div>";
}
Output
Form to create a product with file upload field.
When the form was submitted.
The “uploads” folder was created, with the uploaded file inside.
The file name was saved in the database.
Show uploaded image in PHP
Add image field in query
Open the read_one.php file and apply the following changes to the code.
Add image field to the $query variable. It should look like the following.
$query = "SELECT id, name, description, price, image FROM products WHERE id = ? LIMIT 0,1";
Add ‘$image’ variable after the ‘$price’ variable.
$image = htmlspecialchars($row['image'], ENT_QUOTES);
Add HTML image tag
Find the closing tr tag of the Price field in the HTML table and put the following code after it.
It will show the uploaded image or ‘No image found.’ if no image was uploaded.
<tr>
<td>Image</td>
<td>
<?php echo $image ? "<img src='uploads/{$image}' style='width:300px;' />" : "No image found."; ?>
</td>
</tr>
Output
Click the Read One button of the record we created with a file upload.
You should see something like the images below.
Record with an image.
Record without image.
Download Source Codes
Unlock the full potential of your PHP and MySQL CRUD skills with our source code downloads.
Choose your download
Whether you’re just starting or looking to take your knowledge to the next level, our BASIC and PRO options have everything you need to learn PHP CRUD operations.
Download our source codes today and accelerate your learning process. See the list of features below.
| FEATURES | BASIC | PRO |
|---|---|---|
| PDO extension used | ✓ | ✓ |
| Create product | ✓ | ✓ |
| Read product | ✓ | ✓ |
| Update product | ✓ | ✓ |
| Delete product | ✓ | ✓ |
| Bootstrap UI | ✓ | ✓ |
| Price display with a dollar sign | ✓ | ✓ |
| SQL file in the “dev” folder | ✓ | ✓ |
| HTML5 (font-end) validation for creating and updating a product | – | ✓ |
| Category selection for creating and updating a product. | – | ✓ |
| Buttons with Glyphicons | – | ✓ |
| Pagination | – | ✓ |
| Search products by name or description | – | ✓ |
| HTML5 (font-end) validation for search product | – | ✓ |
| Pagination in search | – | ✓ |
| Allow the user to enter the page number (read and search list) | – | ✓ |
| Export/download records to CSV | – | ✓ |
| Price display with comma and decimal point | – | ✓ |
| Multiple delete | – | ✓ |
| Create and update product records with file upload | – | ✓ |
| Bootstrap navigation bar | – | ✓ |
| Select and highlight a category in the navigation | – | ✓ |
| CRUD of product categories | – | ✓ |
| View products by category | – | ✓ |
| Search a category with pagination | – | ✓ |
| Server-side validation for creating and updating products & categories | – | ✓ |
| Sorting by fields with pagination | – | ✓ |
| Search product by date range with pagination | – | ✓ |
| jQuery UI calendar for picking a date | – | ✓ |
| Use the buttons below to download. ↓ | BASIC | PRO |
Why download?
Our source code offers many benefits to help you master CRUD operations and take your skills to the next level. Here are a few of the many benefits you can expect:
- Versatility: Use the skills you learn from our source code for multiple projects, saving you time and effort in the long run.
- Time-saving: Forget spending hours learning Bootstrap and PHP on your own. Our code examples are direct and to the point, making them easy to understand and implement.
- Clarity: Our source code is well explained and commented on, making it easy to follow and understand.
- Support: Our fast and friendly email support is always available to help you with any questions.
- Up-to-date: Our source code is constantly updated to ensure you always have access to the latest and most advanced features.
How To Run The Source Code?
After downloading and extracting the zip file, you will see a README.txt file. Open and follow the exact instructions on how to run the code.
If you have more questions, don’t hesitate to contact our support team at [email protected].
Thanks for supporting our website and projects here at codeofaninja.com!
What’s Next?
After learning from this PHP and MySQL CRUD tutorial for beginners, we can go one step higher. We can learn object-oriented programming (OOP) in PHP and MySQL.
Object-oriented programming (OOP) is a programming language model organized around objects rather than “actions” and data rather than logic. This is very exciting.
To learn more, we created the following tutorial just for you! Let’s go and learn: PHP OOP CRUD Tutorial: Object-oriented programming with PHP & MySQL
Online Resources
- What is the difference between MySQL, MySQLi and PDO extensions?
- MySQLi or PDO – what are the pros and cons?
- PDO vs. MySQLi: Which Should You Use?
- MySQLi vs. PDO Benchmarks
[adinserter block=”3″]
Leave a Reply