PHP Data Update and Delete using OOP

codernew
Hi friends now let’s continue with this tutorial which is continuation of ours previous tutorial Data Insert and Select using OOPs, So In this tutorial we will see how to use OOP to update and delete data of MySQL which is simple too like previous, it’s so easy to learn, have a look.

PHP Data Update and Delete using OOP

Database Crediantials.

Here i am going to use Database Crediantials which was used in my previous tutorial.
Database name : dbtuts
Table name : users
Table Columns : user_id , first_name , last_name , user_city .


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;

Now all we need to create following pages.
index.php
dbMySql.php
edit_data.php
delete_data.php

index.php i used here to display data and how to display i showed that in my previous tutorial.
Now we need to create dbMySql.php file which contains host connection , database selection and functions which are used to Update and Delete data from MySql users table.
dbMySql.php


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

class DB_con
{
 function __construct()
 {
  $conn = mysql_connect(DB_SERVER,DB_USER,DB_PASS) or die('localhost connection problem'.mysql_error());
  mysql_select_db(DB_NAME, $conn);
 }
 
 public function select()
 {
  $res=mysql_query("SELECT * FROM users");
  return $res;
 }
 
 public function delete($table,$id)
 {
  $res = mysql_query("DELETE FROM $table WHERE user_id=".$id);
  return $res;
 }
 
 public function update($table,$id,$fname,$lname,$city)
 {
  $res = mysql_query("UPDATE $table SET first_name='$fname', last_name='$lname', user_city='$city' WHERE user_id=".$id);
  return $res;
 }
}

?>

Script Explained:
__constructor() : this function connects the localhost server and database.
select() : Select data from users table.
delete() : function with $table and $id which is for MySql Delete Query.
update() : this function have four parameters with table name and table fields value.

Data Update

After data showing on the page create a file called edit_data.php and add the following code just above the html edit data form.
edit_data.php


include_once 'dbMySql.php';
$con = new DB_con();
$table = "users";

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

// and update condition would be as follow ...

if(isset($_POST['btn-update']))
{
 $fname = $_POST['first_name'];
 $lname = $_POST['last_name'];
 $city = $_POST['city_name'];
 
 $id=$_GET['edit_id'];
 $res=$con->update($table,$id,$fname,$lname,$city);
}

Delete Data

Now create a new file called delete_data.php and put the following code into this file like this :


<?php
include_once 'dbMySql.php';
$con = new DB_con();
$table = "users";
if(isset($_GET['delete_id']))
{
 $id=$_GET['delete_id'];
 $res=$con->delete($table,$id);
}
?>

that’s it

Complete Script :

dbMySql.php 


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

class DB_con
{
 function __construct()
 {
  $conn = mysql_connect(DB_SERVER,DB_USER,DB_PASS) or die('localhost connection problem'.mysql_error());
  mysql_select_db(DB_NAME, $conn);
 }
 
 public function select()
 {
  $res=mysql_query("SELECT * FROM users");
  return $res;
 }
 
 public function delete($table,$id)
 {
  $res = mysql_query("DELETE FROM $table WHERE user_id=".$id);
  return $res;
 }
 
 public function update($table,$id,$fname,$lname,$city)
 {
  $res = mysql_query("UPDATE $table SET first_name='$fname', last_name='$lname', user_city='$city' WHERE user_id=".$id);
  return $res;
 }
}

?>

index.php
contains html , php script to display data on this page with some javascript that confirms any operation to be perform or not.


<?php
include_once 'dbMySql.php';
$con = new DB_con();
$res=$con->select();
?>
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>PHP Data Update and Delete Using OOP - By Cleartuts</title>
<link rel="stylesheet" href="style.css" type="text/css" />
<script type="text/javascript">
function del_id(id)
{
 if(confirm('Sure to delete this record ?'))
 {
  window.location='delete_data.php?delete_id='+id
 }
}
function edit_id(id)
{
 if(confirm('Sure to edit this record ?'))
 {
  window.location='edit_data.php?edit_id='+id
 }
}
</script>
</head>
<body>
<center>
<div id="header">
 <div id="content">
    <label>PHP Data Update and Delete Using OOP - By Cleartuts</label>
    </div>
</div>
<div id="body">
 <div id="content">
    <table align="center">
    <tr>
    <th>First Name</th>
    <th>Last Name</th>
    <th>City</th>
    <th colspan="2">edit/delete</th>
    </tr>
    <?php
 while($row=mysql_fetch_row($res))
 {
   ?>
            <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:edit_id(<?php echo $row[0]; ?>)"><img src="b_edit.png" alt="EDIT" /></a></td>
            <td align="center"><a href="javascript:del_id(<?php echo $row[0]; ?>)"><img src="b_drop.png" alt="DELETE" /></a></td>
            </tr>
            <?php
 }
 ?>
    </table>
    </div>
</div>

<div id="footer">
 <div id="content">
    <hr /><br/>
    <label>for more tutorials and blog tips visit : <a href="http://cleartuts.blogspot.com">cleartuts.com</a></label>
    </div>
</div>

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

edit_data.php
contains html and php script to update data with javascript for redirection to the main page and completion messege. 


<?php
include_once 'dbMySql.php';
$con = new DB_con();
$table = "users";
// data insert code starts here.
if(isset($_GET['edit_id']))
{
 $sql=mysql_query("SELECT * FROM users WHERE user_id=".$_GET['edit_id']);
 $result=mysql_fetch_array($sql);
}
// data update code starts here.
if(isset($_POST['btn-update']))
{
 $fname = $_POST['first_name'];
 $lname = $_POST['last_name'];
 $city = $_POST['city_name'];
 
 $id=$_GET['edit_id'];
 $res=$con->update($table,$id,$fname,$lname,$city);
 if($res)
 {
  ?>
  <script>
  alert('Record updated...');
        window.location='index.php'
        </script>
  <?php
 }
 else
 {
  ?>
  <script>
  alert('error updating record...');
        window.location='index.php'
        </script>
  <?php
 }
}
// data update code ends here.

?>
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>PHP Data Insert and Select Data Using OOP - By Cleartuts</title>
<link rel="stylesheet" href="style.css" type="text/css" />
</head>
<body>
<center>

<div id="header">
 <div id="content">
    <label>PHP Data Insert and Select Data Using OOP - 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 $result['first_name']; ?>"  /></td>
    </tr>
    <tr>
    <td><input type="text" name="last_name" placeholder="Last Name" value="<?php echo $result['last_name']; ?>" /></td>
    </tr>
    <tr>
    <td><input type="text" name="city_name" placeholder="City" value="<?php echo $result['user_city']; ?>" /></td>
    </tr>
    <tr>
    <td>
    <button type="submit" name="btn-update"><strong>UPDATE</strong></button></td>
    </tr>
    </table>
    </form>
    </div>
</div>

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

delete_data.php
contains php and javascript , PHP for data delete action and javascript for alert and redirection to the main page. 


<?php
include_once 'dbMySql.php';
$con = new DB_con();
$table = "users";
if(isset($_GET['delete_id']))
{
 $id=$_GET['delete_id'];
 $res=$con->delete($table,$id);
 if($res)
 {
  ?>
  <script>
  alert('Record Deleted ...')
        window.location='index.php'
        </script>
  <?php
 }
 else
 {
  ?>
  <script>
  alert('Record cant Deleted !!!')
        window.location='index.php'
        </script>
  <?php
 }
}
?>

style.css
to make beautify all the pages.


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

*
{
 margin:0;
 padding:0;
}
#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:40%;
 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:100%;
}
table td button:active
{
 position:relative;
 top:1px;
}
#footer
{
 margin-top:50px;
 position:relative;
 bottom:30px;
 font-family:Verdana, Geneva, sans-serif;
}