Multiple Insert, Update, Delete dengan PHP & MySQLi OOP

Publish Date : 08-10-2016 , dibaca 27189 kali, Hari ini dibaca : 1 kali ,0 comments

Pada tutorial sebelumnya admin sudah menulis artikel tentang Membuat multi delete dengan php , anda dapat mengcolaborasikannya lagi dengan Check or Uncheck all checkbox dengan JavaScript , sehingga data nya mudah untuk kita hapus, di tambah lagi kita berikan checxbox all dan uncheck all , Nah pada tutorial kali ini kita akan membahas Membuat Multiple Insert, Update , dan Delete data dengan PHP & Mysqli OOP ,

OK.. Mari kita mulai pembahasannya langsung

Cara Membuat Multiple Insert, Update dan Delete

Lets Starting Coding

Pertama buat dulu database dengan nama "dbmultiple" dan buat tabel dengan nama "users" , Copy can code dibawah in di sql nya

CREATE DATABASE `dbmultiple` ;
CREATE TABLE `dbmultiple`.`users` (
`id` INT( 5 ) NOT NULL AUTO_INCREMENT PRIMARY KEY ,
`first_name` VARCHAR( 25 ) NOT NULL ,
`last_name` VARCHAR( 35 ) NOT NULL 
) ENGINE = MYISAM ;

Susunan code yang kita gunakan

-dbcon.php
-index.php
-generate.php
-add-data.php
-edit_mul.php
-update_mul.php
-delete_mul.php

dbcon.php

copy simple database configuration code with MySQLi.dibawah ini :

<?php
  $DB_host = "localhost";
  $DB_user = "root";
  $DB_pass = "";
  $DB_name = "dbmultiple";
  $MySQLiconn = new MySQLi($DB_host,$DB_user,$DB_pass,$DB_name);
     if($MySQLiconn->connect_errno)
     {
         die("ERROR : -> ".$MySQLiconn->connect_error);
     }
?>

index.php

copy script ini kedalam file index.php, pada index.php ini kita membuat script yang digunakan untuk menampilkan data didatabase nya :

<?php
 include_once 'dbcon.php';
?>
<!DOCTYPE>
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>Multiple Insert, Update, Delete(CRUD) using PHP & MySQLi</title>
<link rel="stylesheet" href="style.css" type="text/css" />
<script src="jquery.js" type="text/javascript"></script>
<script src="js-script.js" type="text/javascript"></script>
</head>
<body>
<form method="post" name="frm">
<table width="50%" align="center" border="0">
<tr>
<td colspan="3"><a href="generate.php">add new records...</a></td>
</tr>
<tr>
<th>##</th>
<th>First Name</th>
<th>Last Name</th>
</tr>
<?php
 $res = $MySQLiconn->query("SELECT * FROM users");
 $count = $res->num_rows;
 if($count > 0)
 {
  while($row=$res->fetch_array())
  {
   ?>
   <tr>
   <td><input type="checkbox" name="chk[]" class="chk-box" value="<?php echo $row['id']; ?>"  /></td>
   <td><?php echo $row['first_name']; ?></td>
   <td><?php echo $row['last_name']; ?></td>
   </tr>
   <?php
  } 
 }
 else
 {
  ?>
        <tr>
        <td colspan="3"> No Records Found ...</td>
        </tr>
        <?php
 }
?>
<?php
if($count > 0)
{
 ?>
 <tr>
    <td colspan="3">
    <label><input type="checkbox" class="select-all" /> Check / Uncheck All</label>
    <label id="actions">
    <span style="word-spacing:normal;"> with selected :</span>
    <span><img src="edit.png" onClick="edit();" alt="edit" />edit</span> 
    <span><img src="delete.png" onClick="delete_rec();" alt="delete" />delete</span>
    </label>
    </td>
 </tr>    
    <?php
}
?>
</table>
</form>
</body>
</html>

multiple insert

generate.php

<link rel="stylesheet" href="style.css" type="text/css" />
<form method="post" action="add-data.php">
<table width="50%" align="center" border="0">
<tr>
<td>Enter how many records you want to insert</td>
</tr>
<tr>
<td>
<input type="text" name="no_of_rec" placeholder="how many records u want to enter ? ex : 1 , 2 , 3 , 5" maxlength="2" pattern="[0-9]+" required />
</td>
</tr>
<tr>
<td><button type="submit" name="btn-gen-form">Generate</button> 
&nbsp;
<a href="index.php">back</a>
</td>
</tr>
</table>
</form>

add-data.php

