Page 1 of 1

Using array in SELECT statement

Posted: Sun Dec 08, 2002 11:25 am
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?

Posted: Sun Dec 08, 2002 1:21 pm
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");

Posted: Wed Dec 11, 2002 10:36 am
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.