...

View Full Version : Having some trouble with rows being deleted w/ checkbox



bucket
08-29-2011, 02:17 PM
Im having some trouble with a table I made with rows being deleted with using a checkbox.

If someone could help me out that would be great.



<?php
$host="localhost"; // Host name
$username="brianubi_admin"; // Mysql username
$password="admin"; // Mysql password
$db_name="brianubi_admin"; // Database name
$tbl_name="test_mysql"; // Table name

//alternating row colors
$color1 = "#CCFFCC";
$color2 = "#BFD8BC";
$row_count = 0;

// 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);

$count=mysql_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="4" bgcolor="#FFFFFF"><strong>Delete multiple rows in mysql</strong> </td>
</tr>
<tr>
<td align="center" bgcolor="#FFFFFF">#</td>
<td align="center" bgcolor="#FFFFFF"><strong>Id</strong></td>
<td align="center" bgcolor="#FFFFFF"><strong>Name</strong></td>
<td align="center" bgcolor="#FFFFFF"><strong>Lastname</strong></td>
<td align="center" bgcolor="#FFFFFF"><strong>Email</strong></td>
</tr>
<?php
while($rows=mysql_fetch_array($result)){
?>
<tr>
<td align="center" bgcolor="#FFFFFF"><input name="checkbox[]" type="checkbox" id="checkbox[]" value="<? echo $rows['id']; ?>"></td>
<td bgcolor="#FFFFFF"><? echo $rows['id']; ?></td>
<td bgcolor="#FFFFFF"><? echo $rows['name']; ?></td>
<td bgcolor="#FFFFFF"><? echo $rows['lastname']; ?></td>
<td bgcolor="#FFFFFF"><? echo $rows['email']; ?></td>
</tr>
<?php
}
?>
<tr>
<td colspan="5" align="center" bgcolor="#FFFFFF"><input name="delete" type="submit" id="delete" value="Delete"></td>
</tr>
<?
// Check if delete button active, start this
if (isset($_POST['delete'])) {
for($i=0;$i<$count;$i++){
$del_id = $checkbox[$i];
$sql = "DELETE FROM $tbl_name WHERE id='$del_id'";
$result = mysql_query($sql);
}

// if successful redirect to delete_multiple.php
if($result){
echo "<meta http-equiv=\"refresh\" content=\"0;URL=index.php\">";
}
}
mysql_close();
?>
</table>
</form>
</td>
</tr>
</table>

Here is a live preview:

http://brian.ubietymedia.com/project

I would be so pleased to recieve some support.

Here is how my table is structured.



CREATE TABLE `test_mysql` (
`id` int(4) NOT NULL auto_increment,
`name` varchar(65) NOT NULL default '',
`lastname` varchar(65) NOT NULL default '',
`email` varchar(65) NOT NULL default '',
PRIMARY KEY (`id`)
) TYPE=MyISAM AUTO_INCREMENT=7 ;

--
-- Dumping data for table `test_mysql`
--

INSERT INTO `test_mysql` VALUES (1, 'Billly', 'Blueton', 'bb5@phpeasystep.com');
INSERT INTO `test_mysql` VALUES (2, 'Jame', 'Campbell', 'jame@somewhere.com');
INSERT INTO `test_mysql` VALUES (3, 'Mark', 'Jackson', 'mark@phpeasystep.com');
INSERT INTO `test_mysql` VALUES (4, 'Linda', 'Travor', 'lin65@phpeasystep.com');
INSERT INTO `test_mysql` VALUES (5, 'Joey', 'Ford', 'fordloi@somewhere.com');
INSERT INTO `test_mysql` VALUES (6, 'Sidney', 'Gibson', 'gibson@phpeasystep.com');

bucket
08-29-2011, 02:20 PM
I am getting this error:


Notice: Undefined variable: checkbox in /home/brianubi/public_html/project/index.php on line 63

Notice: Undefined variable: checkbox in /home/brianubi/public_html/project/index.php on line 63

Notice: Undefined variable: checkbox in /home/brianubi/public_html/project/index.php on line 63

Notice: Undefined variable: checkbox in /home/brianubi/public_html/project/index.php on line 63

Notice: Undefined variable: checkbox in /home/brianubi/public_html/project/index.php on line 63

Notice: Undefined variable: checkbox in /home/brianubi/public_html/project/index.php on line 63

Why is that?

bucket
08-29-2011, 02:24 PM
I managed to fix that error,now I have this one:



