Catch-up the I.T. Bus: 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;
}

Catch-up the I.T. Bus

Tuesday 9 June 2015

PHP Data Update and Delete Using OOP

Database Crediantials.

Data Update

Delete Data

No comments:

Post a Comment

About Me

Blog Archive