Object Oriented CRUD Tutorial with PHP and MySQL

codernew
In my previuos tutorial CRUD Operations Using PHP Oops and MySql ,it was simple Object Oriented Concepts for beginners , A better programming must follow object oriented principals , and Now this tutorial goes advance and some different from previous one , this tutorial contains one crud file that handles such operations like create , read , update and delete , this part 2 tutorial also easy to learn , so let’s take a look.

PHP OOP CRUD Tutorial with MySQL part-2

this tutorial contains a folder called inc with PHP files like this :

index.php
add_records.php
edit_records.php
dbcrud.php
inc
— class.crud.php
— dbconfig.php

Database design and Table.

Database : dbtuts
Table : users
columns : user_id , first_name , last_name , user_city.

copy-paste the following sql query to your MySql Database.


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
this file handles database and server cofiguration and yes , with DB_con class with constructor that works for all files.


<?php
define('DB_SERVER','localhost');
define('DB_USER','root');
define('DB_PASSWORD','');
define('DB_NAME','dbtuts');

class DB_con
{
 function __construct()
 {
  $conn = mysql_connect(DB_SERVER,DB_USER,DB_PASSWORD) or die('error connecting to server'.mysql_error());
  mysql_select_db(DB_NAME, $conn) or die('error connecting to database->'.mysql_error());
 }
}
?>

class.crud.php
this file contains a main class called CRUD which have inner functions like create , read, update and delete, and this function name says what they do.


<?php
include_once 'dbconfig.php';

class CRUD
{
 public function __construct()
 {
  $db = new DB_con();
 }
 
 public function create($fname,$lname,$city)
 {
  mysql_query("INSERT INTO users(first_name,last_name,user_city) VALUES('$fname','$lname','$city')");
 }
 
 public function read()
 {
  return mysql_query("SELECT * FROM users");
 }
 
 public function delete($id)
 {
  mysql_query("DELETE FROM users WHERE user_id=".$id);
 }
 
 public function update($fname,$lname,$city,$id)
 {
  mysql_query("UPDATE users SET first_name='$fname', last_name='$lname', user_city='$city' WHERE user_id=".$id);
 }
}
?>

these are the most important files for handle crud operations and works silently for all the operations.

CREATE : data 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.
HTML From 


<form method="post">
<input type="text" name="fname" placeholder="first name" />
<input type="text" name="lname" placeholder="last name" />
<input type="text" name="city" placeholder="city" />
<button type="submit" name="save">save</button>
</form>

explained :
above html form contains all the fields the users table has.

Put the following script just above html form.


<?php
include_once 'inc/class.crud.php';
$crud = new CRUD();
if(isset($_POST['save']))
{
 $fname = $_POST['fname'];
 $lname = $_POST['lname'];
 $city = $_POST['city'];
 
 // insert
    $crud->create($fname,$lname,$city);
 // insert
}
?>

script explained :
including the class.crud.php we can use it’s create() function to insert data.

READ : select data

here how you can fetch all the data By including the class.crud.php file and usig $crud object with $crud->read() function. 


<?php
include_once 'inc/class.crud.php';
$crud = new CRUD();
$res = $crud->read();
while($row = mysql_fetch_array($res))
{
 echo $row['first_name'];
 echo $row['last_name'];
 echo $row['user_city'];
}
?>

UPDATE : update data

for update data we need to fetch data which are set by querystring as follow :


if(isset($_GET['edt_id']))
{
 $res=mysql_query("SELECT * FROM users WHERE user_id=".$_GET['edt_id']);
 $row=mysql_fetch_array($res);
}

after fetching data we can update data using following function. 


if(isset($_POST['update']))
{
 $id = $_GET['edt_id'];
 $fname = $_POST['fname'];
 $lname = $_POST['lname'];
 $city = $_POST['city'];
 
 $crud->update($fname,$lname,$city,$id);
}

DELETE : delete data


if(isset($_GET['del_id']))
{
 $id = $_GET['del_id'];
 $crud->delete($id);
}

here i shown you that how to use oops in different way to insert, select, update and delete data from mysql. hope it would be helpful to you… that’s it…

Complete Script

inc/dbconfig.php


<?php
define('DB_SERVER','localhost');
define('DB_USER','root');
define('DB_PASSWORD','');
define('DB_NAME','dbtuts');

class DB_con
{
 function __construct()
 {
  $conn = mysql_connect(DB_SERVER,DB_USER,DB_PASSWORD) or die('error connecting to server'.mysql_error());
  mysql_select_db(DB_NAME, $conn) or die('error connecting to database->'.mysql_error());
 }
}

?>

inc/class.crud.php


<?php
include_once 'dbconfig.php';

class CRUD
{
 public function __construct()
 {
  $db = new DB_con();
 }
 
 public function create($fname,$lname,$city)
 {
  mysql_query("INSERT INTO users(first_name,last_name,user_city) VALUES('$fname','$lname','$city')");
 }
 
 public function read()
 {
  return mysql_query("SELECT * FROM users");
 }
 
