Skip to main content
A
Anonymous
April 30, 2010
Question

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

  • April 30, 2010
  • 1 reply
  • 1211 views

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.

This topic has been closed for replies.

1 reply

A
Anonymous
May 5, 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.

A
Anonymous
May 5, 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.

A
Anonymous
May 6, 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.

Terms & ConditionsAccessibility statement
Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices