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

Frank Shearar · Accepted Answer · 2012-06-12 09:23:06Z

6

$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", '');

edited Jun 12, 2012 at 9:23

answered May 18, 2010 at 7:56

Frank Shearar

17.2k8 gold badges72 silver badges96 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

nikc.org Over a year ago

You can accomplish the same with "'" . implode("','", $search_array) . "'", although it is perhaps not as elegant as your solution :-)

Frank Shearar Over a year ago

Oh nice, I hadn't even thought of that!

Alexander Konstantinov Over a year ago

Nice solution, but note that this won't work as expected in PHP before 5.3, because array_reduce() accepts only integer as a third parameter in 5.2 and earlier. As a result the string will be like '0, "one", "two", ...'

Frank Shearar Over a year ago

Thanks for the additional info, Alexander. I only started using array_reduce() from 5.3 onwards, so I didn't know that!

Alexander Konstantinov · Accepted Answer · 2010-05-18 07:59:02Z

4

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

answered May 18, 2010 at 7:59

Alexander Konstantinov

5,4961 gold badge29 silver badges31 bronze badges

Comments

Manos Dilaverakis · Accepted Answer · 2010-05-18 07:56:46Z

0

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?

answered May 18, 2010 at 7:56

Manos Dilaverakis

5,8794 gold badges35 silver badges57 bronze badges

2 Comments

knittl Over a year ago

please mysqli_real_escape_string the values in the array first

ThinkingInBits Over a year ago

I haven't added any injection protection first, just getting the basic query working.

Mark Baker · Accepted Answer · 2010-05-18 07:57:07Z

0

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.

answered May 18, 2010 at 7:57

Mark Baker

213k34 gold badges354 silver badges390 bronze badges

Comments

Sarfraz · Accepted Answer · 2010-05-18 07:57:13Z

0

...

$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";

answered May 18, 2010 at 7:57

Sarfraz

384k81 gold badges561 silver badges613 bronze badges

Comments

Andre · Accepted Answer · 2010-05-18 07:58:16Z

0

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')

answered May 18, 2010 at 7:58

Andre

1,10711 silver badges22 bronze badges

Comments

ThinkingInBits · Accepted Answer · 2010-05-18 08:07:20Z

0

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

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

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

    }

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

answered May 18, 2010 at 8:07

ThinkingInBits

11.5k8 gold badges59 silver badges84 bronze badges

Comments

Francisco Soto · Accepted Answer · 2010-05-18 09:40:51Z

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";

Collectives™ on Stack Overflow

How can I use an array within a SQL query

8 Answers 8

4 Comments

Comments

2 Comments

Comments

Comments

Comments

Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

8 Answers 8

4 Comments

Comments

2 Comments

Comments

Comments

Comments

Comments

Comments

Related