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 one step might look like this:

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

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

Answer 2

Use

and c.value IN (implode(', ', $search_array))

$search_array is an array that you are directly concatenating into a string. You need to turn it into a string before doing that.

Also, why are you not echoing your sql statement to see that you're actually feeding to MySQL?

Answer 3

IN expects a comma-separated list of values, (quoted if they are strings). What does your value of $this->search_string contain? And exploding it into an array will NOT work... try echoing your $query to see exactly what you get.

Answer 4

...

$search_array = explode(" ",$this->search_string);
$search = implode(",", $search_array);
$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)
          group by p.id
          having count(c.value) >= $tag_count";

Answer 5

I'm not a PHP developer so excuse me if I miss something, but the array values should be sparated by commas and in single quotes:

WHERE c.value IN ('a','b','c')

Answer 6

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) ..."

Answer 7

You are not populating your IN statement in the right way, you have two choices, either pass the search string separated by commas, or do it yourself, like in :

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

for ($i = 0; $i < count($search_array); $i++)
{
   $search_array[$i] = "'" . $search_array[$i] . "'";
}

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

$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 ($list)
          group by p.id
          having count(c.value) >= $tag_count";

Answer 8

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

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

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

    }

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