Using array in SELECT statement

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
f1nutter
Forum Contributor
Posts: 125
Joined: Wed Jun 05, 2002 12:08 pm
Location: London

Using array in SELECT statement

Post by f1nutter »

I would like to create a random link to books / videos / games on Amazon. I am going to store my top 12 items in a database, and update it as I want.

I would like to select 4 different items to show on my front page at random. I can generate 4 different numbers from 12 like this:

Code: Select all

<?php
$num_pick_from = 12;
$num_to_pick = 4;

// Load array with numbers to pick from
for ($i = 0 ; $i < $num_pick_from ; $i++)
{
 $input[] = $i;
}

// pick random numbers / keys
$rand_nums = array_rand($input , $num_to_pick);
?>
This returns the numerical keys, which is OK, but it is stored in an array. I then need to extract the numbers and create the right string to use with the database.

Code: Select all

SELECT * FROM shop WHERE id=4 OR id=8 OR id=3 OR id=1
This seems like a lot of overheads. Is there a way of using the array (or AN array) in the query. I remember seeing something which reduced the WHERE clause but I can't find it.

Any ideas?
User avatar
hob_goblin
Forum Regular
Posts: 978
Joined: Sun Apr 28, 2002 9:53 pm
Contact:

Post by hob_goblin »

i'd do something like this:

Code: Select all

$query = "SELECT * FROM table WHERE ";

for($i = 0; $i <= 3; $i++)&#123;
if($i == "3")&#123;
$query .= "num = '$i'";
&#125; else &#123;
$query .= "num = '$i' OR";
&#125;
&#125;

$result = mysql_query("$query");
f1nutter
Forum Contributor
Posts: 125
Joined: Wed Jun 05, 2002 12:08 pm
Location: London

Post by f1nutter »

Found what I was looking for. If its any help to any one else, this

Code: Select all

SELECT * FROM shop WHERE id=4 OR id=8 OR id=3 OR id=1
can be re-written as

Code: Select all

SELECT * FROM shop WHERE id IN (4, 8, 3, 1);
Shorter and easier to read. It still needs PHP to change the array, but that shouldn't be so hard now.

N.B. Use single quotes for strings.
Post Reply