<?php
error_reporting(0);
include_once 'dbcon.php';
if(isset($_POST['save_mul']))
{  
 $total = $_POST['total'];
 for($i=1; $i<=$total; $i++)
 {
  $fn = $_POST["fname$i"];
  $ln = $_POST["lname$i"];  
  $sql="INSERT INTO users(first_name,last_name) VALUES('".$fn."','".$ln."')";
  $sql = $MySQLiconn->query($sql);  
 }
 if($sql)
 {
  ?>
        <script>
  alert('<?php echo $total." records was inserted !!!"; ?>');
  window.location.href='index.php';
  </script>
        <?php
 }
 else
 {
  ?>
        <script>
  alert('error while inserting , TRY AGAIN');
  </script>
        <?php
 }
}
?>
<link rel="stylesheet" href="style.css" type="text/css" />
<div class="container">
<?php
if(isset($_POST['btn-gen-form']))
{
 ?>
    <form method="post">
    <input type="hidden" name="total" value="<?php echo $_POST["no_of_rec"]; ?>" />
 <table width="50%" align="center" border="0">
    <tr>
    <td colspan="3"><a href="generate.php">insert more records...</a></td>
    </tr>
    <tr>
    <th>##</th>
    <th>First Name</th>
    <th>Last Name</th>
    </tr>
 <?php
 for($i=1; $i<=$_POST["no_of_rec"]; $i++) 
 {
  ?>
        <tr>
        <td><?php echo $i; ?></td>
        <td><input type="text" name="fname<?php echo $i; ?>" placeholder="first name" /></td>
        <td><input type="text" name="lname<?php echo $i; ?>" placeholder="last name" /></td>
        </tr>
        <?php
 }
 ?>
    <tr>
    <td colspan="3">
    <button type="submit" name="save_mul">Insert all Records</button> 
    <a href="index.php" >Back to index</a>
    </td>
    </tr>
    </table>
    </form>
 <?php
}
?>
</div>

Multiple Update

edit_mul.php

<?php
 include_once 'dbcon.php';
 if(isset($_POST['chk'])=="")
 {
  ?>
        <script>
  alert('At least one checkbox Must be Selected !!!');
  window.location.href='index.php';
  </script>
        <?php
 }
 $chk = $_POST['chk'];
 $chkcount = count($chk);
?>
<form method="post" action="update_mul.php">
<link rel="stylesheet" href="style.css" type="text/css" />
<table width="50%" align="center" border="0">
<tr>
<th>First Name</th>
<th>Last Name</th>
</tr>
<?php
for($i=0; $i<$chkcount; $i++)
{
 $id = $chk[$i];   
 $res=$MySQLiconn->query("SELECT * FROM users WHERE id=".$id);
 while($row=$res->fetch_array())
 {
  ?>
  <tr>
  <td>
     <input type="hidden" name="id[]" value="<?php echo $row['id'];?>" />
  <input type="text" name="fn[]" value="<?php echo $row['first_name'];?>" />
        </td>
        <td>
  <input type="text" name="ln[]" value="<?php echo $row['last_name'];?>" />
  </td>
  </tr>
  <?php 
 }   
}
?>
<tr>
<td colspan="2">
<button type="submit" name="savemul">Update all</button>&nbsp;
<a href="index.php">cancel</a>
</td>
</tr>
</table>
</form>

update_mul.php

<?php
include_once 'dbcon.php';
$id = $_POST['id'];
$fn = $_POST['fn'];
$ln = $_POST['ln'];
$chk = $_POST['chk'];
$chkcount = count($id);
for($i=0; $i<$chkcount; $i++)
{
 $MySQLiconn->query("UPDATE users SET first_name='$fn[$i]', last_name='$ln[$i]' WHERE id=".$id[$i]);
}
header("Location: index.php");
?>

Multiple Delete

delete_mul.php

<?php
 error_reporting(0);
 include_once 'dbcon.php';
 $chk = $_POST['chk'];
 $chkcount = count($chk);
 if(!isset($chk))
 {
  ?>
        <script>
  alert('At least one checkbox Must be Selected !!!');
  window.location.href = 'index.php';
  </script>
        <?php
 }
 else
 {
  for($i=0; $i<$chkcount; $i++)
  {
   $del = $chk[$i];
   $sql=$MySQLiconn->query("DELETE FROM users WHERE id=".$del);
  } 
  if($sql)
  {
   ?>
   <script>
   alert('<?php echo $chkcount; ?> Records Was Deleted !!!');
   window.location.href='index.php';
   </script>
   <?php
  }
  else
  {
   ?>
   <script>
   alert('Error while Deleting , TRY AGAIN');
   window.location.href='index.php';
   </script>
   <?php
  }
 }
?>

js-script.j

// JavaScript Document
//  for select / deselect all
$('document').ready(function()
{
    $(".select-all").click(function ()
    {
        $('.chk-box').attr('checked', this.checked)
    });
    $(".chk-box").click(function()
    {
        if($(".chk-box").length == $(".chk-box:checked").length)
        {
            $(".select-all").attr("checked", "checked");
        }
        else
        {
            $(".select-all").removeAttr("checked");
        }
    });
});
//  for select / deselect all
//  dynamically redirects to specified page
function edit_records() 
{
 document.frm.action = "edit_mul.php";
 document.frm.submit();  
}
function delete_records() 
{
 document.frm.action = "delete_mul.php";
 document.frm.submit();
}