Delete multiple rows by selecting checkboxes using PHP

Question 1

I want to delete multiple rows from MYSQL database. I have created this delete.php file to select various links and delete them using checkboxes.

<html>
<head>

<title>Links Page</title>

</head>

<body>

<h2>Choose and delete selected links.</h2>

<?php

$dbc = mysqli_connect('localhost','root','admin','sample')
or die('Error connecting to MySQL server');

$query = "select * from links ORDER BY link_id";

$result = mysqli_query($dbc,$query)
or die('Error querying database');

$count=mysqli_num_rows($result);
?>

<table width="400" border="0" cellspacing="1" cellpadding="0">
<tr>
<td><form name="form1" method="post" action="">
<table width="400" border="0" cellpadding="3" cellspacing="1" bgcolor="#CCCCCC">
<tr>
<td bgcolor="#FFFFFF">&nbsp;</td>
<td colspan="3" bgcolor="#FFFFFF"><strong>Delete multiple links</strong> </td>
</tr>
<tr>
<td align="center" bgcolor="#FFFFFF">#</td>
<td align="center" bgcolor="#FFFFFF"><strong>Link ID</strong></td>
<td align="center" bgcolor="#FFFFFF"><strong>Link Name</strong></td>
<td align="center" bgcolor="#FFFFFF"><strong>Link URL</strong></td>
</tr>

<?php

while ($row=mysqli_fetch_array($result)) {
?>

<tr>
<td align="center" bgcolor="#FFFFFF"><input name="checkbox" type="checkbox" value="<?php echo $row['link_id']; ?>"></td>
<td bgcolor="#FFFFFF"><?php echo $row['link_id']; ?></td>
<td bgcolor="#FFFFFF"><?php echo $row['link_name']; ?></td>
<td bgcolor="#FFFFFF"><?php echo $row['link_url']; ?></td>
</tr>

<?php
}
?>

<tr>
<td colspan="4" align="center" bgcolor="#FFFFFF"><input name="delete" type="submit" value="Delete"></td>
</tr>
<table width="400" border="0" cellspacing="1" cellpadding="0">
<tr>
<td><form name="form1" method="post" action="">
<table width="400" border="0" cellpadding="3" cellspacing="1" bgcolor="#CCCCCC">
 <tr>
<td bgcolor="#FFFFFF">&nbsp;</td>
<td colspan="3" bgcolor="#FFFFFF"><strong>Delete multiple links</strong> </td>
</tr>
<tr>
<td align="center" bgcolor="#FFFFFF">#</td>
<td align="center" bgcolor="#FFFFFF"><strong>Link ID</strong></td>
<td align="center" bgcolor="#FFFFFF"><strong>Link Name</strong></td>
<td align="center" bgcolor="#FFFFFF"><strong>Link URL</strong></td>
 </tr>

<?php

while ($row=mysqli_fetch_array($result)) {
?>

<tr>
<td align="center" bgcolor="#FFFFFF"><input name="checkbox" type="checkbox" value="<?php echo $row['link_id']; ?>"></td>
<td bgcolor="#FFFFFF"><?php echo $row['link_id']; ?></td>
<td bgcolor="#FFFFFF"><?php echo $row['link_name']; ?></td>
<td bgcolor="#FFFFFF"><?php echo $row['link_url']; ?></td>
</tr>

<?php
}
?>

<tr>
<td colspan="4" align="center" bgcolor="#FFFFFF"><input name="delete" type="submit" value="Delete"></td>
</tr>



<?php

// Check if delete button active, start this 

if(isset($_POST['delete']))
{
    $checkbox = $_POST['checkbox'];

for($i=0;$i<count($checkbox);$i++){

$del_id = $checkbox[$i];
$sql = "DELETE FROM links WHERE link_id='$del_id'";
$result = mysqli_query($sql);
}
// if successful redirect to delete_multiple.php 
if($result){
echo "<meta http-equiv="refresh" content="0;URL=view_links.php">";
}
 }

mysqli_close($dbc);

?>

</table>
</form>
</td>
</tr>
</table>

</body>

</html>

This doesn't seem to delete any row. My data is populated in the table. I guess the problem is with the PHP code. Please help me out here.

Question 2

You should treat it as an array like this,

<input name="checkbox[]" type="checkbox" value="<?php echo $row['link_id']; ?>">

Then only, you can take its count and loop it for deletion.

