Passing an array to mysql

Question

I currently have an array of values (business_id's)

lets say its

Array
(
[0] => 12
[1] => 14
[2] => 15
)

What is the best way to use each of these value to get the associated row information for each business_id from the database.

Can it be one in one query is my question. Obviously, you could do

$q = "select * from business where business_id = 12";
$rs = mysql_query($q);

Answer 1

$ids = array(1, 2, 3, 4);

$ids = join(', ', $ids);
$query = "SELECT * FROM business WHERE business_id IN ($ids)";
// $query => SELECT * FROM business WHERE business_id IN (1, 2, 3, 4)

The usual SQL injection warnings still apply, you may want to loop through the ids first to validate or escape them. Also, if you expect strings instead of numbers, use this:

$ids = array('a', 'b', 'c', 'd');

$ids = join("', '", $ids);
$query = "SELECT * FROM business WHERE business_id IN ('$ids')";
// $query => SELECT * FROM business WHERE business_id IN ('a', 'b', 'c', 'd')

Answer 2

Use a SQL IN operation:

$sql = 'SELECT * FROM business WHERE business_id IN (' . implode(',', $values) . ')';
$rs = mysql_query($sql);

Note: If the values come from the client they should be sanitized, either with mysql_real_escape_string() or, because these are numbers, you could use intval(). For example:

$ids = array_map('intval', $values);
$sql = 'SELECT * FROM business WHERE business_id IN (' . implode(',', $ids) . ')';
$rs = mysql_query($sql);

Answer 3

You could join the array using a comma as the separator, and use the SQL IN statement.

$businessIds = join(",", $array);
$q = "select * from business where business_id IN ($businessIds)";
$rs = mysql_query($q);