(PHP CRUD Tutorial) Create, Select, Update, Edit, Insert, Delete And Display Image From MySQL database In PHP (Source Code)
(PHP CRUD Tutorial) Create, Select, Update, Edit, Insert, Delete And Display Image From MySQL database In PHP (Source Code)
The purpose of this tutorial is to guide on how to create a simple PHP CRUD (Create, edit, update and delete) posts with MySQL database. To make a dynamic website you need to be able to create new posts and save them in the database, Update the created posts, and delete them as well. This web app connects to the database and use the established connection to make database queries. To connect to mysql database you need login credentials. In this guide i have used “root” as the username and “” empty password. Xampp local server enables you to perform all the crud operations provided by this web application. Localhost or 127.0.0.1 can be used as the server name, you can also confirm the server name using PHPmyAdmin. User can fill the HTML form and submit it to the server for processing. The submitted data can be retrieved and displayed on the webpage. You can also select images from your computer using the provided html form and upload them to the server. The uploaded images are saved in folder found in the server and the image path is saved in MYSQL Database as a text in a VARCHAR type, column.
Project Overview
Step: 1 — CONNECTING TO SERVER & MYSQL DATABASE
Username for mysql database is “root” and password is “”. I have used empty string for password field in the database (SERVER) but you can set it if you want.
To establish a connection ensure that mysql and apached are both running. You can easily enable them by clicking start buttons next to them. You can change the username and password using PHPmyAdmin. To access phpmyadmin, type
http://localhost/phpmyadmin
on your browser. PHPmyAdmin is a php graphical user interface for managing databases like mysql. Using PHPmyAdmin, you can execute sql queries, create databases, create tables, insert into tables, select from tables, delete data from tables, update data stored in tables, drop databases, change server/database username and password. etc.
The database code saved in the projects folder as databaseconn.php, connects to the server and checks if the database with the provided name exists, if the database doesn’t exist, a new database with the provided name is created. After that, the query for creating new table is executed. A new table is created inside the currently created database. This happens automatically when you run the code below. You don’t have to create the database and table manually using PHPMyAdmin.
databaseconn.php
<?php
$connectdb = mysqli_connect("localhost", "root", "");
if ($connectdb) {
echo "<br/> Connected to server";
}else{
die("<br />Connection error ". mysqli_connect_error());
}
$selectdb = mysqli_select_db($connectdb, "products");
if ($selectdb) {
echo "<br />Existing Database Selected";
} else {
$sqlcreatedb = "CREATE DATABASE IF NOT EXISTS `products`";
if (mysqli_query($connectdb, $sqlcreatedb)) {
echo "<br />New database created";
$selectdb2 = mysqli_select_db($connectdb, "products");
if ($selectdb2) {
echo "<br />Created database selected";
$sqlcreatetable = "
CREATE TABLE IF NOT EXISTS `productstb` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`firstname` varchar(100) NOT NULL,
`lastname` varchar(100) NOT NULL,
`email` varchar(100) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
";
if (mysqli_query($connectdb,$sqlcreatetable)) {
echo "<br />New table created";
} else {
echo "<br />No table created";
}
}
} else {
echo "<br />No database created";
}
}
?>
Step: 2— Creating html file for crud
The form has various input fields for inserting data into the database i.e. File upload field, Dropdown menu field, Date chooser field and Normal text input fields. The inserted data is also displayed immediately after its saved in mysql database. This is accomplished by using a “select query” , that selects all data inserted in the database and displays it on the web page in a tabular format. Their is also a search feature where you can search for specific saved data using keywords. Just focus on search box by clicking inside the search input field , start typing and finally click search button . The table will be populated with the found rows based on the entered keyword.
index.php
<?php
include("databaseconn.php");
if(isset($_POST['addtopredfetch'])){
$searchfromdb = $_POST['searchfromdb'];
$sel = "SELECT * FROM `productstb` WHERE `id` = '$searchfromdb'";
$qry = mysqli_query($connectdb, $sel);
$fetch = mysqli_fetch_assoc($qry);
$pid = $fetch['id'];
$pproductname = $fetch['productname'];
$pproductprice = $fetch['productprice'];
$pdate = $fetch['date'];
$pimage = $fetch['image'];
}
if (isset($_POST['searchfromdbsub'])) {
$searchfromdb = $_POST['searchfromdb'];
$del = "DELETE FROM productstb WHERE `id` = '$searchfromdb'";
$qry = mysqli_query($connectdb, $del);
if($qry) {
header("location: index.php");
}
}
?>
<!DOCTYPE html>
<html lang="eng-us">
<head>
<meta charset="utf-8" />
<title>PHP ONLINE SYSTEM</title>
<link rel="stylesheet" href="style.css" type="text/css" />
</head>
<body>
<div id="container">
<div id="head">
<p>PHP ONLINE SYSTEM</p>
</div>
<div id="main">
<div id="left">
<form action="functions.php" method="post" enctype="multipart/form-data" id="myForm">
<ul class="form1">
<li><input type="text" name="productid" placeholder="ID" value ="
<?php if(!isset($pid)) {echo "";} else {echo $pid; } ?>" /></li>
<li><input type="text" name="productname" placeholder="Name" value ="
<?php if(!isset($pproductname)) {echo "";} else {echo $pproductname; } ?>" /></li>
<li><input type="text" name="productprice" placeholder="Price" value ="
<?php if(!isset($pproductprice)) {echo "";} else {echo $pproductprice; } ?>" /></li>
<li><input type="date" name="date" placeholder="Date" value ="
<?php if(!isset($pdate)) {echo "";} else {echo $pdate; } ?>" /></li>
<li><input type="file" name="uploadimg" id="uploadimg" value ="
<?php if(!isset($pimage)) {echo "";} else {echo $pimage; } ?>" /></li>
<li><input type="submit" name="addtopred" value="Add Product"></li>
<li><input type="submit" name="editdata" value="Update Product"></li>
</ul>
</form>
<div id="imgLoc">
<img src="upload/<?php if(!isset($pimage)) {echo "";} else {echo $pimage; } ?>" id="imgLocim" height="200" width="200" />
</div>
</div>
<div id="center">
<form method="post" action="">
<select name="searchfromdb">
<option>Choose ID</option>
<?php
include("databaseconn.php");
$sel = "SELECT * FROM `productstb`";
$qry = mysqli_query($connectdb, $sel);
while($row=mysqli_fetch_array($qry)){
echo "<option>".$row['id']."</option>";
}
?>
</select><br/><br/>
<input type="submit" name="searchfromdbsub" value="Delete"/>
<input type="submit" name="addtopredfetch" value="Fetch Product">
</form>
<form method="POST" action="functions.php" enctype="multipart/form-data">
</form>
</div>
<div id="right">
<div class="headsearch">
<form action="" method="post">
<input type="text" name="search" placeholder="Search.........." />
<input type="submit" name="searchsub" Value="Search"/>
</form>
</div>
<div class="productsDis">
<table>
<tr>
<th>ID</th>
<th>Product Name</th>
<th>Price</th>
<th>Date</th>
<th>Image</th>
</tr>
<?php
include("databaseconn.php");
if(isset($_POST['searchsub'])) {
$search = $_POST['search'];
$Sel = "SELECT * FROM `productstb` WHERE `productname` LIKE '%$search%'";
$qrysearch = mysqli_query($connectdb, $Sel);
while($row = mysqli_fetch_array($qrysearch)) {
echo "
<tr>
<td>".$row['id']."</td>
<td>".$row['productname']." </td>
<td>".$row['productprice']." </td>
<td>".$row['date']."</td>
<td><img src='upload/".$row['image']."' height='60px' width='40px'/> </td>
</tr>
";
}
}else {
$sqlselect = "SELECT * FROM `productstb`";
$qry = mysqli_query($connectdb, $sqlselect);
while ( $row=mysqli_fetch_array($qry)) {
echo "
<tr>
<td>".$row['id']."</td>
<td>".$row['productname']." </td>
<td>".$row['productprice']." </td>
<td>".$row['date']."</td>
<td><img src='upload/".$row['image']."' height='60px' width='40px'/> </td>
</tr>
";
}
}
?>
</table>
</div>
</div>
</div>
<div id="footer">
</div>
</div>
</body>
</html>
Step: 3 — Creating Cascading Style Sheets (CSS)
Css is a language for describing the presentation of the document made with html(Hypertext Markup Language) Its ideal for formating the pages and making them more appealing to users. I have used external css wich is loaded by using
<link rel="stylesheet" href="style.css" type="text/css" />
code. This code is placed inside the index page, in the <head></head> just below the <title></title> section.
style.css
/*(PHP CRUD Tutorial) Create, Select, Update, Edit, Insert, Delete And Display Image From MySQL database In PHP (Source Code)
*/
*{
margin: 0px;
padding: 0px;
}
body{
font-family: courier;
}
#container {
width:1200px;
height: 600px;
margin: 0px auto;
}
#head {
height: 100px;
width: 100%;
background-color: black;
color: #ffffff;
}
#head p {
font-size: 50px;
line-height: 100px;
vertical-align: left;
margin-left: 20px;
}
#main{
height: 650px;
width: 100%;
background-color: gray;
position: relative;
}
.form1 {
list-style-type: none;
margin: 0px;
padding: 0px;
}
#left {
padding: 5px;
float: left;
width:250px;
}
.form1 input[type="text"] {
padding: 10px;
font-size: 15px;
}
.form1 input[type="submit"] {
padding: 10px;
font-size: 15px;
width: 200px;
}
.form1 input[type="date"] {
padding: 10px;
font-size: 15px;
}
.form1 input[type="file"] {
padding: 10px;
font-size: 15px;
}
.form1 li {
margin: 10px;
}
#imgLoc {
height: 200px;
width:200px;
background-color: #ffffff;
margin: 10px;
}
#center {
margin: 10px;
width:246px;
height: 450px;
left: 300px;
position: absolute;
float: left;
padding: 2px;
}
#buttons {
text-align: center;
background-color: #ffffff;
border: 1px solid #009933;
height: 50px;
width: 240px;
margin-bottom: 5px;
}
#buttons a{
display:block;
line-height: 50px;
text-decoration: none;
color:gray;
}
#buttons a:hover{
color:#ffffff;
background-color: maroon;
border: 1px solid #ffffff;
}
select {
padding: 10px;
font-size: 15px;
width: 242px;
}
#center input[type="submit"] {
padding: 10px;
width: 242px;
margin-bottom: 10px;
}
.headsearch {
margin: 10px;
height: 70px;
width: 400px;
}
.headsearch input[type="submit"] {
padding: 10px;
width: 140px;
margin-bottom: 10px;
}
.headsearch input[type="text"] {
padding: 10px;
width: 200px;
margin-bottom: 10px;
}
.productsDis {
width: 500px;
height: 510px;
background-color: white;
margin-top: 10px;
}
#right{
float:right;
margin-right: 10px;
width: 500px;
height: 600px;
}
.productsDis td,th{
padding: 10px;
}
.productsDis tr {
border-bottom: 1px solid gray;
}
.productsDis th {
}
div.productsDis > table {
border-collapse: collapse;
width: 100%;
}
.productsDis tr:nth-child(odd) {
background-color: #66cc99;
}
Step: 4— Creating process file for crud
All the form and (CRUD) queries are processed in this file. This file only executes server queries and its not visible on users browser. You are supposed to specify this page in the forms “action” section. ie. When you click submit button the queries are sent to the page associated with the specified action “value”, in this case functions.php
<form action="functions.php" method="post" enctype="multipart/form-data" id="myForm">
functions.php
<?php
// (PHP CRUD Tutorial) Create, Select, Update, Edit, Insert, Delete And Display Image From MySQL database In PHP (Source Code)
include("databaseconn.php");
if(isset($_POST['addtopred'])) {
$productname = $_POST['productname'];
$productprice = $_POST['productprice'];
$date = $_POST['date'];
$productidname = $_FILES['uploadimg']['name'];
$productidtmp = $_FILES['uploadimg']['tmp_name'];
$folder = "upload/";
move_uploaded_file($productidtmp, $folder.$productidname);
$insert = "INSERT INTO productstb (`productname`, `productprice`, `date`, `image`)
VALUES ('$productname', '$productprice', '$date', '$productidname')";
$qry = mysqli_query($connectdb, $insert);
if($qry) {
echo "inserted";
header("location: index.php");
}
}
if(isset($_POST['editdata'])){
$pidup = trim($_POST['productid']);
$productnameup = $_POST['productname'];
$productpriceup = $_POST['productprice'];
$dateup = $_POST['date'];
$uploadimgupname = $_FILES['uploadimg']['name'];
$uploadimguptmp = $_FILES['uploadimg']['tmp_name'];
$folder = "upload/";
move_uploaded_file($uploadimguptmp, $folder.$uploadimgupname);
if(!empty($productnameup) && !empty($productpriceup) && !empty($dateup) && !empty($uploadimgupname) ) {
$update = "UPDATE `productstb` SET
`productname`='$productnameup',
`productprice`='$productpriceup',
`date`='$dateup',
`image`='$uploadimgupname'
WHERE `id` = '$pidup'";
$qryupdate = mysqli_query($connectdb, $update);
if(!$qryupdate) {
echo "error";
exit();
}else {
header("location: index.php");
}
} else {
echo "all fields must be filled";
}
}
?>
Other php with mysql database video tutorials :
PHP And MySQL Database CRUD (Insert,View,Update,Delete) Tutorial – PART 0 BRIEF OVERVIEW OF THE TARGET SYSTEM
https://www.youtube.com/watch?v=2IGV0fXyj18
PHP And MySQL Database CRUD (Insert,View,Update,Delete) Tutorial – PART 1 HOW TO DOWNLOAD SUBLIME TEXT
https://www.youtube.com/watch?v=HZMHWvCSw2k
PHP And MySQL Database CRUD (Insert,View,Update,Delete) Tutorial – PART 2 HOW TO DOWNLOAD AND INSTALL XAMPP SERVER
https://www.youtube.com/watch?v=3yUSO645zVg
PHP And MySQL Database CRUD (Insert,View,Update,Delete) Tutorial – PART 3 LAYOUT
https://www.youtube.com/watch?v=LiQJd3f1Kwc
PHP And MySQL Database CRUD (Insert,View,Update,Delete) Tutorial – PART 4 LAYOUT
https://www.youtube.com/watch?v=PJlxEkkG80U
PHP And MySQL Database CRUD (Insert,View,Update,Delete) Tutorial – PART 5 LAYOUT
https://www.youtube.com/watch?v=sXRaaZjeyrg
PHP And MySQL Database CRUD (Insert,View,Update,Delete) Tutorial – PART 6 LAYOUT
https://www.youtube.com/watch?v=w8nXFda25hE
PHP And MySQL Database CRUD (Insert,View,Update,Delete) Tutorial – PART 7 DATABASE CONNECTION
https://www.youtube.com/watch?v=ZqxucNfvmiw
PHP And MySQL Database CRUD (Insert,View,Update,Delete) Tutorial – PART 8 CREATING TABLE
https://www.youtube.com/watch?v=cquy2aIuL9M
PHP And MySQL Database CRUD (Insert,View,Update,Delete) Tutorial – PART 9 INSERT DATA INTO DATABASE
https://www.youtube.com/watch?v=Sb_IdfXy8mg
PHP And MySQL Database CRUD (Insert,View,Update,Delete) Tutorial – PART 10 – SELECT AND DISPLAY DATA FROM DATABASE IN A TABLE
https://www.youtube.com/watch?v=nInTVy3yl5c
PHP And MySQL Database CRUD (Insert,View,Update,Delete) Tutorial – PART 11 – POPULATE DROPDOWN WITH DATA FROM DATABASE
https://www.youtube.com/watch?v=7EKnOOS0Fmw
PHP And MySQL Database CRUD (Insert,View,Update,Delete) Tutorial – PART 12 DELETE DATA FROM DATABASE
https://www.youtube.com/watch?v=S2NFpcGMl0Y
PHP And MySQL Database CRUD (Insert,View,Update,Delete) Tutorial – PART 13 DISPLAYING DATA FROM DATABBASE ON INPUT FIELDS
https://www.youtube.com/watch?v=nPTi33PcO8Y
PHP And MySQL Database CRUD (Insert,View,Update,Delete) Tutorial – PART 14 UPDATING AND EDITING DATA IN DATABBASE
https://www.youtube.com/watch?v=y_XsGWrfqLg
PHP And MySQL Database CRUD (Insert,View,Update,Delete) Tutorial – PART 15 SEARCHING DATA FROM DATABBASE
https://www.youtube.com/watch?v=G0epWltstug
PHP And MySQL Database CRUD (Insert,View,Update,Delete) Tutorial – PART 16 FINAL VIDEO
https://www.youtube.com/watch?v=HXAGIKfQj6c