You also need to pass the database connection to the query.

$result = mysqli_query($dbc, $sql);

Yours did not include it:

$result = mysqli_query($sql);

Question 3

MySQL can insert multiple rows in a single query. I left your code as close as possible to the original. Keep in mind that if you have a lot of data, this could create a large query that could be larger than what MySQL will accept.

include_once 'include.php';

$parts = array();    
foreach($_POST['vsr'] as $row=>$vsr) {
   $vsr=mysql_real_escape_string($vsr);
   $ofice=mysql_real_escape_string($_POST['ofice'][$row]);
   $date=mysql_real_escape_string($_POST['date'][$row]);
   $type=mysql_real_escape_string($_POST['type'][$row]);
   $qty=mysql_real_escape_string($_POST['qty'][$row]);
   $uprice=mysql_real_escape_string($_POST['uprice'][$row]);
   $tprice=mysql_real_escape_string($_POST['tprice'][$row]);

   $parts[] = "('$vsr','$ofice','$date','$type','$qty','$uprice','$tprice')";
}

$sql = "INSERT INTO maint_track (`vsr`, `ofice`, `date`, `type`, `qty`, `uprice`,
`tprice`) VALUES " . implode(', ', $parts);

$result = mysql_query($sql, $con);

Question 4

=>Try this Code I hope it's useful.

// html page..

<label>Select State</label><br>
<?php 
$allgroup = mysql_query("SELECT * FROM  state");
$flag=false;
while($state_list = mysql_fetch_array($allgroup))
{
   $parr=explode(',',$er['state_id']);
   $size = sizeof($parr);
   for($i=0;$i<$size;$i++) { 
     if($parr[$i]==$state_list['id']) {                              
        $flag=true;
     } 
   }    

  if($flag==true) {
    ?>
    <input  type='checkbox' name='state[]' style="margin-left:5px;" value="<?php echo $state_list['id']; ?>" checked  > <?php echo $state_list['name']; ?> <br>
    <?php
    $flag=false;
  } else { 
    ?>
    <input  type='checkbox' name='state[]' style="margin-left:5px;" value="<?php echo $state_list['id']; ?>"     > <?php echo $state_list['name']; ?> <br>
    <?php
    }
}
?>

// php code ..

<?php
$states="";
$i=0;
foreach( $_POST['state'] as $selected) {
   echo sizeof($_POST['state']);
   if($i==sizeof($_POST['state'])-1) {
       $states = $states.$selected;
   } else {
     $states = $states.$selected.",";
   }
   $i++;
}
?>

Question 5

So I was finally able to find a solution which I thought I tried. I think the reason why it wasn't working might have something to do with me trying to use bindParam.

Here is what I changed my remove-user.php code to:

try{
    $ids = array($_GET['userID']);
    $inQuery = implode(',', $ids);
    $stmt = $db->prepare(
        'DELETE
         FROM users
         WHERE user_id IN(' . $inQuery . ')'
    );
    $stmt->execute($ids);
    $count = $stmt->rowCount();
    $user_removed = ''.$count.' user(s) deleted successfully.';
    $_SESSION['user_removed'] = $user_removed;
} catch (Exception $e){
    $error = '<strong>The following error occured:</strong>'.$e->getMessage();
    $_SESSION['error'] = $error;
}

Question 6

You need to give the checkboxes an explicit index in the HTML:

$i = 0;
while($shift_query = $result_shift_query->fetch_assoc())
    {
    echo "<tr>";    
    echo "<td><input type="hidden" name="Shift_ID[]"></td>";
    echo "<td><input type="text" name="Shift_Name[]"></td>";
    echo "<td><input type="text" name="Shift_Short_Name[]"></td>";
    echo "<td><input type="text" name="Shift_Color[]"></td>";
    echo "<td><input type="checkbox" name="Shift_Trig[$i]"";
        if($shift_query['Shift_Trig'] == '1')
            {
            echo " checked="checked"";
            }
    echo " value="1"></td>";
    echo "<td><input type="checkbox" name="deleteBox[$i]"></td>";
    echo "</tr>";
    $i++;
    }

In the update code, at the beginning of the loop, do:

    if (!isset($trig[$i])) { $trig[$i] = 0; }

The rest of the update code is unchanged.

Delete multiple rows by selecting checkboxes using PHP

Answers

php,html,mysql,checkbox,isset