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″]
This tutorial is for your if:
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:
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.
Our PHP CRUD tutorial will contain the following main files.
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.
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 ;
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.
This section will answer the question: how to connect to MySQL database with PDO?
database.php
file.<?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(); } ?>
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.
We use the Bootstrap user interface for this project. If you are not familiar with Bootstrap, please learn our Bootstrap Tutorial for Beginners.
php-beginner-crud-level-1
folder.create.php
file.create.php
file.<!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>
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>
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()); } } ?>
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.
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>
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>"; } ?>
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>";
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>"; }
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.
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>
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()); } ?>
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>
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.
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>
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()); } ?>
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>
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()); } } ?>
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.
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>"; }
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>
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()); } ?>
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.
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.
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;
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();
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'];
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 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>"; ?>
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>"; }
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>"; } } }
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>"; }
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.
Now we are going to add a file upload feature when creating a record.
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>
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.
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=""; }
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>"; }
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>"; }
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>"; }
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>"; }
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); }
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>"; }
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.
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);
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>
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.
Unlock the full potential of your PHP and MySQL CRUD skills with our source code downloads.
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 |
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:
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!
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
[adinserter block=”3″]