Using an array(-ish) in SQL queries

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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


[Index of Archives]     [PHP Home]     [PHP Users]     [Postgresql Discussion]     [Kernel Newbies]     [Postgresql]     [Yosemite News]

  Powered by Linux