How to use html form and php to delete selected values from database?

[garvit184]

Please can someone tell me how to use html form and php to delete selected values from database.

here is my code :-

 

while($row1 = mysql_fetch_array($result1))

{

echo '<tr onmouseover="this.style.backgroundColor=… onmouseout="this.style.backgroundColor="…

echo "<td>";

echo '<input type="checkbox" name="selected"/>';

echo "<td>" . $row1['username'] . "</td>";

echo "<td>" . $row1['subject'] . "</td>";

echo "<td>" . $row1['message'] . "</td>";

echo "</tr>";

}

 

 

 

What I want is that user select the entries that he want to delete using checkbox I have given then he clicks at delete button which deletes the entries from the database using their ID's ? Please help its urgent I have to submit my project by Monday !

[viviosoft]

Are you wanting to delete to delete record from the database when the user checks the box?  If so, you'll have to use javascript to perform that action.  OR are you using a POST action to perform the action? For example, the user checks the box then clicks Submit to perform the delete action.

[garvit184]

I tried using javascript method here is the code what I tried and I have also included the result I am get. The table is not getting populated neither the record is been deleted don't know why? I am new to PHP and can't the error. Here is the code please let me know if you can find the error or else explain me the method that I should use..

 

<?php

$host = 'localhost'; // Host name

$username = 'root'; // Mysql username

$password = ''; // Mysql password

$db_name = 'imail'; // Database name

$tbl_name = 'messages'; // Table name - DO NOT TOUCH

 

// Connect to server and select databse.

mysql_connect($host, $username, $password) or die('cannot connect');

mysql_select_db($db_name) or die('cannot select DB');

 

$sql = 'SELECT * FROM `'.$tbl_name.'`';

$result = mysql_query($sql);

?>

<script type="text/javascript">

function selectAll(x) {

for(var i=0,l=x.form.length; i<l; i++)

if(x.form.type == 'checkbox' && x.form.name != 'sAll')

x.form.checked=x.form.checked?false:true

}

</script>

<table width="653" border="0" cellspacing="1" cellpadding="0">

<tr>

<td width="651">

<form name="form1" method="post" action="">

 

<table width="694" border="0" cellpadding="3" cellspacing="1" bgcolor="#CCCCCC">

<tr>

<td width="57" bgcolor="#FFFFFF"> </td>

<td colspan="4" bgcolor="#FFFFFF"><strong>Delete multiple rows in mysql</strong> </td>

</tr>

<tr>

<td align="center" bgcolor="#FFFFFF">#</td>

<td width="57" align="center" bgcolor="#FFFFFF"><strong>MsgId</strong></td>

 

<td width="176" align="center" bgcolor="#FFFFFF"><strong>Username</strong></td>

 

<td width="216" align="center" bgcolor="#FFFFFF"><strong>Message</strong></td>

 

<td width="152" align="center" bgcolor="#FFFFFF"><strong>Subject</strong></td>

</tr>

<?php while ($rows = mysql_fetch_array($result)): ?>

<tr>

<td align="center" bgcolor="#FFFFFF"><input name="need_delete[<? echo $rows['msgid']; ?>]" type="checkbox" id="checkbox[<? echo $rows['msgid']; ?>]" value="<? echo $rows['msgid']; ?>"></td>

<td bgcolor="#FFFFFF"><? echo $rows['msgid']; ?></td>

<td bgcolor="#FFFFFF"><? echo htmlspecialchars($rows['username']); ?></td>

<td bgcolor="#FFFFFF"><? echo htmlspecialchars($rows['message']); ?></td>

<td bgcolor="#FFFFFF"><? echo htmlspecialchars($rows['subject']); ?></td>

</tr>

<?php endwhile; ?>

<tr>

<td colspan="5" align="center" bgcolor="#FFFFFF"><input name="delete" type="submit" id="delete" value="Delete">

<input type="checkbox" name="sAll" onclick="selectAll(this)" /> (Select all)<br />

</td>

</tr>

<?php

// Check if delete button active, start this

if ( ! empty($_POST['delete'])) {

foreach ($_POST['need_delete'] as $id => $value) {

$sql = 'DELETE FROM `'.$tbl_name.'` WHERE `msgid`='.(int)$id;

mysql_query($sql);

}

header('Location: shoutcron.php'); exit();

}

mysql_close();

?>

</table>

</form>

</td>

</tr>

</table>

[viviosoft]

On your select statement using the following to see any errors you might be getting:

 

$sql = 'SELECT * FROM `'.$tbl_name.'`';
$result = mysql_query($sql) or die(mysql_error()); // <-- Adding  or die(mysql_error()); will help you determine if you have any errors in your query

 

You need to start you php echos with <?php NOT <? .  Thy this and see if you get any results in your table.

 

 

                    <?php while ($rows = mysql_fetch_array($result)): ?>
                    <tr>
                        <td align="center" bgcolor="#FFFFFF"><input name="need_delete[<?php echo $rows['msgid']; ?>]"
                                                                    type="checkbox"
                                                                    id="checkbox[<?php echo $rows['msgid']; ?>]"
                                                                    value="<?php echo $rows['msgid']; ?>"></td>
                        <td bgcolor="#FFFFFF"><?php echo $rows['Id']; ?></td>
                        <td bgcolor="#FFFFFF"><?php echo htmlspecialchars($rows['Username']); ?></td>
                        <td bgcolor="#FFFFFF"><?php echo htmlspecialchars($rows['Message']); ?></td>
                        <td bgcolor="#FFFFFF"><?php echo htmlspecialchars($rows['Subject']); ?></td>
                    </tr>
                    <?php endwhile; ?>

 

 

 

[Drummin]

Modified version to compare.

<?php
$host = 'localhost'; // Host name
$username = 'root'; // Mysql username
$password = ''; // Mysql password
$db_name = 'imail'; // Database name
$tbl_name = 'messages'; // Table name - DO NOT TOUCH

// Connect to server and select databse.
mysql_connect($host, $username, $password) or die('cannot connect');
mysql_select_db($db_name) or die('cannot select DB');

// Check if delete button active, start this
if (isset($_POST['delete'])) {
foreach ($_POST['need_delete'] as $id) {
$sql = "DELETE FROM `$tbl_name` WHERE `msgid`='$id'";
mysql_query($sql);
}
header('Location: shoutcron.php'); exit();
}
// If you are expecting to use header location above, this has to happen before anything is sent to browser so <html> tag needs to be here.
?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN"
        "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<title>My page</title>
</head>
<body>
<?php
$sql = 'SELECT * FROM `'.$tbl_name.'`';
$result = mysql_query($sql);
?>

<table width="653" border="0" cellspacing="1" cellpadding="0">
<tr>
<td width="651">
<form name="form1" method="post" action="">

<table width="694" border="0" cellpadding="3" cellspacing="1" bgcolor="#CCCCCC">
<tr>
<td width="57" bgcolor="#FFFFFF"> </td>
<td colspan="4" bgcolor="#FFFFFF"><strong>Delete multiple rows in mysql</strong> </td>
</tr>
<tr>
<td align="center" bgcolor="#FFFFFF">#</td>
<td width="57" align="center" bgcolor="#FFFFFF"><strong>MsgId</strong></td>

<td width="176" align="center" bgcolor="#FFFFFF"><strong>Username</strong></td>

<td width="216" align="center" bgcolor="#FFFFFF"><strong>Message</strong></td>

<td width="152" align="center" bgcolor="#FFFFFF"><strong>Subject</strong></td>
</tr>
<?php while ($rows = mysql_fetch_array($result)){ ?>
<tr>
<td align="center" bgcolor="#FFFFFF"><input name="need_delete[]" type="checkbox" value="<?php echo $rows['msgid']; ?>"></td>
<td bgcolor="#FFFFFF"><?php echo $rows['msgid']; ?></td>
<td bgcolor="#FFFFFF"><?php echo htmlspecialchars($rows['username']); ?></td>
<td bgcolor="#FFFFFF"><?php echo htmlspecialchars($rows['message']); ?></td>
<td bgcolor="#FFFFFF"><?php echo htmlspecialchars($rows['subject']); ?></td>
</tr>
<?php } ?>
<tr>
<td colspan="5" align="center" bgcolor="#FFFFFF"><input name="delete" type="submit" id="delete" value="Delete"></td>
</tr>
</table>
</form>
</td>
</tr>
</table>
</body>
</html> 

[garvit184]

Thanx a lott Drummin & viviosoft. My code is perfectly working now !!!

The code that Drummin provided works perfectly 

[Drummin]

Understand that when using foreach you are getting the KEY and Value when you use

foreach ($_POST['need_delete'] as $id => $value)

...and as you know array keys start with 0 (zero) for the first record, 1 for the next record etc.  So when defining the value with => $value, that turns the variable after "as" ($id) into the key or zero for the first record.  Array keys and values can be very useful if say you were updating other information in the record as the array keys would be the same for user_id[], name[], status[] etc.

 

In this case though, we just need the value or the record id so by not defining the value with => $value, $id becomes the value.

[garvit184]

Thanks, now I get it ! I have one small problem in header location what should I put because I have user ajax to show that table in <div id="center"> now when I press delete it brings me to the main page and not " javascript:ajaxpage('inbox.php','center')" ? What I basically want is that in my inbox.php I have the table with all messages and when user selected the message he wants to delete and click delete he should redirected to the same inbox.php with the messages deleted from the table.

The code you provided is working perfectly without ajax but with it I have problem in identifying what should I give in header location ?

[Drummin]

If I understand you, then you're using the same page, and thus you don't need a header location.  Processing is done before page is rendered so deleted records are remove when processing is done and then new query to show records is made.

[garvit184]

Ohk...Thanks a ton !  Got it and its working perfectly.

Now, If I want to use a CSS button for deletion then how can I get my form to submit ? More precisely an Image as a delete button ?

[Drummin]

A little JS should do the job.  Just style the link as a button.

http://www.javascript-coder.com/files/form-submit/javascript-form-submit-example.html

 

You could also use an image if you wish.

http://www.webdevelopersnotes.com/tips/html/using_an_image_as_a_submit_button.php3

 

[KevinM1]

A little JS should do the job.  Just style the link as a button.

http://www.javascript-coder.com/files/form-submit/javascript-form-submit-example.html

You could also use an image if you wish.

http://www.webdevelopersnotes.com/tips/html/using_an_image_as_a_submit_button.php3

 

Go with what I bolded.  There's no need to use JavaScript for this.