Simple PHP CRUD Operations with MySQL | Coding Cage

Simple PHP CRUD Operations with MySQL

On By
In this tutorial we are going to discuss about simple CRUD(Create , Read , Update , Delete) PHP operations , these are some of the basic things of PHP web application , Records are insert , select update and delete using PHP and MySQL, Creating a Simple Insert, Select, Update and Delete using PHP with MySQL Database is easy task. learning a crud operations is the first way to understand this is a simple and easy tutorial i have posted for beginners, let's have a look.
Simple PHP CRUD Operations with MySQL

Database Design and Table which are used in this tutorial
Database name : dbtuts
Table name : users
Table Columns : first_name , last_name , user_city

Copy-Paste the following sql schema in your MySql database to create database and table.

CREATE DATABASE `dbtuts` ;
CREATE TABLE `dbtuts`.`users` (
`user_id` INT( 10 ) NOT NULL AUTO_INCREMENT PRIMARY KEY ,
`first_name` VARCHAR( 25 ) NOT NULL ,
`last_name` VARCHAR( 25 ) NOT NULL ,
`user_city` VARCHAR( 45 ) NOT NULL
) ENGINE = InnoDB;

Database Configuration :
dbconfig.php 

<?php
$host = "localhost";
$user = "root";
$password = "";
$datbase = "dbtuts";
mysql_connect($host,$user,$password);
mysql_select_db($datbase);
?>

Create : insert new records

For providing user interface for the database insert, we should create a form, containing all the fields the users table has, except user_id which is auto generated numeric field. And, HTML code of ADD form will be as follows.
add_data.php 

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>CRUD Operations With PHP and MySql - By Cleartuts</title>
<link rel="stylesheet" href="style.css" type="text/css" />
</head>
<body>
<center>

<div id="header">
 <div id="content">
    <label>CRUD Operations With PHP and MySql - By Cleartuts</label>
    </div>
</div>
<div id="body">
 <div id="content">
    <form method="post">
    <table align="center">
    <tr>
    <td align="center"><a href="index.php">back to main page</a></td>
    </tr>
    <tr>
    <td><input type="text" name="first_name" placeholder="First Name" required /></td>
    </tr>
    <tr>
    <td><input type="text" name="last_name" placeholder="Last Name" required /></td>
    </tr>
    <tr>
    <td><input type="text" name="city_name" placeholder="City" required /></td>
    </tr>
    <tr>
    <td><button type="submit" name="btn-save"><strong>SAVE</strong></button></td>
    </tr>
    </table>
    </form>
    </div>
</div>

</center>
</body>

On the top of the html tag we need to put PHP code that handle database connection and insert new records into to the user table , So the code is as follow :
put the following php insert code just above starting tag


<?php
include_once 'dbconfig.php';
if(isset($_POST['btn-save']))
{
 // variables for input data
 $first_name = $_POST['first_name'];
 $last_name = $_POST['last_name'];
 $city_name = $_POST['city_name'];
 // variables for input data
 
 // sql query for inserting data into database
 
        $sql_query = "INSERT INTO users(first_name,last_name,user_city) VALUES('$first_name','$last_name','$city_name')";
 mysql_query($sql_query);
        
        // sql query for inserting data into database
 
}
?>

above html form will look like :

Simple PHP CRUD operation

Read : Retrieveng Records From table


After data insert we need to retrieve all the records from users table :
Retrieveng Records From users table
following is the php code for selecting records from table.

$sql_query="SELECT * FROM users";
$result_set=mysql_query($sql_query);

Using the following while() loop we can fetch all the records from users table. For example

<?php
$sql_query="SELECT * FROM users";
$result_set=mysql_query($sql_query);
while($row = mysql_fetch_array($result))
{
    ?>
    <!--Add HTML code here to display records.-->
    <?php
}
?>

After that, list of database records will be displayed to the browser, as shown below. And, this list contains icons to trigger update and delete operations for each record.
index.php 

<?php
include_once 'dbconfig.php';
?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>CRUD Operations With PHP and MySql - By Cleartuts</title>
<link rel="stylesheet" href="style.css" type="text/css" />
</head>
<body>
<center>

