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!

stackoverflow.com/questions/907806/… sound similar.

Nigel Ren
– Nigel Ren

2018-02-16 16:05:44 +00:00
Commented Feb 16, 2018 at 16:05 — Nigel Ren
– Nigel Ren, Commented Feb 16, 2018 at 16:05

trincot · Accepted Answer · 2018-02-16 16:02:10Z

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 · Accepted Answer · 2018-02-17 22:19:01Z

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, 2018 at 22:19

answered Feb 16, 2018 at 16:02

mcv

1,4703 gold badges19 silver badges42 bronze badges

2 Comments

Johannes Over a year ago

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!

mcv Over a year ago

@Johannes I noted an error in my code above omitted the category before IN. I also used sqlFiddle to display results above. It's a good tool to use to have people help you. UNsure how you only got one result. :(

Atik Hashmee · Accepted Answer · 2018-02-16 16:06:05Z

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, 2018 at 16:06

Atik Hashmee

3933 silver badges12 bronze badges

4 Comments

trincot Over a year ago

What is ||? And how would this work for any given array $category?

Atik Hashmee Over a year ago

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 .,

trincot Over a year ago

True for the first point, unless the relevant configuration has been set for it to mean string-concatenation.

Johannes Over a year ago

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.

Collectives™ on Stack Overflow

prepared statement where value is in array

3 Answers 3

Comments

2 Comments

4 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

2 Comments

4 Comments

Linked

Related