RE: [PHP-DB] Using an array(-ish) in SQL queries

[Bastien Koert]

DELETE FROM the_table WHERE `ID` IN(1,2,3,4,5,6) will work just fine. The 
trick is to be sure that
those records indeed are to be deleted. I prefer to mark the record as 
deleted for a time before permanent deletion. That way its recoverable 
should something really bad happen.

bastien

From: -{ Rene Brehmer }- <[EMAIL PROTECTED]>
To: [EMAIL PROTECTED],[EMAIL PROTECTED]
Subject: [PHP-DB] Using an array(-ish) in SQL queries
Date: Wed, 03 Nov 2004 01:29:20 +0100
X-posted to MySQL and PHP DB
Hi gang
Task at hand: deleting or selecting (same difference) several numbers of 
records using only 1 query.

My first version simply looped through all the ticked off IDs and ran a 
single query for each delete routine. I've still not suceeded in getting 
the delete queries to work on multiple tables at once, despite the column 
names being the same. But besides this:

My current version generates, for multi-select cases, queries like this:
DELETE FROM the_table WHERE `ID`='1' OR ID`='2' OR `ID`='3' OR `ID`='4' OR 
`ID`='5' OR `ID`='6'

or similar with the SELECT statement.
On some occasions this can result in a very large amount of OR statements, 
like for 50 IDs totally.

I've been reading through the MySQL manual and the comments in the select 
and delete parts, but cannot seem to find any mentioning of an easier way 
to do this. Or it's been deluting me cuz English is my second language, so 
the MySQL manual doesn't always make much sense to me.

I'm looking for something like passing on an array (as comma-seperated-list 
maybe), and then just do statements like:

DELETE FROM the_table WHERE `ID` ISIN(1,2,3,4,5,6)
Did I totally miss that part of the manual, or is it just not possible with 
MySQL ?

Now, for my script it doesn't really matter much which approach to use, but 
was more thinking performance wise it ought to be faster and less taxing 
for the server to parse an SQL statement that's closer to table structure, 
rather than the OR statements that has to be transformed first.

Sorry if I'm just a blind mouse that can't seem to find things in the MySQL 
manual. It's not really my best friend...

TIA
Rene
--
Rene Brehmer
aka Metalbunny
If your life was a dream, would you wake up from a nightmare, dripping of 
sweat, hoping it was over? Or would you wake up happy and pleased, ready to 
take on the day with a smile?

http://metalbunny.net/
References, tools, and other useful stuff...
Check out the new Metalbunny forums at http://forums.metalbunny.net/
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: [PHP-DB] Using an array(-ish) in SQL queries

[Jennifer Goodie]

 -- Original message --
From: -{ Rene Brehmer }- <[EMAIL PROTECTED]>
> Task at hand: deleting or selecting (same difference) several numbers of 
> records using only 1 query.
> 
> My first version simply looped through all the ticked off IDs and ran a 
> single query for each delete routine. I've still not suceeded in getting 
> the delete queries to work on multiple tables at once, despite the column 
> names being the same. But besides this:

Multi-table deletes are new to mySQL 4.0, so if you are running a 3.x release they 
won't work. 
http://dev.mysql.com/doc/mysql/en/DELETE.html 

> 
> My current version generates, for multi-select cases, queries like this:
> 
> DELETE FROM the_table WHERE `ID`='1' OR ID`='2' OR `ID`='3' OR `ID`='4' OR 
> `ID`='5' OR `ID`='6'
> 
> or similar with the SELECT statement.
[snip lots of stuff]
> DELETE FROM the_table WHERE `ID` ISIN(1,2,3,4,5,6)

use IN  http://dev.mysql.com/doc/mysql/en/Comparison_Operators.html#IDX1268

If you know all the values in the array are escaped and safe you can just use 
implode() to make the list for IN

$string = implode("','",$array);
$sql = "SELECT FROM $table WHERE col_name IN('$string')";
Notice I added single quotes around the string, that is because they will be missing 
since implode only sticks the string between array elements.

However, you'd need a join that makes sense for a multi-table delete.  I don't know if 
it will work with a union, I have never tried, maybe somone else will chime in.


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]