<div id="body">
 <div id="content">
    <table align="center">
    <tr>
    <th colspan="5"><a href="add_data.php">add data here.</a></th>
    </tr>
    <th>First Name</th>
    <th>Last Name</th>
    <th>City Name</th>
    <th colspan="2">Operations</th>
    </tr>
    <?php
 $sql_query="SELECT * FROM users";
 $result_set=mysql_query($sql_query);
 while($row=mysql_fetch_row($result_set))
 {
  ?>
        <tr>
        <td><?php echo $row[1]; ?></td>
        <td><?php echo $row[2]; ?></td>
        <td><?php echo $row[3]; ?></td>
  <td align="center"><a href="javascript:edt_id('<?php echo $row[0]; ?>')"><img src="b_edit.png" align="EDIT" /></a></td>
        <td align="center"><a href="javascript:delete_id('<?php echo $row[0]; ?>')"><img src="b_drop.png" align="DELETE" /></a></td>
        </tr>
        <?php
 }
 ?>
    </table>
    </div>
</div>

</center>
</body>
</html>

Output :

PHP CRUD operations with MySQL

Update : Update MySQL records.


to complete this operation with each record we can use edit icon that displayed with each record as shown above screenshot on clicking this icon for the specified row the page will jump to the edit_data.php with the specified row and details which are to be edited are filled up in the below form , and it will look like.

PHP CRUD operations with MySQL

For that we need to fetch the selected row from users table by using SELECT Query and the QueryString which are set in the edit_data.php url with edit_id=value.

if(isset($_GET['edit_id']))
{
 $sql_query="SELECT * FROM users WHERE user_id=".$_GET['edit_id'];
 $result_set=mysql_query($sql_query);
 $fetched_row=mysql_fetch_array($result_set);
}

after selecting data we need to execute UPDATE Query as follow

if(isset($_POST['btn-update']))
{
 // variables for input data
 $first_name = $_POST['first_name'];
 $last_name = $_POST['last_name'];
 $city_name = $_POST['city_name'];
 // variables for input data
 
 // sql query for update data into database
 $sql_query = "UPDATE users SET first_name='$first_name',last_name='$last_name',user_city='$city_name' WHERE user_id=".$_GET['edit_id'];
        mysql_query($sql_query);
 // sql query for update data into database
}

edit_data.php 

<?php
include_once 'dbconfig.php';
if(isset($_GET['edit_id']))
{
 $sql_query="SELECT * FROM users WHERE user_id=".$_GET['edit_id'];
 $result_set=mysql_query($sql_query);
  $fetched_row=mysql_fetch_array($result_set);
}
if(isset($_POST['btn-update']))
{
 // variables for input data
 $first_name = $_POST['first_name'];
 $last_name = $_POST['last_name'];
 $city_name = $_POST['city_name'];
 // variables for input data
 
 // sql query for update data into database
 $sql_query = "UPDATE users SET first_name='$first_name',last_name='$last_name',user_city='$city_name' WHERE user_id=".$_GET['edit_id'];
        mysql_query($sql_query));
 // sql query for update data into database 
}
?>

<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>CRUD Operations With PHP and MySql - By Cleartuts</title>
<link rel="stylesheet" href="style.css" type="text/css" />
</head>
<body>
<center>

<div id="body">
 <div id="content">
    <form method="post"><
    <table align="center">
    <tr>
    <td><input type="text" name="first_name" placeholder="First Name" value="<?php echo $fetched_row['first_name']; ?>" required /></td>
    </tr>
    <tr>
    <td><input type="text" name="last_name" placeholder="Last Name" value="<?php echo $fetched_row['last_name']; ?>" required /></td>
    </tr>
    <tr>
    <td><input type="text" name="city_name" placeholder="City" value="<?php echo $fetched_row['user_city']; ?>" required /></td>
    </tr>
    <tr>
    <td>
    <button type="submit" name="btn-update"><strong>UPDATE</strong></button>
    </td>
    </tr>
    </table>
    </form>
    </div>
</div>

</center>
</body>
</html>

Delete : delete records from MySQL Table.

By clicking on delete icon of any specified user record, the users id will set to the URL QueryString of the page and the QueryString index.php?delete_id=any_value if set the Sql Query DELETE will be executed by if(isset($_GET['delete_id'])) function , and delete statement is as follow : 

if(isset($_GET['delete_id']))
{
 $sql_query="DELETE FROM users WHERE user_id=".$_GET['delete_id'];
 mysql_query($sql_query);
 header("Location: $_SERVER[PHP_SELF]");
}
put the above delete php code in index.php file above data insert code .
now there is CRUD operations are completed.
thats' it
Complete CRUD Script

dbconfig.php 

<?php
$host = "localhost";
$user = "root";
$password = "";
$datbase = "dbtuts";
mysql_connect($host,$user,$password);
mysql_select_db($datbase);
?>

index.php 

<?php
include_once 'dbconfig.php';

// delete condition
if(isset($_GET['delete_id']))
{
 $sql_query="DELETE FROM users WHERE user_id=".$_GET['delete_id'];
 mysql_query($sql_query);
 header("Location: $_SERVER[PHP_SELF]");
}
// delete condition
?>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>CRUD Operations With PHP and MySql - By Cleartuts</title>
<link rel="stylesheet" href="style.css" type="text/css" />
<script type="text/javascript">
function edt_id(id)
{
 if(confirm('Sure to edit ?'))
 {
  window.location.href='edit_data.php?edit_id='+id;
 }
}
function delete_id(id)
{
 if(confirm('Sure to Delete ?'))
 {
  window.location.href='index.php?delete_id='+id;
 }
}
</script>
</head>
<body>
<center>

<div id="header">
 <div id="content">
    <label>CRUD Operations With PHP and MySql - <a href="http://cleartuts.blogspot.com" target="_blank">By Cleartuts</a></label>
    </div>
</div>

<div id="body">
 <div id="content">
    <table align="center">
    <tr>
    <th colspan="5"><a href="add_data.php">add data here.</a></th>
    </tr>
    <th>First Name</th>
    <th>Last Name</th>
    <th>City Name</th>
    <th colspan="2">Operations</th>
    </tr>
    <?php
 $sql_query="SELECT * FROM users";
 $result_set=mysql_query($sql_query);
 while($row=mysql_fetch_row($result_set))
 {
  ?>
        <tr>
        <td><?php echo $row[1]; ?></td>
        <td><?php echo $row[2]; ?></td>
        <td><?php echo $row[3]; ?></td>
  <td align="center"><a href="javascript:edt_id('<?php echo $row[0]; ?>')"><img src="b_edit.png" align="EDIT" /></a></td>
        <td align="center"><a href="javascript:delete_id('<?php echo $row[0]; ?>')"><img src="b_drop.png" align="DELETE" /></a></td>
        </tr>
        <?php
 }
 ?>
    </table>
    </div>
</div>

</center>
</body>
</html>

add_data.php 

<?php
include_once 'dbconfig.php';
if(isset($_POST['btn-save']))
{
 // variables for input data
 $first_name = $_POST['first_name'];
 $last_name = $_POST['last_name'];
 $city_name = $_POST['city_name'];
 // variables for input data

 // sql query for inserting data into database
 $sql_query = "INSERT INTO users(first_name,last_name,user_city) VALUES('$first_name','$last_name','$city_name')";
 // sql query for inserting data into database
 
 // sql query execution function
 if(mysql_query($sql_query))
 {
  ?>
  <script type="text/javascript">
  alert('Data Are Inserted Successfully ');
  window.location.href='index.php';
  </script>
  <?php
 }
 else
 {
  ?>
  <script type="text/javascript">
  alert('error occured while inserting your data');
  </script>
  <?php
 }
 // sql query execution function
}
?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>CRUD Operations With PHP and MySql - By Cleartuts</title>
<link rel="stylesheet" href="style.css" type="text/css" />
</head>
<body>
<center>

<div id="header">
 <div id="content">
    <label>CRUD Operations With PHP and MySql - By Cleartuts</label>
    </div>
</div>
<div id="body">
 <div id="content">
    <form method="post">
    <table align="center">
    <tr>
    <td align="center"><a href="index.php">back to main page</a></td>
    </tr>
    <tr>
    <td><input type="text" name="first_name" placeholder="First Name" required /></td>
    </tr>
    <tr>
    <td><input type="text" name="last_name" placeholder="Last Name" required /></td>
    </tr>
    <tr>
    <td><input type="text" name="city_name" placeholder="City" required /></td>
    </tr>
    <tr>
    <td><button type="submit" name="btn-save"><strong>SAVE</strong></button></td>
    </tr>
    </table>
    </form>
    </div>
</div>

</center>
</body>
</html>

edit_data.php 

<?php
include_once 'dbconfig.php';
if(isset($_GET['edit_id']))
{
 $sql_query="SELECT * FROM users WHERE user_id=".$_GET['edit_id'];
 $result_set=mysql_query($sql_query);
 $fetched_row=mysql_fetch_array($result_set);
}
if(isset($_POST['btn-update']))
{
 // variables for input data
 $first_name = $_POST['first_name'];
 $last_name = $_POST['last_name'];
 $city_name = $_POST['city_name'];
 // variables for input data

 // sql query for update data into database
 $sql_query = "UPDATE users SET first_name='$first_name',last_name='$last_name',user_city='$city_name' WHERE user_id=".$_GET['edit_id'];
 // sql query for update data into database
 
 // sql query execution function
 if(mysql_query($sql_query))
 {
  ?>
  <script type="text/javascript">
  alert('Data Are Updated Successfully');
  window.location.href='index.php';
  </script>
  <?php
 }
 else
 {
  ?>
  <script type="text/javascript">
  alert('error occured while updating data');
  </script>
  <?php
 }
 // sql query execution function
}
if(isset($_POST['btn-cancel']))
{
 header("Location: index.php");
}
?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>CRUD Operations With PHP and MySql - By Cleartuts</title>
<link rel="stylesheet" href="style.css" type="text/css" />
</head>
<body>
<center>

<div id="header">
 <div id="content">
    <label>CRUD Operations With PHP and MySql - By Cleartuts</label>
    </div>
</div>

<div id="body">
 <div id="content">
    <form method="post">
    <table align="center">
    <tr>
    <td><input type="text" name="first_name" placeholder="First Name" value="<?php echo $fetched_row['first_name']; ?>" required /></td>
    </tr>
    <tr>
    <td><input type="text" name="last_name" placeholder="Last Name" value="<?php echo $fetched_row['last_name']; ?>" required /></td>
    </tr>
    <tr>
    <td><input type="text" name="city_name" placeholder="City" value="<?php echo $fetched_row['user_city']; ?>" required /></td>
    </tr>
    <tr>
    <td>
    <button type="submit" name="btn-update"><strong>UPDATE</strong></button>
    <button type="submit" name="btn-cancel"><strong>Cancel</strong></button>
    </td>
    </tr>
    </table>
    </form>
    </div>
</div>

</center>
</body>
</html>

style.css 

@charset "utf-8";
/* CSS Document */

*
{
 margin:0;
 padding:0;
}
body
{
 background:#f9f9f9;
 font-family:"Courier New", Courier, monospace;
}
#header
{
 width:100%;
 height:50px;
 background:#00a2d1;
 color:#f9f9f9;
 font-family:"Lucida Sans Unicode", "Lucida Grande", sans-serif;
 font-size:35px;
 text-align:center;
}
#header a
{
 color:#fff;
 text-decoration:blink;
}
#body
{
 margin-top:50px;
}
table
{
 width:80%;
 font-family:Tahoma, Geneva, sans-serif;
 font-weight:bolder;
 color:#999;
 margin-bottom:80px;
}
table a
{
 text-decoration:none;
 color:#00a2d1;
}
table,td,th
{
 border-collapse:collapse;
 border:solid #d0d0d0 1px;
 padding:20px;
}
table td input
{
 width:97%;
 height:35px;
 border:dashed #00a2d1 1px;
 padding-left:15px;
 font-family:Verdana, Geneva, sans-serif;
 box-shadow:0px 0px 0px rgba(1,0,0,0.2);
 outline:none;
}
table td input:focus
{
 box-shadow:inset 1px 1px 1px rgba(1,0,0,0.2);
 outline:none;
}
table td button
{
 border:solid #f9f9f9 0px;
 box-shadow:1px 1px 1px rgba(1,0,0,0.2);
 outline:none;
 background:#00a2d1;
 padding:9px 15px 9px 15px;
 color:#f9f9f9;
 font-family:Arial, Helvetica, sans-serif;
 font-weight:bolder;
 border-radius:3px;
 width:49.5%;
}
table td button:active
{
 position:relative;
 top:1px;
}
I hope you guys like this tutorial, please don't forget to share and feel free to ask any question regarding this tutorial.



