Multiple DELETE from MYSQL with a loop?

greg posted this at 20:30 — 17th August 2007.

Joined: Nov 2005

I am making a loop to delete multiple items from MYSQL

it's from URL data ($_GET) so the query is in a foreach loop

eg
(I know I havent put the correct quotes in as it looks messy in here)

foreach($_GET['urldata'] as $var1=>$var2)
{
$mysql= mysql_query("DELETE FROM tablename WHERE var1="$var1" AND var2="$var2" ")
}

firstly, will MYSQL query in that loop close its link to the DB on each loop for each item? or will it be efficient in its loop by keeping it open until loop finished?
if not, what is the best way?

the other issue I have
I didn't want to use a SELECT QUERY to first check if all entries actually exist in the DB, as this is then TWO queries to the DB for the same items

So is it possible to put in that loop something that on each loop will identify that there wasn't a DB match, and assign the data for the current loop ($var1 and $var2) to something it will later remember and then continue looping?

so in the loop something like

START OF LOOP
DB QUERY

$exists_check = mysql_affected_rows();
if ($exists_check <1)
{
$var1[] = $var1
$var2[] = $var2
}

BACK TO START OF LOOP

then after the loop has finished, and all the DB entries that where successfully matched where deleted I can use all the $var1[] and $var2[]

to echo them to the user and say these where not removed etc

I dont know if the MYSQL QUERY will exit on a false query, if the $var1 or $var2 didnt match at any point anything in the DB will it stop?
or continue through the loop as I need it to?

cheers!

teammatt3 posted this at 17:14 — 18th August 2007.

He has: 2,102 posts

Joined: Sep 2003

Quote: firstly, will MYSQL query in that loop close its link to the DB on each loop for each item? or will it be efficient in its loop by keeping it open until loop finished?
if not, what is the best way?

I am pretty sure MySQL closes its link at the end of the page automatically.

Quote: So is it possible to put in that loop something that on each loop will identify that there wasn't a DB match, and assign the data for the current loop ($var1 and $var2) to something it will later remember and then continue looping?

I think all you would need is that delete query, and mysql_affected_rows(). Something like

<?php
foreach($_GET['urldata'] as $var1=>$var2)
{
  $sql = \"DELETE...FROM...WHERE\";

  $result = mysql_query($sql);

  if(mysql_affected_rows($result) == 0) // nothing was deleted
  {
      $delete_errors[$var1] = TRUE; // flag it
  }
}

// print out error for each item in the $delete_errors array
foreach($delete_errors as $var1 => $var2)
{
  echo \"error with $var1\";
}
?>

I didn't check my code, but I think you can get the idea of what to do from it. Run the query, check to see if any rows were affected, if 0 rows were affected, make note of it in an array, and print out the array when the other loop is done.

Quote: I dont know if the MYSQL QUERY will exit on a false query, if the $var1 or $var2 didnt match at any point anything in the DB will it stop?
or continue through the loop as I need it to?

mysql_query returns false on a bad query, but it does not exit the loop because of it (although you could write some code to do that if you wanted).

greg posted this at 01:13 — 19th August 2007.

He has: 1,581 posts

Joined: Nov 2005

yes, thanks for those confirmations

That code works fine apart from one thing

if the if(mysql_affected_rows($result) == 0) never gets executed (i.e. it's never 0)
then the $delete_errors[$var1] = TRUE is always empty
and then the foreach further down returned an error
Warning: Invalid argument supplied for foreach() in /etc/etc/etc line 10

I noticed this about foreach. if the $var you use in the foreach is empty, it always returns an error
maybe there is something I am missing or don't know?

also once that page is complete and files selected for deletion are successfully deleted there is a css button to go to another page
if I go to that page and then click the browser back button
I get that error
Warning: Invalid argument supplied for foreach() in /etc/etc/etc line 10

to avoid it I just put an IF clause in before the foreach, which worked

but then of course any user playing with the url or clicking back still gets that error for the FOREACH that is used on the mysql query

So I had to put a full clause in for the whole page (global var assigned to find where user came from etc)

Would be interesting to learn how to avoid that
I cannot find anything on google or php net

cheers

teammatt3 posted this at 02:45 — 19th August 2007.

He has: 2,102 posts

Joined: Sep 2003

Quote: if the if(mysql_affected_rows($result) == 0) never gets executed (i.e. it's never 0)
then the $delete_errors[$var1] = TRUE is always empty
and then the foreach further down returned an error
Warning: Invalid argument supplied for foreach() in /etc/etc/etc line 10

That's because I gave you crappy code (hey, I'm in a hotel room, it's hard to write code in here). mysql_affected_rows doesn't need that $result argument, so take it out. Also took out == 0 and replaced it with less than 1 (mysql_affected_rows doesn't always return 0 when it doesn't affect rows)

<?php
 if(mysql_affected_rows() < 1) // nothing was deleted
  {
      $delete_errors[$var1] = TRUE; // flag it
  }
?>

That hopefully will solve most of the errors you are getting.

FrankR posted this at 02:19 — 27th August 2007.

He has: 45 posts

Joined: Oct 2006

You really need to run all your user input through verification before passing it to a SQL query. At least use mysql_escape_string() to escape the strings to avoid a SQL injection attack that could devastate your data integrity and confidentiality.

Author of SQL Converter for Excel, which is an Excel add-in for converting data to MySQL.