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

Author

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

Jennifer Goodie

2004-11-02, 8:55 pm

-------------- Original message ----------------------
From: -{ Rene Brehmer }- <metalbunny@metalbunny.net>
> 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/C...rs.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.

-{ Rene Brehmer }-

2004-11-03, 3:55 am

At 02:37 03-11-2004, Jennifer Goodie wrote:
>[snip]
>[snip lots of stuff]
>
>use IN http://dev.mysql.com/doc/mysql/en/C...rs.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.

thanks a whole bunch ... can't believe how close I was ... and I couldn't
even find it in the manual :-/

and for some reason I've never thought of implode :-s ... anyways, live and
learn ...

thanks :)

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/

Sponsored Links