Like This Post? Please share!
Subscribe through email and get Free updates

You May Also Like...

31 comments:

  1. Allow me to introduce you to my friend, Bobby Drop Tables T_T: https://xkcd.com/327/.
    I know it's a year old, but a fairly high ranking result in Google. Best to use PDO. Or at least $first_name = mysql_real_escape_string($_POST['first_name']);

    ReplyDelete

  2. wow Thats awesome can I download this tutorial

    ReplyDelete

  3. please how do i add images and pdf's to the crud operations and save them in a folder. I tried following these other two tutorials http://cleartuts.blogspot.com/2014/12/file-upload-and-view-with-php-and-mysql.html and http://cleartuts.blogspot.com/2014/12/simple-file-uploading-with-php.html, but the code mixes up and does not work.

    ReplyDelete
    Replies

    1. just alter your mysql table and add image, pdf file fields to the table and make change in sql insert query as i showed in following tutorial :

      https://codingcage.com/2014/12/file-upload-and-view-with-php-and-mysql.html

      it inserts file types just you have to make change in sql insert query , as showed in crud tutorials the inserting process was same ...

      Delete
  4. Replies

    1. there is css code in this tutorial just after edit-data.php code.

      Delete

  5. search function na lang ahee thanks =)))

    ReplyDelete

  6. script functions missing in index.php, use onclick="" instead of href="", Not tested code,

    ReplyDelete

  7. scripts are there, thanks for the tutorial

    ReplyDelete

  8. Thanks for such valuable and most used codes of PHP and mysql.
    kairasoftware.com

    ReplyDelete

  9. For some reason the dbconfig.php file breaks the page. It doesn't load when I put it in the /var/www/html folder. But when I remove it localhost shows up but nothing works for obvious reasons since the config file is missing. Is there something I'm forgetting to include in the config file? I'm assuming the password is what i set for mysql.

    ReplyDelete
    Replies

    1. just for trial, usually I'm using

      php -S localhost:8084

      Note :
      execute from your project folder
      port is optional, just give another number in case post 80 is already used
      for accesing from other client in network, replace localhost with your IP, and give exception/access for the firewall request

      hope this helps

      Delete

  10. In index.php, the 'javascript:edt_id' function does not redirect the page to edit_data.php. Because the codes does not state which page it should go after clicking 'EDIT'. Am i missing something here? My edit and delete doesn't work..

    ReplyDelete

  11. Script file is not exist..please upload it again

    ReplyDelete

  12. how can I make a registration form with data and image for crud operation. please suggest me as I am a beginner for website and database

    ReplyDelete

  13. Thanks a lot friend, you're a good programmer

    ReplyDelete

  14. crud operation image with info is needed for upload image and data to database

    ReplyDelete

  17. good for learning thank you

    ReplyDelete

  18. thankyou sir,good tutorial

    ReplyDelete

  22. how to fix this one?


    Notice: Undefined index: user_city in D:\xampp\htdocs\dbtuts\add_data.php on line 8
    Error: INSERT INTO users (first_name,last_name,user_city,) VALUES ('123123','Pineda','')
    You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ') VALUES ('123123','Pineda','')' at line 1

    ReplyDelete

  23. How to fix this error?

    Notice: Undefined index: user_city in D:\xampp\htdocs\dbtuts\add_data.php on line 8
    Error: INSERT INTO users (first_name,last_name,user_city,) VALUES ('123123','Pineda','')
    You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ') VALUES ('123123','Pineda','')' at line 1

    ReplyDelete

  24. is there an updated article that uses mysqli or pdo

    ReplyDelete

  25. This tutorial does not work.... Plus the script file is no longer up... :/

    ReplyDelete

  26. edit and delete button is not working....

    ReplyDelete