Notice: Undefined offset: 1 in /home/brianubi/public_html/project/index.php on line 65

Notice: Undefined offset: 2 in /home/brianubi/public_html/project/index.php on line 65

Notice: Undefined offset: 3 in /home/brianubi/public_html/project/index.php on line 65

Notice: Undefined offset: 4 in /home/brianubi/public_html/project/index.php on line 65

Rowsdower!
08-29-2011, 02:25 PM
You are trying to delete $checkbox[$i] - but where is that ever set up? Are you trying to delete straight from the $_POST? If so, that would be $_POST['checkbox'][$i] instead...

Or have I misunderstood the problem?

Rowsdower!
08-29-2011, 02:26 PM
I managed to fix that error,now I have this one:



Notice: Undefined offset: 1 in /home/brianubi/public_html/project/index.php on line 65

Notice: Undefined offset: 2 in /home/brianubi/public_html/project/index.php on line 65

Notice: Undefined offset: 3 in /home/brianubi/public_html/project/index.php on line 65

Notice: Undefined offset: 4 in /home/brianubi/public_html/project/index.php on line 65

What is on line 65?

bucket
08-29-2011, 02:35 PM
I managed to fix it, but one more annoying thing, after I select the checkboxes, and click delete, it reloads the page, and still shows the rows. I have to manually refresh the page to show the deleted rows. Its like stalling or something.

Why is that?

Rowsdower!
08-29-2011, 02:45 PM
That is because you are doing your db selection at the top of the script and the deletion at the bottom. So when you run the delete form it prints everything that was available before the delete ever occurs.

Move your deletion code to the top of the file (or at least above this part:
$sql="SELECT * FROM $tbl_name";
$result=mysql_query($sql);
) and you'll get the results you expect.

bucket
08-29-2011, 02:53 PM
Did that, but for some reason now it doesnt delete at all.



<?php
$tbl_name="test_mysql"; // Table name
mysql_connect("localhost", "brianubi_admin", "admin")or die("cannot connect");
mysql_select_db("brianubi_admin")or die("cannot select DB");


//alternating row colors
$color1 = "#CCFFCC";
$color2 = "#BFD8BC";
$row_count = 0;

if (isset($_POST['delete'])) {
$checkbox = $_POST['checkbox'];
for($i=0;$i<$count;$i++){
$del_id = $checkbox[$i];
$sql = "DELETE FROM $tbl_name WHERE id='$del_id'";
$result = mysql_query($sql);
}
}

//select the table
$sql="SELECT * FROM $tbl_name";
$result=mysql_query($sql);
$count=mysql_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="4" bgcolor="#FFFFFF"><strong>Delete multiple rows in mysql</strong> </td>
</tr>
<tr>
<td align="center" bgcolor="#FFFFFF">#</td>
<td align="center" bgcolor="#FFFFFF"><strong>Id</strong></td>
<td align="center" bgcolor="#FFFFFF"><strong>Name</strong></td>
<td align="center" bgcolor="#FFFFFF"><strong>Lastname</strong></td>
<td align="center" bgcolor="#FFFFFF"><strong>Email</strong></td>
</tr>
<?php
while($rows=mysql_fetch_array($result)){
?>
<tr>
<td align="center" bgcolor="#FFFFFF"><input name="checkbox[]" type="checkbox" id="checkbox[]" value="<? echo $rows['id']; ?>"></td>
<td bgcolor="#FFFFFF"><? echo $rows['id']; ?></td>
<td bgcolor="#FFFFFF"><? echo $rows['name']; ?></td>
<td bgcolor="#FFFFFF"><? echo $rows['lastname']; ?></td>
<td bgcolor="#FFFFFF"><? echo $rows['email']; ?></td>
</tr>
<?php
}
?>
<tr>
<td colspan="5" align="center" bgcolor="#FFFFFF"><input name="delete" type="submit" id="delete" value="Delete"></td>
</tr>
<?
// Check if delete button active, start this
mysql_close();
?>
</table>
</form>
</td>
</tr>
</table>

Help is very appreciated.

bucket
08-29-2011, 03:23 PM
Hm... I dont know what I am missing.

Fou-Lu
08-29-2011, 03:47 PM
Where's $count declared?
On a side note, you cannot name you're checkbox's id checkbox[]. HTML id's must be unique, use checkbox_$k and just create a $k var that increments.

