prepared statement where value is in array

Question

In a php page, I have an array, similar to this:

$category = array(16, 22, 23);

Then I am doing a database query with a prepared statement. I would like to get all rows where the field category contains one of the values from that $category array and where price is lower than a value stored in the variable $price.

Among others I read the answers to this question and tried to use find_in_set() as described there (and at a lot of other places), but somehow I can't make it work within the prepared statement. I tried this:

/*  database connection "$db" is established beforehand and works  */

if($ps = $db->prepare("
   SELECT id, product, category, price 
   FROM products
   WHERE price <= ? and find_in_set(?, category)
   ORDER BY id") {
     $ps->bind_param("ds", $price, $category); 
     $ps->execute();
     $ps->bind_result($id, $name, $cat, $pr);
     while($ps->fetch()) {
       /* ...  echo the results .....  */
     }
    $ps->free_result();
    $ps->close();
 }

But I get an empty result.

If I try to use "dd" or "di" instead of "ds" in the bind_param() line, I do get results, but the wrong ones - I get all rows with category 1.

I also tried to use category IN ? instead of find_in_set(?, category), but that won't work either.

What can I do to make that work? Any help appreciated!

https://stackoverflow.com/questions/907806/passing-an-array-to-a-query-using-a-where-clause sound similar. — Nigel Ren, Feb 16 '18 at 16:05

score 4 · Accepted Answer · answered Feb 16 '18 at 16:02

Two issues:

The list should be passed as second argument to find_in_set, so it should be:
```
find_in_set(category, ?)
```
That argument should be of type string (comma separated values). So first convert your array to such a string with implode:
```
$csv = implode(",", $category);
```

Code:

if($ps = $db->prepare("
   SELECT id, product, category, price 
   FROM products
   WHERE price <= ? and find_in_set(category, ?)
   ORDER BY id") {
     $csv = implode(",", $category);
     $ps->bind_param("ds", $price, $csv); 
     $ps->execute();
     $ps->bind_result($id, $name, $cat, $pr);
     while($ps->fetch()) {
       /* ...  echo the results .....  */
     }
    $ps->free_result();
    $ps->close();
}

mcv · Answer 2 · 2018-02-17T22:19:01.420

2

If you want to find values where $category belongs to a certain set try using IN. Just a note you cannot pass an array into a string like you have above.

Also don't forget to convert your array to a CSV string using implode

$category = array(16, 22, 23);
$cat_str = implode(",",$category);  //16,22,23

$ps = $db->prepare("
   SELECT id, product, category, price 
   FROM products
   WHERE price <= ? and category IN (?)
   ORDER BY id") {
$ps->bind_param("ds", $price, $cat_str);

edited Feb 17 '18 at 22:19

answered Feb 16 '18 at 16:02

mcv

1,380
3
16
41

Hmm - that only returns the rows from the first value in the string. But the way @trincot describes it works for me, so I am all set. Still, thanks for taking the time to answer! – Johannes Feb 16 '18 at 16:29
@Johannes I noted an error in my code above omitted the `category` before IN. I also used [sqlFiddle](http://sqlfiddle.com/#!9/08b368/1) to display results above. It's a good tool to use to have people help you. UNsure how you only got one result. :( – mcv Feb 16 '18 at 18:56

score 0 · Answer 3 · answered Feb 16 '18 at 16:06

0

if i tried the same case , then i would probably have done it like this

SELECT id, product, category, price 
 FROM products
WHERE category = '16' || category = '22' || category = '23' and price < $price`

i am assuming your $category array variable is fixed , it's not dynamically appending value .

I answered only your query part . i'd hope you have php coding convention idea rather than putting the entire statement in the if statement .

if still has anything to ask about , please go ahead

answered Feb 16 '18 at 16:06

Atik Hashmee

393
3
12

What is `||`? And how would this work for any given array `$category`? – trincot Feb 16 '18 at 16:09
sir , || is equivalent to OR , and i probably i have mentioned above that i am assuming if he has finite array value , if his array grows dynamically then he might have to follow your way ., – Atik Hashmee Feb 16 '18 at 16:13
True for the first point, unless the [relevant configuration](https://dev.mysql.com/doc/refman/5.7/en/sql-mode.html#sqlmode_pipes_as_concat) has been set for it to mean string-concatenation. – trincot Feb 16 '18 at 16:26
well, that's basically how I did it before I had to compare more than two values. But with more values that's getting rather complicated and additionally, my array/s now have different number/s of values, so I can't use that. – Johannes Feb 16 '18 at 16:31

prepared statement where value is in array

3 Answers3