Asked  7 Months ago    Answers:  5   Viewed   30 times

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.

 Answers

55

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);
Wednesday, March 31, 2021
 
Gersom
answered 7 Months ago
32

Extract the information from the database for the checkbox fields. Next change the above example line to: (this code assumes that you've retrieved the information for the user into an associative array called dbvalue and the DB field names match those on the HTML form)

<input type="checkbox" name="tag_1" id="tag_1" value="yes" <?php echo ($dbvalue['tag_1']==1 ? 'checked' : '');?>>

If you're looking for the code to do everything for you, you've come to the wrong place.

Wednesday, March 31, 2021
 
diegoiglesias
answered 7 Months ago
14

=>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++;
}
?>
Wednesday, March 31, 2021
 
drowneath
answered 7 Months ago
12

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;
}   
Wednesday, March 31, 2021
 
Octopus
answered 7 Months ago
79

I think you are looking for

SELECT * FROM Posts WHERE class in (1,2,'w') ORDER BY class ASC

This will give you all the posts with the class values within the In () statement. It will show each record separately which means you will have two rows for titles which have multiple class values. Since it looks like you want to separate them using PHP that should be ok. Then you can do that in your while loop like:

$classes = array();
while($row = $stmt->fetch()){
  if(!isset($classes[$row['class']])){ $classes[$row['class']] = array(); }
  $classes[$row['class']][] = $row['title'];
}

This would give you arrays of Titles for each class:

[
   1=>[1234,9124]
   2=>[4567]
   'w'=>[9124]
]

Then you can output them all in order of the class or by class:

foreach($classes as $key => $class){
   echo "Class Value: ".$key; // just to show the order
   foreach($class as $title){ echo "<a>".$title."</a>"; }
}

Which will wind up giving you:

Class Value: 1
<a>1234</a>
<a>9124</a>
Class Value: 2
<a>4567</a>
Class Value: w
<a>9124</a>

I am not entirely sure if this leads you to what you are looking for, however, it does give you a workflow to be able to order the posts by class and output them based on class. I hope this helps

Saturday, May 29, 2021
 
rypskar
answered 5 Months ago
Only authorized users can answer the question. Please sign in first, or register a free account.
Not the answer you're looking for? Browse other questions tagged :