PDA

Click to See Complete Forum and Search --> : Delete Multiple Rows | Php/mysql

LogicOpinion
10-10-2007, 07:59 AM
Hello,

with the code below i delete 1 row even if several rows are selected, what should i do to be able to delete as much rows as much i select at once.



<?php
mysql_connect("localhost", "root", "") or die ("Could not connect");
mysql_select_db("db1") or die ("Could not connect to DB");
if ($_POST['delete'])

{

$deleteID = $_POST['delete'];

mysql_query("DELETE FROM dbtable WHERE id='$deleteID'") or die(mysql_error());

echo "The Row Number $deleteID has been Successfully Removed";


}
else

{

echo "please select at least one row, to delete it";

}

?>
MrCoder
10-10-2007, 08:29 AM
Please show us the source code for the form that you are submitting to that page.
LogicOpinion
10-10-2007, 08:35 AM
thats the code:

Thanks.

<body>
<form action="index.php?goto=remove" method="post">
<?php



mysql_connect("localhost", "root", "") or die(mysql_error());
mysql_select_db("db1") or die(mysql_error());
$query = "SELECT * FROM dbtable ORDER BY id DESC";
$result = mysql_query($query) or die(mysql_error());

$i = 0;

echo "<table align=center width=98% class=maintxt border=0 cellpadding=0 cellspacing=2>";


while($row = mysql_fetch_array($result))
{


if ($i == 0)

{
$bgcolor = '#DCDCDC';
$i = 1;
} else {
$bgcolor = '#f5f5f5';
$i = '0';
}

print "<tr>";
echo "<td height='25' bgcolor='$bgcolor' align=center>";
echo $row['id'];
print "</td>";
print "<td bgcolor='$bgcolor' align=center>";
echo $row['group_id'];
print "</td>";
print "<td bgcolor='$bgcolor' align=center>";
echo $row['name'];
print "</td>";
print "<td bgcolor='$bgcolor' align=center>";
echo $row['s_name'];
print "</td>";
print "<td bgcolor='$bgcolor' align=center>";
echo $row['city'];
print "</td>";
print "<td bgcolor='$bgcolor' align=center>";
echo $row['school_id'];
print "</td>";
print "<td bgcolor='$bgcolor' align=center>";
echo $row['proffesion'];
print "</td>";
print "<td bgcolor='$bgcolor' align=center>";
echo $row['telephone'];
print "</td>";
print "<td bgcolor='$bgcolor' align=center>";
echo $row['personal_id'];
print "</td>";
print "<td bgcolor='$bgcolor' align=center>";
echo $row['date'];
print "</td>";
print "<td bgcolor='$bgcolor' align=center>";
print "<input name='delete' value='{$row['id']}' type='checkbox'>";
print "</td>";
print "</tr>";


}




?>
&nbsp;&nbsp;&nbsp;&nbsp;<input type="submit" value="წაშლა" name="B1"><br><br>
</form>
MrCoder
10-10-2007, 08:49 AM
Add [] to the following..


<input name='delete[]' value='{$row['id']}' type='checkbox'>


This will pass an array of rows that you want to delete then add the following to the top of your script before the mysql_connect (in index.php?goto=remove)..

echo "<pre>".var_export($_POST, true)."</pre>";
die();


That will let you see the $_POST values that are coming from the form.

Now change your mysql query to delete all the selected ones by cycling through the $_POST values by building a SQL query to do it in 1 query, or execute a query for each one.
LogicOpinion
10-10-2007, 09:03 AM
this is the form:


<form action="index.php?goto=remove" method="post">
<?php
mysql_connect("localhost", "root", "") or die(mysql_error());
mysql_select_db("db1") or die(mysql_error());
$query = "SELECT * FROM dbtable ORDER BY id DESC";
$result = mysql_query($query) or die(mysql_error());

$i = 0;

echo "<table align=center width=98% class=maintxt border=0 cellpadding=0 cellspacing=2>";


