contact

Contact Person
Pin BB : 5AB31960
SMS : 082288467823
WA : 081275371724
call : 081275668074


Multiple Insert, Update, Delete dengan PHP & MySQLi OOP

Posted in PHP MYSQL / i Life at 08 Oktober 2016 With 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



multi delete insert update

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();
}

Artikel Terkait

Diskusi