CRUD Operations Using PHP OOP and MySQL

In this tutorial, we will look into how to perform CRUD operations using PHP and MySQL, the Object Oriented way.
To perform this operation we will be creating a small form where users are given the ability to edit or delete the fields after adding(creating) an entry.

Overview

To continue with the tutorial you need to create and save the following files in the same directory.

  • Index.php
  • Crud_db.php
  • I_u_document.php
  • Style.css

Database Schema

Let us being the coding part with creating a database to store the user information. We will be using MySQL for the purpose. I have named my database “dbtuts” You can name yours anything relevant or use same as mine.
After creating the database, you need to add a table to the database. I have named mine “users” as I will be saving user information.
You can use following codes to create a database and add tables to it.

 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;

Index.php

 

In this file, we will be creating a small form using PHP codes, which shows all the data in the database by reading them during page load.

When the user loads this page it issues a query to database to get all the data in it. Then using a while loop we iterate over the table and each entry is shown in an individual table row. Then each entry is assigned with two buttons in front of it to delete or edit the record.

The file displays “Insert Entry” to make a fresh data entry to the database.
Basically, all the CRUD operations functions of Create, Read, Update and Delete can be performed from the page form.

<?php
include_once 'crud_db.php';
$con = new connect();

if(isset($_GET['delete_id']))
{
$con->deleteEntry("DELETE FROM users WHERE user_id=".$_GET['delete_id']);
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>Tutorial-20</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='i_u_document.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="body">
<div id="content">
<table align="center">
<tr>
<th colspan="5"><a href="i_u_document.php">Insert Entry</a></th>
</tr>
<th>First Name</th>
<th>Last Name</th>
<th>City Name</th>
<th colspan="2">Operations</th>
</tr>
<?php
$res=$con->getEntry("SELECT * FROM users");
if(mysql_num_rows($res)==0)
{
?>
<tr>
<td colspan="5">No Entries Found</td>
</tr>
<?php
}
else
{
while($row=mysql_fetch_array($res))
{
?>
<tr>
<td><?php echo $row['first_name']; ?></td>
<td><?php echo $row['last_name']; ?></td>
<td><?php echo $row['user_city']; ?></td>
<td align="center"><button type="button" onclick="edt_id(<?php echo $row['user_id']; ?>)">Edit</button> </td>
<td align="center"><button type="button" onclick="delete_id(<?php echo $row['user_id']; ?>)">Delete</button></td>
</tr>
<?php
}
}
?>
</table>
</div>
</div>

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

Crud_db.php

This file contains the database configuration which is needed to establish a successful connection with the MySQL server to exchange information between form and the database. While performing various SQL queries.

<?php
class connect
{
public function connect()
{
mysql_connect("localhost","root");
mysql_select_db("dbtuts");
}
public function setEntry($sql)
{
mysql_query($sql);
}
public function getEntry($sql)
{
return mysql_query($sql);
}
public function deleteEntry($sql)
{
mysql_query($sql);
}
}
?>

I_u_document.php

This page is responsible for adding a new entry as well as whenever the user wants to edit and insert the data which is already in the database.
In order to edit an entry, as soon as the user clicks the edit button in the index page, it gets the ID of the entry and used to issue an SQL query to read the relevant Row and return all its data. Then those data is shown in the form in order to the user to edit it. After the user clicks on save button it will re-issue an Update Query to save all the updated data.

<?php
include_once 'crud_db.php';
$con = new connect();

// data insert code starts here.
if(isset($_POST['btn-save']))
{
$first_name = $_POST['first_name'];
$last_name = $_POST['last_name'];
$city = $_POST['city_name'];
$con->setEntry("INSERT INTO users(first_name,last_name,user_city) VALUES('$first_name','$last_name','$city')");
header("Location: index.php");
}

if(isset($_GET['edit_id']))
{
$res=$con->getEntry("SELECT * FROM users WHERE user_id=".$_GET['edit_id']);
$row=mysql_fetch_array($res);
}

if(isset($_POST['btn-update']))
{
$con->setEntry("UPDATE users SET first_name='".$_POST['first_name']."',
last_name='".$_POST['last_name']."',
user_city='".$_POST['city_name']."'
WHERE user_id=".$_GET['edit_id']);
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>Tutorial-20</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 align="center"><a href="index.php">back to main page</a></td>
</tr>
<tr>
<td><input type="text" name="first_name" placeholder="First Name" value="<?php if(isset($row))echo $row['first_name']; ?>" required /></td>
</tr>
<tr>
<td><input type="text" name="last_name" placeholder="Last Name" value="<?php if(isset($row))echo $row['last_name']; ?>" required /></td>
</tr>
<tr>
<td><input type="text" name="city_name" placeholder="City" value="<?php if(isset($row))echo $row['user_city']; ?>" required /></td>
</tr>
<tr>
<td>
<?php
if(isset($_GET['edit_id']))
{
?><button type="submit" name="btn-update"><strong>UPDATE</strong></button></td><?php
}
else
{
?><button type="submit" name="btn-save"><strong>SAVE</strong></button></td><?php
}
?>
</tr>
</table>
</form>
</div>
</div>

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

Style.css

The codes are essential to add some styling to the files. It provides an attractive and user-friendly layout to the forms. You can add additional styling to the redesign it.

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

*
{
margin:0;
padding:0;
}
body
{
background:#fff;
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;
}

Bonus

You can download the Full source code here.

If you face any challenge during the process feel free to comment or direct a mail I would happy to help you.
If you find this post helpful don’t forget to share it with your friends and community.

Download the complete source code.

Grab the the free working demo Intantly!

You have Successfully Suscribed!

One thought on “CRUD Operations Using PHP OOP and MySQL

Leave a Reply

Your email address will not be published. Required fields are marked *