PHP CRUD Operations using PHP OOP and MYSQL

by Anuj Kumar

CRUD means Create, Read, Update and delete. In this tutorial, I will explain how to perform crud operation using PHP OOPs and MySQL.
Create a database oopscrud.

create database oopscrud;

1	create database oopscrud;

Now create an SQL table tblusers inside this database. Structure of tblusers table

CREATE TABLE IF NOT EXISTS `tblusers` (
  `id` int(11) NOT NULL,
  `FirstName` varchar(150) NOT NULL,
  `LastName` varchar(150) NOT NULL,
  `EmailId` varchar(120) NOT NULL,
  `ContactNumber` char(11) NOT NULL,
  `Address` varchar(255) NOT NULL,
  `PostingDate` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=latin1;

CREATE TABLE IF NOT EXISTS `tblusers` (

`id` int(11) NOT NULL,

`FirstName` varchar(150) NOT NULL,

`LastName` varchar(150) NOT NULL,

`EmailId` varchar(120) NOT NULL,

`ContactNumber` char(11) NOT NULL,

`Address` varchar(255) NOT NULL,

`PostingDate` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP

) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=latin1;

function.php is the main file which will handle the database connection, insert, read, update and delete.

<?php
session_start();
define('DB_SERVER','localhost');
define('DB_USER','root');
define('DB_PASS' ,'');
define('DB_NAME', 'oopscrud');
class DB_con
{
function __construct()
{
$con = mysqli_connect(DB_SERVER,DB_USER,DB_PASS,DB_NAME);
$this->dbh=$con;
// Check connection
if (mysqli_connect_errno())
{
echo "Failed to connect to MySQL: " . mysqli_connect_error();
 }
}
//Data Insertion Function
	public function insert($fname,$lname,$emailid,$contactno,$address)
	{
	$ret=mysqli_query($this->dbh,"insert into tblusers(FirstName,LastName,EmailId,ContactNumber,Address) values('$fname','$lname','$emailid','$contactno','$address')");
	return $ret;
	}
//Data read Function
public function fetchdata()
	{
	$result=mysqli_query($this->dbh,"select * from tblusers");
	return $result;
	}
//Data one record read Function
public function fetchonerecord($userid)
	{
	$oneresult=mysqli_query($this->dbh,"select * from tblusers where id=$userid");
	return $oneresult;
	}
//Data updation Function
public function update($fname,$lname,$emailid,$contactno,$address,$userid)
	{
	$updaterecord=mysqli_query($this->dbh,"update  tblusers set FirstName='$fname',LastName='$lname',EmailId='$emailid',ContactNumber='$contactno',Address='$address' where id='$userid' ");
	return $updaterecord;
	}
//Data Deletion function Function
public function delete($rid)
	{
	$deleterecord=mysqli_query($this->dbh,"delete from tblusers where id=$rid");
	return $deleterecord;
	}
}
?>

<?php

session_start();

define('DB_SERVER','localhost');

define('DB_USER','root');

define('DB_PASS' ,'');

define('DB_NAME', 'oopscrud');

class DB_con

{

function __construct()

{

$con = mysqli_connect(DB_SERVER,DB_USER,DB_PASS,DB_NAME);

$this->dbh=$con;

// Check connection

if (mysqli_connect_errno())

{

echo "Failed to connect to MySQL: " . mysqli_connect_error();

}

//Data Insertion Function

public function insert($fname,$lname,$emailid,$contactno,$address)

{

$ret=mysqli_query($this->dbh,"insert into tblusers(FirstName,LastName,EmailId,ContactNumber,Address) values('$fname','$lname','$emailid','$contactno','$address')");

return $ret;

}

//Data read Function

public function fetchdata()

{

$result=mysqli_query($this->dbh,"select * from tblusers");

return $result;

}

//Data one record read Function

public function fetchonerecord($userid)

{

$oneresult=mysqli_query($this->dbh,"select * from tblusers where id=$userid");

return $oneresult;

}

//Data updation Function

public function update($fname,$lname,$emailid,$contactno,$address,$userid)

{

$updaterecord=mysqli_query($this->dbh,"update tblusers set FirstName='$fname',LastName='$lname',EmailId='$emailid',ContactNumber='$contactno',Address='$address' where id='$userid' ");

return $updaterecord;

}

//Data Deletion function Function

public function delete($rid)

{

$deleterecord=mysqli_query($this->dbh,"delete from tblusers where id=$rid");

return $deleterecord;

}

DB_con is the constructor function which will create localhost connection and database selection.
public function insert() function have some parameter which will accepts inputs from html form.
public function fetchdata() function read all the data from tblusers table.
public function fetchonerecord() function have one parameter userid. On the basis of the userid this function will read the data.
public function update() function have some parameter with user id which will accepts inputs from html form to update the record.
public function delete() function have one parameter rid(row id). On the basis of the rid(row id) this function will delete the data.

Create: Data Insertion (insert.php)

Create a html form

<form name="insertrecord" method="post">
<div class="row">
<div class="col-md-4"><b>First Name</b>
<input type="text" name="firstname" class="form-control" required>
</div>
<div class="col-md-4"><b>Last Name</b>
<input type="text" name="lastname" class="form-control" required>
</div>
</div>
<div class="row">
<div class="col-md-4"><b>Email id</b>
<input type="email" name="emailid" class="form-control" required>
</div>
<div class="col-md-4"><b>Contactno</b>
<input type="text" name="contactno" class="form-control" maxlength="10" required>
</div>
</div>
<div class="row">
<div class="col-md-8"><b>Address</b>
<textarea class="form-control" name="address" required></textarea>
</div>
</div>
<div class="row" style="margin-top:1%">
<div class="col-md-8">
<input type="submit" name="insert" value="Submit">
</div>
</div>
</form>

<div class="col-md-4"><b>First Name</b>

</div>

</div>

<div class="col-md-4"><b>Email id</b>

</div>

<div class="col-md-4"><b>Contactno</b>

</div>

<div class="col-md-8"><b>Address</b>

</div>

</div>

</form>

Code for insert a record in database. Once the user filled all the data and click on the submit button then data will be saved in the database using the below code:

<?php
// include database connection file
require_once'function.php';
// Object creation
$insertdata=new DB_con();
if(isset($_POST['insert']))
{
// Posted Values
$fname=$_POST['firstname'];
$lname=$_POST['lastname'];
$emailid=$_POST['emailid'];
$contactno=$_POST['contactno'];
$address=$_POST['address'];
//Function Calling
$sql=$insertdata->insert($fname,$lname,$emailid,$contactno,$address);
if($sql)
{
// Message for successfull insertion
echo "<script>alert('Record inserted successfully');</script>";
echo "<script>window.location.href='index.php'</script>";
}
else
{
// Message for unsuccessfull insertion
echo "<script>alert('Something went wrong. Please try again');</script>";
echo "<script>window.location.href='index.php'</script>";
}
}
?>

<?php

// include database connection file

require_once'function.php';

// Object creation

$insertdata=new DB_con();

if(isset($_POST['insert']))

{

// Posted Values

$fname=$_POST['firstname'];

$lname=$_POST['lastname'];

$emailid=$_POST['emailid'];

$contactno=$_POST['contactno'];

$address=$_POST['address'];

//Function Calling

$sql=$insertdata->insert($fname,$lname,$emailid,$contactno,$address);

if($sql)

{

// Message for successfull insertion

echo "<script>alert('Record inserted successfully');</script>";

echo "<script>window.location.href='index.php'</script>";

}

else

{

// Message for unsuccessfull insertion

echo "<script>alert('Something went wrong. Please try again');</script>";

echo "<script>window.location.href='index.php'</script>";

}

Read : Fetch the Data(index.php)

<table id="mytable" class="table table-bordred table-striped">
<thead>
<th>#</th>
<th>First Name</th>
<th>Last Name</th>
<th>Email</th>
<th>Contact</th>
<th>Address</th>
<th>Posting Date</th>
<th>Edit</th>
<th>Delete</th>
</thead>
<tbody>
 <?php
 $fetchdata=new DB_con();
  $sql=$fetchdata->fetchdata();
  $cnt=1;
  while($row=mysqli_fetch_array($sql))
  {
  ?>
    <tr>
    <td><?php echo htmlentities($cnt);?></td>
    <td><?php echo htmlentities($row['FirstName']);?></td>
    <td><?php echo htmlentities($row['LastName']);?></td>
    <td><?php echo htmlentities($row['EmailId']);?></td>
    <td><?php echo htmlentities($row['ContactNumber']);?></td>
    <td><?php echo htmlentities($row['Address']);?></td>
    <td><?php echo htmlentities($row['PostingDate']);?></td>
 <td><a href="update.php?id=<?php echo htmlentities($row['id']);?>"><button class="btn btn-primary btn-xs"><span class="glyphicon glyphicon-pencil"></span></button></a></td>
 <td><a href="index.php?del=<?php echo htmlentities($row['id']);?>"><button class="btn btn-danger btn-xs" onClick="return confirm('Do you really want to delete');"><span class="glyphicon glyphicon-trash"></span></button></a></td>
    </tr>
<?php
// for serial number increment
$cnt++;
} ?>
</tbody>
</table>

<thead>

<th>First Name</th>

<th>Email</th>

<th>Contact</th>

<th>Address</th>

<th>Posting Date</th>

<th>Delete</th>

</thead>

<tbody>

<?php

$fetchdata=new DB_con();

$sql=$fetchdata->fetchdata();

$cnt=1;

while($row=mysqli_fetch_array($sql))

{

<tr>

</tr>

<?php

// for serial number increment

$cnt++;

} ?>

</tbody>

</table>

UPDATE : Data Updation(unpdate.php )
Get the data in HTML form .
Code for get a record based on the given id. Through this way we can get data autofill-data in HTML Form.

<?php
// Get the userid
$userid=intval($_GET['id']);
$onerecord=new DB_con();
$sql=$onerecord->fetchonerecord($userid);
$cnt=1;
while($row=mysqli_fetch_array($sql))
  {
  ?>
<form name="insertrecord" method="post">
<div class="row">
<div class="col-md-4"><b>First Name</b>
<input type="text" name="firstname" value="<?php echo htmlentities($row['FirstName']);?>" class="form-control" required>
</div>
<div class="col-md-4"><b>Last Name</b>
<input type="text" name="lastname" value="<?php echo htmlentities($row['LastName']);?>" class="form-control" required>
</div>
</div>
<div class="row">
<div class="col-md-4"><b>Email id</b>
<input type="email" name="emailid" value="<?php echo htmlentities($row['EmailId']);?>" class="form-control" required>
</div>
<div class="col-md-4"><b>Contactno</b>
<input type="text" name="contactno" value="<?php echo htmlentities($row['ContactNumber']);?>" class="form-control" maxlength="10" required>
</div>
</div>
<div class="row">
<div class="col-md-8"><b>Address</b>
<textarea class="form-control" name="address" required><?php echo htmlentities($row['Address']);?></textarea>
</div>
</div>
<?php } ?>
<div class="row" style="margin-top:1%">
<div class="col-md-8">
<input type="submit" name="update" value="Update">
</div>
</div>
     </form>

<?php

// Get the userid

$userid=intval($_GET['id']);

$onerecord=new DB_con();

$sql=$onerecord->fetchonerecord($userid);

$cnt=1;

while($row=mysqli_fetch_array($sql))

{

<div class="col-md-4"><b>First Name</b>

<input type="text" name="firstname" value="<?php echo htmlentities($row['FirstName']);?>" class="form-control" required>

</div>

<input type="text" name="lastname" value="<?php echo htmlentities($row['LastName']);?>" class="form-control" required>

</div>

<div class="col-md-4"><b>Email id</b>

<input type="email" name="emailid" value="<?php echo htmlentities($row['EmailId']);?>" class="form-control" required>

</div>

<div class="col-md-4"><b>Contactno</b>

<input type="text" name="contactno" value="<?php echo htmlentities($row['ContactNumber']);?>" class="form-control" maxlength="10" required>

</div>

<div class="col-md-8"><b>Address</b>

</div>

<?php } ?>

</div>

</form>

Code for update the record

<?php
// include database connection file
include_once("function.php");
//Object
$updatedata=new DB_con();
if(isset($_POST['update']))
{
// Get the userid
$userid=intval($_GET['id']);
// Posted Values
$fname=$_POST['firstname'];
$lname=$_POST['lastname'];
$emailid=$_POST['emailid'];
$contactno=$_POST['contactno'];
$address=$_POST['address'];
//Function Calling
$sql=$updatedata->update($fname,$lname,$emailid,$contactno,$address,$userid);
// Mesage after updation
echo "<script>alert('Record Updated successfully');</script>";
// Code for redirection
echo "<script>window.location.href='index.php'</script>";
}
?>

<?php

// include database connection file

include_once("function.php");

//Object

$updatedata=new DB_con();

if(isset($_POST['update']))

{

// Get the userid

$userid=intval($_GET['id']);

// Posted Values

$fname=$_POST['firstname'];

$lname=$_POST['lastname'];

$emailid=$_POST['emailid'];

$contactno=$_POST['contactno'];

$address=$_POST['address'];

//Function Calling

$sql=$updatedata->update($fname,$lname,$emailid,$contactno,$address,$userid);

// Mesage after updation

echo "<script>alert('Record Updated successfully');</script>";

// Code for redirection

echo "<script>window.location.href='index.php'</script>";

}

DELETE : Record Deletion

Delete a record from the database

<?php //Deletion
if(isset($_GET['del']))
    {
// Geeting deletion row id
$rid=$_GET['del'];
$deletedata=new DB_con();
$sql=$deletedata->delete($rid);
if($sql)
{
// Message for successfull insertion
echo "<script>alert('Record deleted successfully');</script>";
echo "<script>window.location.href='index.php'</script>";
}
    }
?>

<?php //Deletion

if(isset($_GET['del']))

{

// Geeting deletion row id

$rid=$_GET['del'];

$deletedata=new DB_con();

$sql=$deletedata->delete($rid);

if($sql)

{

// Message for successfull insertion

echo "<script>alert('Record deleted successfully');</script>";

echo "<script>window.location.href='index.php'</script>";

}

CRUD Script using PHP OOP and MYSQL

Size: 5.46 KB

Version: V 1.0

Download Now!

PHP CRUD Operations using PHP OOP and MYSQL

Recommended Tutorials for you

You may also like...

PHP OOP Examples

PHP CRUD Operations using PHP OOP and MYSQL

Create: Data Insertion (insert.php)

Read : Fetch the Data(index.php)

Code for update the record

DELETE : Record Deletion

Recommended Tutorials for you

You may also like...

How to Fetch data from database Using OOPS In PHP

Static methods and properties

Interfaces – the next level of abstraction

PHP OOP Examples