 public function delete($id)
 {
  mysql_query("DELETE FROM users WHERE user_id=".$id);
 }
 
 public function update($fname,$lname,$city,$id)
 {
  mysql_query("UPDATE users SET first_name='$fname', last_name='$lname', user_city='$city' WHERE user_id=".$id);
 }
}
?>

index.php


<?php
include_once 'inc/class.crud.php';
$crud = new CRUD();
?>
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<link rel="stylesheet" href="style.css" type="text/css" />
<title>php oops crud tutorial part-2 by cleartuts</title>
</head>
<body>

<div id="header">
<label>php oops crud tutorial part-2 by cleartuts</label>
</div>

<center>
<table id="dataview">
<tr>
<td colspan="5"><a href="add_records.php">add new</a></td>
</tr>
<?php
$res = $crud->read();
if(mysql_num_rows($res)>0)
{
 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><a href="edit_records.php?edt_id=<?php echo $row['user_id']; ?>">edit</a></td>
    <td><a href="dbcrud.php?del_id=<?php echo $row['user_id']; ?>">delete</a></td>
    </tr>
    <?php
 } 
}
else
{
 ?><tr><td colspan="5">Nothing here... add some new</td></tr><?php
}
?>
</table>

<footer>
<label>Visit <a href="http://cleartuts.blogspot.com/">cleartuts</a> for more tutorials...</label>
</footer>

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

add_records.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>php oops crud tutorial part-2 by cleartuts</title>
<link rel="stylesheet" href="style.css" type="text/css" />
</head>
<body>

<div id="header">
<label>php oops crud tutorial part-2 by cleartuts</label>
</div>

<center>
<form method="post" action="dbcrud.php">
<table id="dataview">
<tr>
<td><input type="text" name="fname" placeholder="first name" /></td>
</tr>
<tr>
<td><input type="text" name="lname" placeholder="last name" /></td>
</tr>
<tr>
<td><input type="text" name="city" placeholder="city" /></td>
</tr>
<tr>
<td><button type="submit" name="save">save</button></td>
</tr>
</table>
</form>
</center>
</body>
</html>

edit_records.php


<?php
include_once 'inc/class.crud.php';
$crud = new CRUD();
if(isset($_GET['edt_id']))
{
 $res=mysql_query("SELECT * FROM users WHERE user_id=".$_GET['edt_id']);
 $row=mysql_fetch_array($res);
}
?>
<!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>php oops crud tutorial part-2 by cleartuts</title>
<link rel="stylesheet" href="style.css" type="text/css" />
</head>
<body>

<div id="header">
<label>php oops crud tutorial part-2 by cleartuts</label>
</div>

<center>
<form method="post" action="dbcrud.php?edt_id=<?php echo $_GET['edt_id'] ?>">
<table id="dataview">
<tr><td><input type="text" name="fname" placeholder="first name" value="<?php echo $row['first_name'] ?>" /><br /></td></tr>
<tr><td><input type="text" name="lname" placeholder="last name" value="<?php echo $row['last_name'] ?>" /></td></tr>
<tr><td><input type="text" name="city" placeholder="city" value="<?php echo $row['user_city'] ?>" /></td></tr>
<tr><td><button type="submit" name="update">update</button></td></tr>
</table>
</form>
</table>
</center>
</body>
</html>

dbcrud.php


<?php
include_once 'inc/class.crud.php';
$crud = new CRUD();
if(isset($_POST['save']))
{
 $fname = $_POST['fname'];
 $lname = $_POST['lname'];
 $city = $_POST['city'];
 
 // insert
    $crud->create($fname,$lname,$city);
 // insert
 header("Location: index.php");
}

if(isset($_GET['del_id']))
{
 $id = $_GET['del_id'];
 $crud->delete($id);
 header("Location: index.php");
}

if(isset($_POST['update']))
{
 $id = $_GET['edt_id'];
 $fname = $_POST['fname'];
 $lname = $_POST['lname'];
 $city = $_POST['city'];
 
 $crud->update($fname,$lname,$city,$id);
 header("Location: index.php");
}
?>

style.css


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

* { margin:0; padding:0; } 

#header
{
 text-align:center;
 width:100%;
 height:50px;
 background:#00a2d1;
 color:#f9f9f9;
 font-weight:bolder;
 font-family:Verdana, Geneva, sans-serif;
 font-size:35px;
}

table,td
{
 width:40%;
 padding:15px;
 border:solid #e1e1e1 1px;
 font-family:Verdana, Geneva, sans-serif;
 border-collapse:collapse;
}
#dataview
{
 margin-top:100px;
 position:relative;
 bottom:50px;
}
#dataview input
{
 width:100%;
 height:40px;
 border:0; outline:0;
 font-family:Verdana, Geneva, sans-serif;
 padding-left:10px;
}
#dataview button
{
 width:200px;
 height:40px;
 border:0; outline:0;
 font-family:Verdana, Geneva, sans-serif;
 padding-left:10px;
}


footer { margin-top:50px; position:relative; bottom:50px; font-family:Verdana, Geneva, sans-serif; }