while($row = mysql_fetch_array($result))
{


if ($i == 0)

{
$bgcolor = '#DCDCDC';
$i = 1;
} else {
$bgcolor = '#f5f5f5';
$i = '0';
}

print "<tr>";
echo "<td height='25' bgcolor='$bgcolor' align=center>";
echo $row['id'];
print "</td>";
print "<td bgcolor='$bgcolor' align=center>";
echo $row['group_id'];
print "</td>";
print "<td bgcolor='$bgcolor' align=center>";
echo $row['name'];
print "</td>";
print "<td bgcolor='$bgcolor' align=center>";
echo $row['s_name'];
print "</td>";
print "<td bgcolor='$bgcolor' align=center>";
echo $row['city'];
print "</td>";
print "<td bgcolor='$bgcolor' align=center>";
echo $row['school_id'];
print "</td>";
print "<td bgcolor='$bgcolor' align=center>";
echo $row['proffesion'];
print "</td>";
print "<td bgcolor='$bgcolor' align=center>";
echo $row['telephone'];
print "</td>";
print "<td bgcolor='$bgcolor' align=center>";
echo $row['personal_id'];
print "</td>";
print "<td bgcolor='$bgcolor' align=center>";
echo $row['date'];
print "</td>";
print "<td bgcolor='$bgcolor' align=center>";
print "<input name='delete[]' value='{$row['id']}' type='checkbox'>";
print "</td>";
print "</tr>";


}




?>
&nbsp;&nbsp;&nbsp;&nbsp;<input type="submit" value="წაშლა" name="submit"><br><br>
</form>


and this is remove.php :


<?php
echo "<pre>".var_export($_POST, true)."</pre>";
die();
mysql_connect("localhost", "root", "") or die ("Could not connect");
mysql_select_db("db1") or die ("Could not connect to DB");
if ($_POST['delete'])

{

$deleteID = $_POST['delete'];

mysql_query("DELETE FROM dbtable WHERE id='$deleteID'") or die(mysql_error());

echo "The Row Number $deleteID has been Successfully Removed";


}
else

{

echo "please select at least one row, to delete it";

}

?>


when i select several CHECKBOXes

it outputs something like this :

array (
'delete' => '5',
'submit' => 'remove',
)
MrCoder
10-10-2007, 10:20 AM
Add enctype="multipart/form-data" to the <form> tag.
LogicOpinion
10-10-2007, 10:31 AM
it does the same :

please take a look at the code of both files again. maybe i miss something.

form


<form enctype="multipart/form-data" action="index.php?goto=remove" method="post">
<?php
mysql_connect("localhost", "root", "") or die(mysql_error());
mysql_select_db("db1") or die(mysql_error());
$query = "SELECT * FROM dbtable ORDER BY id DESC";
$result = mysql_query($query) or die(mysql_error());

$i = 0;

echo "<table align=center width=98% class=maintxt border=0 cellpadding=0 cellspacing=2>";
print "<tr>";
print "<td bgcolor=#FFF8DC height=40>&nbsp;&nbsp;<B>№</B>&nbsp;&nbsp;</td>";
print "<td bgcolor=#FFF8DC align=center><B>ჯგუფი</B></td>";
print "<td bgcolor=#FFF8DC align=center><B>სახელი</B></td>";
print "<td bgcolor=#FFF8DC align=center><B>გვარი</B></td>";
print "<td bgcolor=#FFF8DC align=center><B>ქალაქი</B></td>";
print "<td bgcolor=#FFF8DC align=center><B>სკოლა ნომერი</B></td>";
print "<td bgcolor=#FFF8DC align=center><B>პროფესია</B></td>";
print "<td bgcolor=#FFF8DC align=center><B>საკონტაქტო ტელეფონი</B></td>";
print "<td bgcolor=#FFF8DC align=center><B>პირადი ნომერი</B></td>";
print "<td bgcolor=#FFF8DC align=center><B>ტრენინგის გავლის თარიღი</B></td>";
print "<td bgcolor=#FFF8DC align=center><B>შენიშვნა</B></td>";
print "<td bgcolor=#FFF8DC align=center>&nbsp;&nbsp;<B>select</B>&nbsp;&nbsp;</td>";
print "</tr>";

while($row = mysql_fetch_array($result))
{


if ($i == 0)

{
$bgcolor = '#DCDCDC';
$i = 1;
} else {
$bgcolor = '#f5f5f5';
$i = '0';
}

print "<tr>";
echo "<td height='25' bgcolor='$bgcolor' align=center>";
echo $row['id'];
print "</td>";
print "<td bgcolor='$bgcolor' align=center>";
echo $row['group_id'];
print "</td>";
print "<td bgcolor='$bgcolor' align=center>";
echo $row['name'];
print "</td>";
print "<td bgcolor='$bgcolor' align=center>";
echo $row['s_name'];
print "</td>";
print "<td bgcolor='$bgcolor' align=center>";
echo $row['city'];
print "</td>";
print "<td bgcolor='$bgcolor' align=center>";
echo $row['school_id'];
print "</td>";
print "<td bgcolor='$bgcolor' align=center>";
echo $row['proffesion'];
print "</td>";
print "<td bgcolor='$bgcolor' align=center>";
echo $row['telephone'];
print "</td>";
print "<td bgcolor='$bgcolor' align=center>";
echo $row['personal_id'];
print "</td>";
print "<td bgcolor='$bgcolor' align=center>";
echo $row['date'];
print "</td>";
print "<td bgcolor='$bgcolor' align=center>";
echo $row['note'];
print "</td>";
print "<td bgcolor='$bgcolor' align=center>";
print "<input name='delete[]' value='{$row['id']}' type='checkbox'>";
print "</td>";
print "</tr>";
}
?>


</table>
<table align=center width=98% class=maintxt border=0 cellpadding=0 cellspacing=2>
<tr>
<td align=left width=50><input type="submit" value="წაშლა" name="submit"></td>
<td align=left><input type="submit" value="რედაქტირება" name="submit"></td>
</tr>
</table>
</form>



remove.php



<body>
<?php
echo "<pre>".var_export($_POST, true)."</pre>";
die();
mysql_connect("localhost", "root", "") or die ("Could not connect");
mysql_select_db("db1") or die ("Could not connect to DB");
if ($_POST['delete'])

{

$deleteID = $_POST['delete'];

mysql_query("DELETE FROM dbtable WHERE id='$deleteID'") or die(mysql_error());

echo "The Row Number $deleteID has been Successfully Removed";


}
else

{

echo "please select at least one row, to delete it";

}

?>
</body>





i selected all the checkboxes i had and then pressed submit button and it showed me such thing:


array (
'delete' =>
array (
0 => '12',
1 => '11',
2 => '10',
3 => '9',
4 => '8',
5 => '7',
6 => '6',
7 => '5',
8 => '3',
),
'submit' => 'წაშლა',
)
MrCoder
10-10-2007, 10:42 AM
array (
'delete' =>
array (
0 => '12',
1 => '11',
2 => '10',
3 => '9',
4 => '8',
5 => '7',
6 => '6',
7 => '5',
8 => '3',
),
'submit' => 'წაშლა',
)


That is working as expected, as you can see "delete" is now an array containing all the ID's of the rows that need deleting.
Now you just have to do something like this..


foeach($_POST["delete"] as $key => $id)
{
// Do your mysql query
mysql_query("DELETE FROM dbtable WHERE id='".(int)$id."'") or die(mysql_error());

echo "The Row Number ".(int)$id." has been Successfully Removed";
}


You could do it with one mysql_query, but that would involve a more advanced approach.
LogicOpinion
10-10-2007, 10:51 AM
there is such error after submiting REMOVE Buttton:


Parse error: syntax error, unexpected T_AS in C:\xampp\htdocs\db\navi\remove.php on line 21


remove.php after modification:



<?php
echo "<pre>".var_export($_POST, true)."</pre>";
die();
mysql_connect("localhost", "root", "") or die ("Could not connect");
mysql_select_db("db1") or die ("Could not connect to DB");

if ($_POST['delete'])

{

$deleteID = $_POST['delete'];

foeach($_POST["delete"] as $key => $id)

{

mysql_query("DELETE FROM dbtable WHERE id='".(int)$id."'") or die(mysql_error());

echo "The Row Number ".(int)$id." has been Successfully Removed";

}

else

{

echo "please select at least one row, to delete it";

}

?>
LogicOpinion
10-10-2007, 11:51 AM
i realy do not understand what i miss :(
JoeHoldcroft
10-10-2007, 11:59 AM
You have a typo, change this line (21):
foeach($_POST["delete"] as $key => $id)
to this..
foreach($_POST["delete"] as $key => $id)
MrCoder
10-10-2007, 12:34 PM
Sorry, as Joe pointed out.
LogicOpinion
10-10-2007, 12:40 PM
so i did everything as u said.

here is the code of both files:

Form where i selecet CHECKBOXes And Press Remove Button To Remove Selected Rows:



<form enctype="multipart/form-data" action="index.php?goto=remove" method="post">
<?php
mysql_connect("localhost", "root", "") or die(mysql_error());
mysql_select_db("db1") or die(mysql_error());
$query = "SELECT * FROM dbtable ORDER BY id DESC";
$result = mysql_query($query) or die(mysql_error());

$i = 0;

echo "<table align=center width=98% class=maintxt border=0 cellpadding=0 cellspacing=2>";
print "<tr>";
print "<td bgcolor=#FFF8DC height=40>&nbsp;&nbsp;<B>№</B>&nbsp;&nbsp;</td>";
print "<td bgcolor=#FFF8DC align=center><B>ჯგუფი</B></td>";
print "<td bgcolor=#FFF8DC align=center><B>სახელი</B></td>";
print "<td bgcolor=#FFF8DC align=center><B>გვარი</B></td>";
print "<td bgcolor=#FFF8DC align=center><B>ქალაქი</B></td>";
print "<td bgcolor=#FFF8DC align=center><B>სკოლა ნომერი</B></td>";
print "<td bgcolor=#FFF8DC align=center><B>პროფესია</B></td>";
print "<td bgcolor=#FFF8DC align=center><B>საკონტაქტო ტელეფონი</B></td>";
print "<td bgcolor=#FFF8DC align=center><B>პირადი ნომერი</B></td>";
print "<td bgcolor=#FFF8DC align=center><B>ტრენინგის გავლის თარიღი</B></td>";
print "<td bgcolor=#FFF8DC align=center><B>შენიშვნა</B></td>";
print "<td bgcolor=#FFF8DC align=center>&nbsp;&nbsp;<B>select</B>&nbsp;&nbsp;</td>";
print "</tr>";

while($row = mysql_fetch_array($result))
{


if ($i == 0)

{
$bgcolor = '#DCDCDC';
$i = 1;
} else {
$bgcolor = '#f5f5f5';
$i = '0';
}

print "<tr>";
echo "<td height='25' bgcolor='$bgcolor' align=center>";
echo $row['id'];
print "</td>";
print "<td bgcolor='$bgcolor' align=center>";
echo $row['group_id'];
print "</td>";
print "<td bgcolor='$bgcolor' align=center>";
echo $row['name'];
print "</td>";
print "<td bgcolor='$bgcolor' align=center>";
echo $row['s_name'];
print "</td>";
print "<td bgcolor='$bgcolor' align=center>";
echo $row['city'];
print "</td>";
print "<td bgcolor='$bgcolor' align=center>";
echo $row['school_id'];
print "</td>";
print "<td bgcolor='$bgcolor' align=center>";
echo $row['proffesion'];
print "</td>";
print "<td bgcolor='$bgcolor' align=center>";
echo $row['telephone'];
print "</td>";
print "<td bgcolor='$bgcolor' align=center>";
echo $row['personal_id'];
print "</td>";
print "<td bgcolor='$bgcolor' align=center>";
echo $row['date'];
print "</td>";
print "<td bgcolor='$bgcolor' align=center>";
echo $row['note'];
print "</td>";
print "<td bgcolor='$bgcolor' align=center>";
print "<input name='delete[]' value='{$row['id']}' type='checkbox'>";
print "</td>";
print "</tr>";
}
?>
</table>
<table align=center width=98% class=maintxt border=0 cellpadding=0 cellspacing=2>
<tr>
<td align=left width=50><input type="submit" value="წაშლა" name="submit"></td>
</tr>
</table>
</form>




and this the code of Remove.php file:



<?php
echo "<pre>".var_export($_POST, true)."</pre>";
die();
mysql_connect("localhost", "root", "") or die ("Could not connect");
mysql_select_db("db1") or die ("Could not connect to DB");
$deleteID = $_POST['delete'];

if ($_POST['delete'])

{



foreach($_POST["delete"] as $key => $id)

{

mysql_query("DELETE FROM dbtable WHERE id='".(int)$id."'") or die(mysql_error());

echo "The Row Number ".(int)$id." has been Successfully Removed";

}
}
else

{

echo "please select at least one row, to delete it";

}

?>




After Selecting Rows Which Should Be deleted, i press Remove Button and when page goes to

http://localhost/db/index.php?goto=remove

it displays the same Code on the Page

See this:


array (
'delete' =>
array (
0 => '12',
1 => '11',
2 => '10',
3 => '7',
4 => '6',
5 => '5',
6 => '3',
),
'submit' => 'წაშლა',
)
MrCoder
10-10-2007, 04:40 PM
The following bit of code was just to debug your post values..


echo "<pre>".var_export($_POST, true)."</pre>";
die();


Remove these lines and it will work.
LogicOpinion
10-11-2007, 03:19 AM
Thank you man, its working..
Good Luck!
MrCoder
10-11-2007, 06:54 AM
Glad I could help.
noboost4you
06-23-2008, 02:09 PM
Just would like to add this thread helped me out as well.

Thanks!
Sheldon
06-24-2008, 03:16 AM
Just because i was bored, I went about it a different method to do it in one mysql query. I also added more checking and crap like that....


<?php

mysql_connect("localhost", "root", "") or die ("Could not connect");
mysql_select_db("db1") or die ("Could not connect to DB");

$back = "<a href=\"javascript:history.go(-1);\" title=\"Back\">Back</a>";

if(!empty($_POST['delete'])){
$n=1;
$num = count($_POST['delete']);
$sql = "DELETE FROM dbtable WHERE id IN(";
foreach($_POST['delete'] as $name => $value){
$id = (int)mysql_real_escape_string($value);
$ids .= $id;
$sql .= "'{$id}'";
$n++;
if($n < $num){
$sql .= ",";
$ids .= ",";
}
}
$sql .= ")";
$qry = @mysql_query($sql);
$num = @mysql_num_rows($qry);
if($num >= 1){
$s = NULL; if($num > 1){ $s = "s"; }
$output = "<p><strong>You have successfully deleted '{$num}' row{$s}.</strong></p>\n";
$output .= "<p><em>Rows: {$ids}</em>{$back}</p>\n";
}elseif(mysql_error()){
$output = "<p>There was a technical error: ". mysql_error() ."</p>\n";
$output .= "<p>{$back}</p>\n";
}else{
$output = "<p>Please select at least one row - {$back}</p>\n";
}
}else{
$output = "<p>Please select at least one row - {$back}</p>\n";
}

echo($output);

?>
MrCoder
06-24-2008, 09:22 PM
$sql = "DELETE FROM dbtable WHERE id IN(";
foreach($_POST['delete'] as $name => $value){
$id = (int)mysql_real_escape_string($value);
$ids .= $id;
$sql .= "'{$id}'";
$n++;
if($n < $num){
$sql .= ",";
$ids .= ",";
}
}
$sql .= ")";


Since I'm bored too I will throw a couple of different ways of building the query.

$ids = array();

foreach($_POST['delete'] as $name => $value)
$ids[] = (int)$value;

$sql = "DELETE FROM dbtable WHERE id IN(".implode(",", $ids).")";


Or..


$ids = "";

foreach($_POST['delete'] as $name => $value)
$ids .= (int)$value.",";

$sql = "DELETE FROM dbtable WHERE id IN(".substr($ids, 0, -1).")";