bucket
08-29-2011, 04:03 PM
It seemed to work when I had the delete script lower on the page, closer to:


// Check if delete button active, start this
mysql_close();

But now it doesnt at all. I am really unsure what is wrong.

Also, it seems to work the way I am doing it with the checkbox[].

bucket
08-29-2011, 04:16 PM
I managed to move the script around a bit, and now it deletes the rows, but a Warning pops up when it loads the page after clicking delete



Warning: mysql_fetch_array() expects parameter 1 to be resource, boolean given in /home/brianubi/public_html/project/index.php on line 50

Here is my code:


<?php

$tbl_name="test_mysql"; // Table name
mysql_connect("localhost", "brianubi_admin", "admin")or die("cannot connect");
mysql_select_db("brianubi_admin")or die("cannot select DB");


//alternating row colors
$color1 = "#CCFFCC";
$color2 = "#BFD8BC";
$row_count = 0;

//select the table
$sql="SELECT * FROM $tbl_name";
$result=mysql_query($sql);
$count=mysql_num_rows($result);

if (isset($_POST['delete'])) {
$checkbox = $_POST['checkbox'];
for($i=0;$i<$count;$i++){
$del_id = $checkbox[$i];
$sql = "DELETE FROM $tbl_name WHERE id='$del_id'";
$result = mysql_query($sql);

}
if($result){
echo "<meta http-equiv=\"refresh\" content=\"6;URL=index.php\">";
}
}


?>
<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="4" bgcolor="#FFFFFF"><strong>Delete multiple rows in mysql</strong> </td>
</tr>
<tr>
<td align="center" bgcolor="#FFFFFF">#</td>
<td align="center" bgcolor="#FFFFFF"><strong>Id</strong></td>
<td align="center" bgcolor="#FFFFFF"><strong>Name</strong></td>
<td align="center" bgcolor="#FFFFFF"><strong>Lastname</strong></td>
<td align="center" bgcolor="#FFFFFF"><strong>Email</strong></td>
</tr>
<?php
while($rows=mysql_fetch_array($result)){
?>
<tr>
<td align="center" bgcolor="#FFFFFF"><input name="checkbox[]" type="checkbox" id="checkbox[]" value="<? echo $rows['id']; ?>"></td>
<td bgcolor="#FFFFFF"><? echo $rows['id']; ?></td>
<td bgcolor="#FFFFFF"><? echo $rows['name']; ?></td>
<td bgcolor="#FFFFFF"><? echo $rows['lastname']; ?></td>
<td bgcolor="#FFFFFF"><? echo $rows['email']; ?></td>
</tr>
<?php
}
?>
<tr>
<td colspan="5" align="center" bgcolor="#FFFFFF"><input name="delete" type="submit" id="delete" value="Delete"></td>
</tr>
<?
// Check if delete button active, start this
mysql_close();
?>
</table>
</form>
</td>
</tr>
</table>

It also shows a blank table.

Fou-Lu
08-29-2011, 04:16 PM
You're previous code worked since $count was declared as a part of the selection query (which is wrong in the case of handling a deletion). It no longer exists for the deletion to work with. That should be the count of $_POST['checkbox'], not any count relating to a query.
That can be easily simplified though to a simple IN clause:


$sDelIDs = implode("', '", $_POST['checkbox']);
$sQry = 'DELETE FROM table WHERE id IN (\'' . $sDelIDs . '\')';

No loop required.


As for your above post, deletion, creation and update queries to not return result sets, only booleans.

bucket
08-29-2011, 04:24 PM
Okay, I managed to change it to the code you posted, but it still does not delete.


<?php

$tbl_name="test_mysql"; // Table name
mysql_connect("localhost", "brianubi_admin", "admin")or die("cannot connect");
mysql_select_db("brianubi_admin")or die("cannot select DB");


//alternating row colors
$color1 = "#CCFFCC";
$color2 = "#BFD8BC";
$row_count = 0;

//select the table
$sql="SELECT * FROM $tbl_name";
$result=mysql_query($sql);
$count=mysql_num_rows($result);

if (isset($_POST['delete'])) {
$checkbox = $_POST['checkbox'];
$sDelIDs = implode("', '", $checkbox);
$sQry = 'DELETE FROM test_mysql WHERE id IN (\'' . $sDelIDs . '\')';

if($sQry){
echo "<meta http-equiv=\"refresh\" content=\"6;URL=index.php\">";
}
}


