11 December 2014

Basic insert, view, edit, delete and update using PHP and Mysql

Hi readers, today i going to post one of old topic in php and mysql, here i'm going to post Insert data through form, and fetch / view the data from database, and also Edit, Delete and Update with detailed explanation. i know mysql is depreciated, even most of the people are using mysql so now i'm going to use mysql and i'll update with mysqli and pdo later, now i'm going to explain the basics of php mysql functions, how to insert, fetch, delete, update like all operations. New with Codeigniter insert edit view update delete

DOWNLOAD LIVE DEMO

FILES

db.php
index.php
insert.php
view.php
edit.php

These are the files needed to execute the basic insert fetch edit delete operation, let see what the file contains. i already posted how to create database and insert code and AJAX insert without refresh the page.

DB.PHP
first we have to connect the db file.

<?php
ob_start();
define('DB_SERVER', 'localhost');
define('DB_USERNAME', 'root');
define('DB_PASSWORD', '');
define('DB_DATABASE', '2my4edge');
$connection = mysql_connect(DB_SERVER, DB_USERNAME, DB_PASSWORD) or die(mysql_error());
$database = mysql_select_db(DB_DATABASE) or die(mysql_error());
?>

i believe you can get clear idea from the color variation, RED all database fields, and the other all separated one with other same.

DATABASE NAME --> 2my4edge
TABLE NAME --> sample
TABLE FIELDS --> id(int, primary key, auto increment), username(varchar), emailid(varchar), mobileno(varchar), created(timestamp)

INDEX.PHP

<!DOCTYPE>
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>Insert form</title>
<link type="text/css" media="all" rel="stylesheet" href="style.css">
</head>

<body>
<div class="display">
<form action="insert.php" method="post" name="insertform">
<p>
  <label for="name" id="preinput"> USER NAME : </label>
  <input type="text" name="username" required placeholder="Enter your name" id="inputid"/>
</p>
<p>
  <label  for="email" id="preinput"> EMAIL ID : </label>
  <input type="email" name="usermail" required placeholder="Enter your Email" id="inputid" />
</p>
<p>
  <label for="mobile" id="preinput"> MOBILE NUMBER : </label>
  <input type="text" name="usermobile" required placeholder="Enter your mobile number"

                                                           id="inputid" />
</p>
<p>
  <input type="submit" name="send" value="Submit" id="inputid1"  />
</p>
</form>
</div>
<?php include('view.php'); ?>
</body>
</html>

here i'm just viewing the view.php file below the form but you can make that separate page for that. the action will be on insert.php page. method is post.

INSERT.PHP
the index.php page form is run in the insert.php.

<?php
 ob_start();
  include("db.php");
  if(isset($_POST['send'])!="")
  {
  $username=mysql_real_escape_string($_POST['username']);
  $usermail=mysql_real_escape_string($_POST['usermail']);
  $usermobile=mysql_real_escape_string($_POST['usermobile']);
  $update=mysql_query("INSERT INTO sample(username,emailid,mobileno,created)VALUES
                                      ('$username','$usermail','$usermobile',now())");
  
  if($update)
  {
      $msg="Successfully Updated!!";
      echo "<script type='text/javascript'>alert('$msg');</script>";
      header('Location:index.php');
  }
  else
  {
     $errormsg="Something went wrong, Try again";
      echo "<script type='text/javascript'>alert('$errormsg');</script>";
  }
  }
 ob_end_flush();
?>

if the data is successfully registered, the header location will be to index.php

VIEW.PHP

<?php
 include('db.php');
  $select=mysql_query("SELECT * FROM sample order by id desc");
  $i=1;
  while($userrow=mysql_fetch_array($select))
  {
  $id=$userrow['id'];
  $username=$userrow['username'];
  $usermail=$userrow['emailid'];
  $usermobile=$userrow['mobileno'];
  $created=$userrow['created']
?>

<div class="display">
  <p> USER NAME : <span><?php echo $username; ?></span>
    <a href="delete.php?id=<?php echo $id; ?>" 
    onclick="return confirm('Are you sure you wish to delete this Record?');">
            <span class="delete" title="Delete"> X </span></a>
  </p>
  <br />
  <p> EMAIL ID : <span><?php echo $usermail; ?></span>
    <a href="edit.php?id=<?php echo $id; ?>"><span class="edit" title="Edit"> E </span></a>
  </p>
  <br />
  <p> MOBILE NO : <span><?php echo $usermobile; ?></span>
  </p>
  <br />
  <p> CREATED ON : <span><?php echo $created; ?></span>
  </p>
  <br />
</div>
<?php } ?>

View page contains edit and delete link in the while loop, here we are passing the values though id for edit and delete, like

<a href="delete.php?id=<?php echo $id; ?>" 
    onclick="return confirm('Are you sure you wish to delete this Record?');"> X </a>

<a href="edit.php?id=<?php echo $id; ?>"> E </a>

we have to give this link before the while loop ends,

DELETE.PHP

<?php
  ob_start();
  include("db.php");
  if(isset($_GET['id'])!="")
  {
  $delete=$_GET['id'];
  $delete=mysql_query("DELETE FROM sample WHERE id='$delete'");
  if($delete)
  {
      header("Location:index.php");
  }
  else
  {
      echo mysql_error();
  }
  }
  ob_end_flush();
?>

the delete.php is deleting the data what are the data is there in the ID. if successfully deleted the page will goes to index.php page.

EDIT.PHP
this edit page contains both edit form and also update code.

<?php 
ob_start();
include('db.php');
if(isset($_GET['id']))
{
  $id=$_GET['id'];
  if(isset($_POST['update']))
  {
  $eusername=$_POST['eusername'];
  $eusermail=$_POST['eusermail'];
  $emobile=$_POST['eusermobile'];
  $updated=mysql_query("UPDATE sample SET 
        username='$eusername', emailid='$eusermail', mobileno='$emobile' WHERE id='$id'")

or die();
  if($updated)
  {
  $msg="Successfully Updated!!";
  header('Location:index.php');
  }
}
}  //update ends here
ob_end_flush();
?>
<!DOCTYPE>
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>Edit form</title>
<link type="text/css" media="all" rel="stylesheet" href="style.css">
</head>

<body>
<?php 
  if(isset($_GET['id']))
  {
  $id=$_GET['id'];
  $getselect=mysql_query("SELECT * FROM sample WHERE id='$id'");
  while($profile=mysql_fetch_array($getselect))
  {
    $username=$profile['username'];
    $usermail=$profile['emailid'];
    $usermobile=$profile['mobileno'];
?>
<div class="display">
  <form action="" method="post" name="insertform">
    <p>
      <label for="name"  id="preinput"> USER NAME : </label>
      <input type="text" name="eusername" required placeholder="Enter your name" 
      value="<?php echo $username; ?>" id="inputid" />
    </p>
    <p>
      <label  for="email"  id="preinput"> EMAIL ID : </label>
      <input type="email" name="eusermail" required placeholder="Enter your Email" 
      value="<?php echo $usermail; ?>" id="inputid" />
    </p>
    <p>
      <label for="mobile" id="preinput"> MOBILE NUMBER : </label>
      <input type="text" name="eusermobile" required placeholder="Enter your mobile number" 
      value="<?php echo $usermobile; ?>" id="inputid" />
    </p>
    <p>
      <input type="submit" name="update" value="Update" id="inputid1" />
    </p>
  </form>
</div>
<?php } } ?>
</body>
</html>