How can I use an array within a SQL query

Question

So I'm trying to take a search string (could be any number of words) and turn each value into a list to use in the following IN statement) in addition, I need a count of all these values to use with my having count filter

$search_array = explode(" ",$this->search_string);
$tag_count = count($search_array);

$db = Connect::connect();
$query = "select p.id
          from photographs p
          left join photograph_tags c
          on p.id = c.photograph_id
          and c.value IN ($search_array)
          group by p.id
          having count(c.value) >= $tag_count";

This currently returns no results, any ideas?

Solution:

$search_array = explode(" ",$this->search_string);

foreach ($search_array as $key => $value) {

    $new_search_array[] = "'$value'";

}

$search_string = implode(',', $new_search_array);

This gives me a comma separated list

Answer 1

$search_array = implode(",", $search_array);

because IN takes a comma separated list of values. (But you need to ensure $search_array's contents are quoted, if they're words.)

Doing it in one step might look like this:

function quoteAndComma($result, $each) {
  return $result . ', "'.$each.'"';
}

$search_array = array_reduce($search_array, "quoteAndComma", '');

Answer 2

You should build a string from this array first:

// Don't forget to escape the data!
$search_array = array_map('mysql_real_escape_string', $search_array);

// Convert array to a string like "'one', 'two', ..."
$search_values = "'" . implode("', '", $search_array) . "'";

// Build a query
$query = "select ... c.value IN ($search_values) ..."