?>
<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="4" bgcolor="#FFFFFF"><strong>Delete multiple rows in mysql</strong> </td>
</tr>
<tr>
<td align="center" bgcolor="#FFFFFF">#</td>
<td align="center" bgcolor="#FFFFFF"><strong>Id</strong></td>
<td align="center" bgcolor="#FFFFFF"><strong>Name</strong></td>
<td align="center" bgcolor="#FFFFFF"><strong>Lastname</strong></td>
<td align="center" bgcolor="#FFFFFF"><strong>Email</strong></td>
</tr>
<?php
while($rows=mysql_fetch_array($result)){
?>
<tr>
<td align="center" bgcolor="#FFFFFF"><input name="checkbox[]" type="checkbox" id="checkbox[]" value="<? echo $rows['id']; ?>"></td>
<td bgcolor="#FFFFFF"><? echo $rows['id']; ?></td>
<td bgcolor="#FFFFFF"><? echo $rows['name']; ?></td>
<td bgcolor="#FFFFFF"><? echo $rows['lastname']; ?></td>
<td bgcolor="#FFFFFF"><? echo $rows['email']; ?></td>
</tr>
<?php
}
?>
<tr>
<td colspan="5" align="center" bgcolor="#FFFFFF"><input name="delete" type="submit" id="delete" value="Delete"></td>
</tr>
<?
// Check if delete button active, start this
mysql_close();
?>
</table>
</form>
</td>
</tr>
</table>

Fou-Lu
08-29-2011, 04:27 PM
You're not executing the query.
Also, if these are numbers, run them through an array map with an int cast, or if they are strings make sure they make their way through a real_escape_string.

bucket
08-29-2011, 04:35 PM
I am executing it...



<?php
error_reporting(E_ALL);
ini_set('display_errors', '1');

$tbl_name="test_mysql"; // Table name
mysql_connect("localhost", "brianubi_admin", "admin")or die("cannot connect");
mysql_select_db("brianubi_admin")or die("cannot select DB");


//alternating row colors
$color1 = "#CCFFCC";
$color2 = "#BFD8BC";
$row_count = 0;


if (isset($_POST['delete'])) {
$checkbox = $_POST['checkbox'];
$sDelIDs = $checkbox;
$sQry = 'DELETE FROM test_mysql WHERE id IN (\'' . $sDelIDs . '\')';
$result=mysql_query($sQry);
if($result){
echo "<meta http-equiv=\"refresh\" content=\"6;URL=index.php\">";
}
}
//select the table
$sql="SELECT * FROM $tbl_name";
$result=mysql_query($sql);
$count=mysql_num_rows($result);

?>

But its still not deleting anything...

Fou-Lu
08-29-2011, 05:11 PM
This doesn't match my code. You cannot delete an 'array'.


if (isset($_POST['delete'])) {
$checkbox = $_POST['checkbox'];
$sDelIDs = $checkbox;
$sQry = 'DELETE FROM test_mysql WHERE id IN (\'' . $sDelIDs . '\')';
$result=mysql_query($sQry);
if($result){
echo "<meta http-equiv=\"refresh\" content=\"6;URL=index.php\">";
}
}

That should be:


if (isset($_POST['delete'])) {
$checkbox = $_POST['checkbox'];
$sDelIDs = implode("', '", $checkbox);
$sQry = 'DELETE FROM test_mysql WHERE id IN (\'' . $sDelIDs . '\')';
$result=mysql_query($sQry);
if($result){
echo "<meta http-equiv=\"refresh\" content=\"6;URL=index.php\">";
}
}

And assumes that id's are strings. If they are not, they should be treated as integers since MySQL can be set to strict datatype mode.
Don't forget to cast or sanitize the data.

bucket
08-29-2011, 05:26 PM
I managed to make it my own little way.

It seems to work fast and great.



if (isset($_POST['delete'])) {
$checkbox = $_POST['checkbox'];
foreach($checkbox as $value)
{
$sql_query = mysql_query("DELETE FROM test_mysql WHERE id = '$value'");
}


if($sql_query){
echo "deleted!";
}
}

Fou-Lu
08-29-2011, 05:41 PM
Magnitude is higher than you need it to be. I'd still use an IN clause to execute a single query instead of n queries.
The problem with what you have above is that $sql_query only needs to succeed on the last record to say that it was deleted. You would be better off using a mysql_affected_rows to see how many were deleted.



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum