How do i do a loop to delete multiple rows in my database?

Report · Apr 30, 2010

Hi,

I am trying to delete multiple rows in my database by their id. Firstly I have done a search on the database ($rsDr) to select these ids based on certain criteria, and now i want to run the delete code for every row found in the search.

I have tried and tried do ... while loops with the code but am getting no where, each time only one record is deleted not every record. Any help or advise would be much appreciated.

The code I am using for the delete is :

if (isset($row_rsDr['rentalsid']) && ($row_rsDr['rentalsid'] != "")) {
$deleteSQL = sprintf("DELETE FROM rentalstable WHERE rentalsid=%s",
GetSQLValueString($row_rsDr['rentalsid'], "int"));

mysql_select_db($database_connDelete, $connDelete);
$Result1 = mysql_query($deleteSQL, $connDelete) or die(mysql_error());
}

and i have been trying to use

$row_rsDr = mysql_fetch_assoc($rsDr);

from the search in a do ... while loop so that a delete is done for every id found in the recordset.

I look forward to any suggestions.

Report · May 04, 2010

U have to put all the ID that u want to delete in an array using implode function

$ID_list = mysql_real_escape_string(implode(',', $_POST['delete']));

The value for $_POST['delete'] is the value of ID u want to delete. It will store the sequence of id to be deleted (ex: 1,3,5,6,.....). Then execute this query below.

$delete_data = mysql_query("DELETE FROM table_name WHERE ID IN ($ID_list)") or die (mysql_error());

*replace the bold words with ur table name and the id row in that table.

Report · May 05, 2010

Thank you so much for that, the only thing i´m not sure about is the value that i put in $POST['delete'].

I have done a search on the database to retrieve the ids that I want to delete, the search code is below:

mysql_select_db($database_connSearch, $connSearch);
$query_rsDs = sprintf("SELECT detailstable.propid, listedbytable.listedid, detailstable.detailsid, detailstable.location, detailstable.listedby, photossale.propsaleid, photossale.photoid, photossale.photo1, photossale.photo2, photossale.photo3, photossale.photo4, photossale.photo5, photossale.photo6, photossale.photo7, photossale.photo8, photossale.photo9 FROM listedbytable, detailstable, photossale WHERE listedid = %s AND listedbytable.listedid = detailstable.listedby AND detailstable.detailsid = photossale.propsaleid", GetSQLValueString($varDs_rsDs, "int"));
$rsDs = mysql_query($query_rsDs, $connSearch) or die(mysql_error());
$row_rsDs = mysql_fetch_assoc($rsDs);
$totalRows_rsDs = mysql_num_rows($rsDs);

Firstly I want to delete all rows that are retrieved from this query by detailsid in one table, then after that I will use the photoid to do the delete on another table and so on,

in the first delete what value would I use to put instead of $POST['delete'] so that all the rows by detailsid are deleted?

Looking forward to your reply, thank you.

Report · May 05, 2010

I see that detailstable and photossale tables are connected with detailsid and propsaleid. But what listedbytable is used for? You can make recordset to view data using table detailstable and make its propid as the value for $_POST['delete']. Im not so familiar with cross table delete but you can try as below.

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

$list = mysql_real_escape_string(implode(',', $_POST['delete']));

$delete = mysql_query("DELETE detailstable.*, photossale.* FROM detailstable a INNER JOIN photossale b ON a.detailsid = b.propsaleid WHERE a.propid IN ($list)") or die(mysql_error());

}

* this is the coding to delete the record based on user select.

Report · May 06, 2010

Hi, thanks for the reply, again.

I have tried the code you suggested and changed the $_POST['delete'] to the following:

if(isset($row_rsDs['propid']) && !empty($row_rsDs['propid'])) {
     $list = mysql_real_escape_string(implode(',', $row_rsDs['propid']));
     $delete = mysql_query("DELETE detailstable.*, photossale.* FROM detailstable
 a INNER JOIN photossale b ON a.detailsid = b.propsaleid WHERE a.propid IN ($list)") or die(mysql_error());
}

Have i done right in using my recordset $rowrsDs['propid'] instead of the $_POST['delete']??
what to put as $_POST['delete']is the part that i don´t follow.

Basically, I have a table of agents(listedbytable), a table of properties(detailstable) and a table of photo
ids(photosale) all joined. The recordset I use searches for the ids of each row for a particular agent 
(ie all the detailsid's and photosaleid's) then I use the results of this search to do a delete on the
detailstable by detailsid then another delete on the photossale table by photosaleid, but i want to delete
all rows and thats where you came in! I am actually using my first recordset that gets all the ids for a
particular agent to populate the delete sql and thats the part that i don´t understand.

I tried the code that you suggested with my $row_rsDs['propid'] as $_POST['delete'] and it came up with an
error:
Warning:  implode() [function.implode]:
 Invalid arguments passed in J:\xampp\htdocs\..... on line 110

You have an error in your SQL syntax; check the manual that corresponds 
to your MySQL server version for the right syntax to use near ')' at 
line 1 

I'm not sure why i´ve got this error or if in fact i've amend the code correctly, my 
initial code did work but only deleted one record from the first query and not all of them.
Thank you for your patience with me, i appreciate your help and hope to hear from you again.

Report · May 06, 2010

I think I gotcha now. U can use your query above to retrieve the id that u want to delete.

mysql_select_db($database_connSearch, $connSearch);
$query_rsDs = sprintf("SELECT detailstable.propid, listedbytable.listedid, detailstable.detailsid, detailstable.location, detailstable.listedby, photossale.propsaleid, photossale.photoid, photossale.photo1, photossale.photo2, photossale.photo3, photossale.photo4, photossale.photo5, photossale.photo6, photossale.photo7, photossale.photo8, photossale.photo9 FROM listedbytable, detailstable, photossale WHERE listedid = %s AND listedbytable.listedid = detailstable.listedby AND detailstable.detailsid = photossale.propsaleid", GetSQLValueString($varDs_rsDs, "int"));
$rsDs = mysql_query($query_rsDs, $connSearch) or die(mysql_error());
$row_rsDs = mysql_fetch_assoc($rsDs);
$totalRows_rsDs = mysql_num_rows($rsDs);

[Firstly I want to delete all rows that are retrieved from this query by detailsid in one table, then after that I will use the photoid to do the delete on another table and so on.]

But u need to change it a bit. Just comment it with // or delete the line $row_rsDs = mysql_fetch_assoc($rsDs);

For the delete query, change to this:

if($totalRows_rsDs > 0) {
     while ($row_rsDs = mysql_fetch_assoc($rsDs)) {
          $list = mysql_real_escape_string(implode(',', $row_rsDs['propid']));
     }
     $delete = mysql_query("DELETE detailstable.*, photossale.* FROM detailstable
 a INNER JOIN photossale b ON a.detailsid = b.propsaleid WHERE a.propid IN ($list)") or die(mysql_error());
}

So the full code should be like below:

mysql_select_db($database_connSearch, $connSearch);
$query_rsDs = sprintf("SELECT detailstable.propid, listedbytable.listedid, detailstable.detailsid, detailstable.location, detailstable.listedby, photossale.propsaleid, photossale.photoid, photossale.photo1, photossale.photo2, photossale.photo3, photossale.photo4, photossale.photo5, photossale.photo6, photossale.photo7, photossale.photo8, photossale.photo9 FROM listedbytable, detailstable, photossale WHERE listedid = %s AND listedbytable.listedid = detailstable.listedby AND detailstable.detailsid = photossale.propsaleid", GetSQLValueString($varDs_rsDs, "int"));
$rsDs = mysql_query($query_rsDs, $connSearch) or die(mysql_error());
$totalRows_rsDs = mysql_num_rows($rsDs);

if($totalRows_rsDs > 0) {
     while ($row_rsDs = mysql_fetch_assoc($rsDs)) {
          $list = mysql_real_escape_string(implode(',', $row_rsDs['propid']));
     }
     $delete = mysql_query("DELETE detailstable.*, photossale.* FROM detailstable
 a INNER JOIN photossale b ON a.detailsid = b.propsaleid WHERE a.propid IN ($list)") or die(mysql_error());
}

Report · May 06, 2010

Hi again,

Thank you so much, I do now understand the code and what it is supposed to do, sorry for not explaing fully exactly what I meant. However I am still having problems, I am getting the following error:

Warning: implode() [function.implode]: Invalid arguments passed in J:\xampp\htdocs\propertypages\adminDeleteAgent.php on line 77

Warning: implode() [function.implode]: Invalid arguments passed in J:\xampp\htdocs\propertypages\adminDeleteAgent.php on line 77

Warning: implode() [function.implode]: Invalid arguments passed in J:\xampp\htdocs\propertypages\adminDeleteAgent.php on line 77
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ')' at line 2

I have pasted my code below which is the code you suggested (the only thing i have changed is the first line - the connection i am now using is one that allows for search and delete in the database).

I do hope that you can help, especially as i now understand how to achieve the multiple deletes thanks to you, just not sure why the error is happeneing and obviously the error is preventing the delete code from working.

line 77 is code: $list = mysql_real_escape_string(implode(',', $row_rsDs['propid']));

Code:

mysql_select_db($database_connDelete, $connDelete);
$query_rsDs = sprintf("SELECT detailstable.propid, listedbytable.listedid, detailstable.detailsid, detailstable.location, detailstable.listedby, photossale.propsaleid, photossale.photoid, photossale.photo1, photossale.photo2, photossale.photo3, photossale.photo4, photossale.photo5, photossale.photo6, photossale.photo7, photossale.photo8, photossale.photo9 FROM listedbytable, detailstable, photossale WHERE listedid = %s AND listedbytable.listedid = detailstable.listedby AND detailstable.detailsid = photossale.propsaleid", GetSQLValueString($varDs_rsDs, "int"));
$rsDs = mysql_query($query_rsDs, $connSearch) or die(mysql_error());
$totalRows_rsDs = mysql_num_rows($rsDs);

// delete all rows found in the above recordset firstly from details table then from photos table
if($totalRows_rsDs > 0) {
     while ($row_rsDs = mysql_fetch_assoc($rsDs)) {
          $list = mysql_real_escape_string(implode(',', $row_rsDs['propid']));
     }
$delete = mysql_query("DELETE detailstable.*, photossale.* FROM detailstable
a INNER JOIN photossale b ON a.detailsid = b.propsaleid WHERE a.propid IN ($list)") or die(mysql_error());
}

Looking forward to hearing from you

Report · May 06, 2010

Can u please change to this

while ($row_rsDs = mysql_fetch_assoc($rsDs)) {
echo $row_rsDs['propid'].',';
}

And paste here the results. And also try this one:

while ($row_rsDs = mysql_fetch_assoc($rsDs)) {
$list = implode(',', $row_rsDs['propid']);
}

Report · May 07, 2010

Hi,

I´ve tried both the options below and pasted the results, still no success though but perhaps the result I have pasted can narrow down the reasons for it not working, I hope so.

FIRST OPTION TRIED:

while ($row_rsDs = mysql_fetch_assoc($rsDs)) {

echo $row_rsDs['propid'].',';

}

RESULT OF FIRST OPTION(note sun 58, 42,62 are the correct echos for the $row_rsDs['propid']):

sun58,sun42,sun62, you have an error in your SQL syntax; check the manual that corresponds to your MySQL server

version for the right syntax to use near ')' at line 2

SECOND OPTION TRIED:

while ($row_rsDs = mysql_fetch_assoc($rsDs)) {

$list = implode(',', $row_rsDs['propid']);

}SECOND OPTION RESULT: No error message just a blank screen (the delete was not successful).

So 2 different results and different from the original error.

I have some time today so now that I understand the concept of what you are trying to do I will

try having a look at it myself too and see if i can try to get somewhere, obviously if I do i´ll let you know.

Thank you again for your time, looking forward to hearing from you.

Report · May 07, 2010

Is propid is the primary key in your table? Because primary key should be an integer and auto incerement, not a varchar(sun58,sun42,sun62)

Report · May 07, 2010

Hi,

the primary key is detailsid and is int auto incr.

I´ve managed to get rid of the implode error by adding ['propid'] after while ($row_rsDs as below:

if($totalRows_rsDs > 0) {
     while ($row_rsDs['propid'] = mysql_fetch_assoc($rsDs)) {
          $list = mysql_real_escape_string(implode(',', $row_rsDs['propid']));
          echo 'list is: ' . $list . '<br />';
     }
$delete = mysql_query("DELETE detailstable.*, photosale.* FROM detailstable a INNER JOIN photossale b ON a.detailsid = b.propsaleid WHERE a.propid IN ($list)") or die(mysql_error());
}

I am still getting an error but i think it may be more to do with the delete sql now and i´ve also added an echo to see what $list was, results below:

list is: sun58,736,756,1,736,756,634,Default.jpg,Default.jpg,Default.jpg,Default.jpg,Default.jpg,Default.jpg,Default.jpg,Default.jpg,Default.jpg
list is: sun42,736,754,1,736,754,632,Default.jpg,Default.jpg,Default.jpg,Default.jpg,Default.jpg,Default.jpg,Default.jpg,Default.jpg,Default.jpg
list is: sun62,736,757,1,736,757,635,Default.jpg,Default.jpg,Default.jpg,Default.jpg,Default.jpg,Default.jpg,Default.jpg,Default.jpg,Default.jpg
Unknown table 'detailstable' in MULTI DELETE

As far as the sql detailstable is the correct table so i think that the sql delete could possible not be just right.

Until i can get the code to work i won´t know if the delete actually does what it is supposed to so i am going to look at the delete sql next, if you have any thoughts on that or the delete i really look forward to your input as you have helped me a great deal so far and i feel we get a step closer each day.

Report · May 11, 2010

Resolved!!

After looking into the sql more i have managed to do what i wanted to do in a different way, for anyone interested:

Rather than find the ids that i wanted to delete in a search then try to do a delete from those ids i removed the search and just did a delete as follows:

// delete all rows that are listed by the same agent

if (isset($_GET['listedid']) && ($_GET['listedid'] != "")) {
$deleteSQL = sprintf("DELETE FROM detailstable WHERE EXISTS
(select listedbytable.listedid from listedbytable where listedbytable.listedid=detailstable.listedby and listedbytable.listedid=%s)",
GetSQLValueString($_GET['listedid'], "int"));

mysql_select_db($database_connDelete, $connDelete);
$Result1 = mysql_query($deleteSQL, $connDelete) or die(mysql_error());
}

I also did a similar delete on the photo table and it does delete all the rows i wanted to delete.

Thanks again for you help in getting there.