CRUD Operations Using PHP and MySQL with OOP Concepts , Object Oriented PHP is More efficient than simple and core , its being used in many more MVC(model , view , controller) pattern based PHP frameworks Because we can create one class for all such operations and it’s provide reusability of any class and created function , So have a look .
Database and Table which are used in this tutorial.
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;
dbcrud.php this is the main PHP file which handles database connection data insert , select , update and delete by creating such functions given in the following php file.
dbcrud.php
<?php
class connect
{
public function connect()
{
mysql_connect("localhost","root");
mysql_select_db("dbtuts");
}
public function setdata($sql)
{
mysql_query($sql);
}
public function getdata($sql)
{
return mysql_query($sql);
}
public function delete($sql)
{
mysql_query($sql);
}
}
?>
functions which are used in above php file.
1. connect() : used for database connection and selection.
2. setdata() : used for data insert and data update.
3. getdata() : used for data select from mysql.
4. delete() : used for data delete from mysql.
CREATE : insert
To insert Data into mysql table we need to create html form containing all the fields the users table has. and HTML code of insert form will be as follows.
<form method="post">
<table align="center">
<tr>
<td><input type="text" name="first_name" placeholder="First Name" value="" required /></td>
</tr>
<tr>
<td><input type="text" name="last_name" placeholder="Last Name" value="" required /></td>
</tr>
<tr>
<td><input type="text" name="city_name" placeholder="City" value="" required /></td>
</tr>
<tr>
<td>
<button type="submit" name="btn-save"><strong>SAVE</strong></button></td> </tr>
</table>
</form>Using above form we can insert data with php oops as follow :
<?php
include_once 'dbcrud.php';
$con = new connect();
if(isset($_POST['btn-save']))
{
$first_name = $_POST['first_name'];
$last_name = $_POST['last_name'];
$city = $_POST['city_name'];
$con->setdata("INSERT INTO users(first_name,last_name,user_city) VALUES('$first_name','$last_name','$city')");
}
?>READ : select
By using getdata() function we can fetch all data from table because it have return type.
<?php
$res=$con->getdata("SELECT * FROM users");
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>
</tr>
<?php
}
}
?>UPDATE : update data
for update data we need to fetch data which are set by querystring as follow :
$res=$con->getdata("SELECT * FROM users WHERE user_id=".$_GET['edit_id']);
$row=mysql_fetch_array($res);after fetching data we can update data using following function.
$con->setdata("UPDATE users SET first_name='".$_POST['first_name']."',
last_name='".$_POST['last_name']."',
user_city='".$_POST['city_name']."'
WHERE user_id=".$_GET['edit_id']);DELETE : delete data
following function is used to delete data.
$con->delete("DELETE FROM users WHERE user_id=".$_GET['delete_id']);
here i shown you that how to use oops to insert , select , update and delete data from mysql.
that’s it
complete oops crud script.
index.php
<?php
include_once 'dbcrud.php';
$con = new connect();
// delete condition
if(isset($_GET['delete_id']))
{
$con->delete("DELETE FROM users WHERE user_id=".$_GET['delete_id']);
header("Location: index.php");
}
// 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 Using PHP Oops - 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='insert-update.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 Using PHP Oops - <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="insert-update.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
$res=$con->getdata("SELECT * FROM users");
if(mysql_num_rows($res)==0)
{
?>
<tr>
<td colspan="5">Nothing Found Here !</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"><a href="javascript:edt_id('<?php echo $row['user_id']; ?>')"><img src="b_edit.png" alt="EDIT" /></a></td>
<td align="center"><a href="javascript:delete_id('<?php echo $row['user_id']; ?>')"><img src="b_drop.png" alt="DELETE" /></a></td>
</tr>
<?php
}
}
?>
</table>
</div>
</div>
</center>
</body>
</html>insert-update.php
<?php
include_once 'dbcrud.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->setdata("INSERT INTO users(first_name,last_name,user_city) VALUES('$first_name','$last_name','$city')");
header("Location: index.php");
}
// data insert code ends here.
// code for fetch user data via QueryString URL
if(isset($_GET['edit_id']))
{
$res=$con->getdata("SELECT * FROM users WHERE user_id=".$_GET['edit_id']);
$row=mysql_fetch_array($res);
}
// code for fetch user data via QueryString URL
// data update condition
if(isset($_POST['btn-update']))
{
$con->setdata("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");
}
// data update 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 Using 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 Using 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" 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>dbcrud.php
<?php
class connect
{
public function connect()
{
mysql_connect("localhost","root");
mysql_select_db("dbtuts");
}
public function setdata($sql)
{
mysql_query($sql);
}
public function getdata($sql)
{
return mysql_query($sql);
}
public function delete($sql)
{
mysql_query($sql);
}
}
?>style.css
@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;
}This is it. We have created a simple crud module capable to perform CRUD ( Create, Read, Update, Delete) operations using PHP Oops with MySql database.