how to build a sql query using the content of a variable

Question

Hi ,I'm trying to build a query using php and mysql,

 $query = "select * from products where product_name = '$item_name'";

this works when $item_name holds only one name, but $item_name is an array and based on the user's interaction can contain multiple names, how can I make the query to run for multiple name and get the resulted rows.

Thanks in advance

Answer 1

$vals = implode(',',$item_name);
$query = "select * from products where product_name in (".$vals.");";

Give that a try.

Answer 2

foreach($item_name as $name) {
$query = "select * from products where product_name = '$name'";
//whatever you want to do with the query here
}

something like that ought to do it.

Answer 3

$query = "select * from products where product_name in(";
foreach($item_name as $name) 
{
    $query .= "'" . $item_name . "', ";
}

$query = substr($query, 0, strlen$query) - 2);
$query .= ");";

First answer (by inkedmn) is really the best one though

Answer 4

Here's how you could build a safe list of names for inserting into an IN clause...

if (is_array($names) && count($names))
{
    $filter="('".implode("','" array_map('mysql_real_escape_string', $names))."')";
    $sql="select * from products where product_name in $filter";

    //go fetch the results
}
else
{
    //input was empty or not an array - you might want to throw an
    //an error, or show 'no results'
}

array_map returns the input array of names after running each name through mysql_real_escape_string to sanitize it. We implode that array to make a nice list to use with an IN clause.

You should always ensure any data, particularly coming directly from the client side, is properly escaped in a query to prevent SQL injection attacks.

Answer 5

Based on inkedmn's response (which didn't quote the item names):

$query = 'select * from products where product_name in ("' . implode('", "', $item_name ) . '")';

Although you may be better with a fulltext search.

http://dev.mysql.com/doc/refman/5.1/en/fulltext-search.html