Using Array Values in Mysql Query

Question

I have a query to select a limited result based on a set parameter:

$query = $this->db->query ("
    SELECT 
        p.product_id, 
        p.quantity 
    FROM {$this->prefix}product p 
    LEFT JOIN {$this->prefix}product_to_category pc 
        ON (p.product_id = pc.product_id) 
    WHERE pc.category_id = '3' 
    AND p.status = '1' 
    ORDER BY p.quantity DESC 
    LIMIT 0, 4");

This returns the 4 products with the highest quantity in stock where the product's category_id = 3.

I'd like to have this use an array of category id's as opposed to a static one. ie:

$categories = array(2, 6, 22, 33, 34, 83, 220, 222, 886, 897);

Is this possible?

Jim · Accepted Answer · 2013-09-20 12:55:05Z

7

You can convert the array to a string and use that in your query. Please note that the below assumes that $categories are already safe and wont contain malicious input. If this is not the case you'll need to clean the input.

$categoriesClause = implode(",",$categories);

$query = $this->db->query ("
    SELECT 
        p.product_id, 
        p.quantity 
    FROM {$this->prefix}product p 
    LEFT JOIN {$this->prefix}product_to_category pc 
        ON (p.product_id = pc.product_id) 
    WHERE pc.category_id IN ($categoriesClause)
    AND p.status = '1' 
    ORDER BY p.quantity DESC 
    LIMIT 0, 4");

answered Sep 20, 2013 at 12:55

Jim

22.7k6 gold badges55 silver badges81 bronze badges

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

8 Comments

Anthony Over a year ago

This also assumes the array values are already quoted, unfortunately. You'll need to add an array_walk before imploding to cover that.

Jim Over a year ago

@Anthony The askers example was a list of ints. If it was an array of strings you could use $categoriesClause = "'" . implode("','",$categories) . "'";

Jim Over a year ago

(assuming it's non empty)

Anthony Over a year ago

the OP also wraps the category_id in single quotes in his example query and imploding with the quote marks is always a nightmare in my experience. Check out my answer!

secondman Over a year ago

Works perfect. Is there a way to return ONLY one product per each category, ie: the greatest quantity of each category? Right now the query is sorting only by quantity so I'm getting multiple products from the same category since they have greater quantities than products from a different category.

|

Amal · Accepted Answer · 2013-09-20 12:55:48Z

2

You can use IN clause.

WHERE pc.category_id in (2, 6, 22, 33, 34, 83, 220, 222, 886, 897);

edited Sep 20, 2013 at 12:55

Amal

76.8k18 gold badges133 silver badges154 bronze badges

answered Sep 20, 2013 at 12:55

K T

1991 gold badge4 silver badges14 bronze badges

Comments

Anthony · Accepted Answer · 2013-09-20 13:09:53Z

1

If your categories are text instead of numeric, they will need to be quoted first before they can go into the query.

You can use array_walk to do this and an anonymous callback function on PHP 5.3 and later. You can also go ahead and escape the values to make them safe while your looping through them:

$categories = array(2, 6, 22, 33, 34, 83, 220, 222, 886, 897);

array_walk($categories, function( &$category ) { 
     $category = $this->db->escape_string($category);
     $category = "'{$category}'";
 });

$categories_inclause = implode(",", $categories);

$this->db->query = "SELECT blah WHERE pc.category_id IN ($categories_inclause)";

answered Sep 20, 2013 at 13:09

Anthony

37.2k26 gold badges103 silver badges167 bronze badges

1 Comment

secondman Over a year ago

They are statically set numerically so this isn't an issue. Thanks.

Daniel W. · Accepted Answer · 2013-09-20 13:08:54Z

0

Using IN() in combination with implode():

$query = $this->db->query ("
SELECT 
    p.product_id, 
    p.quantity 
FROM {$this->prefix}product p 
LEFT JOIN {$this->prefix}product_to_category pc 
    ON (p.product_id = pc.product_id) 
WHERE pc.category_id = IN(" . implode(',', $categories) . ") 
AND p.status = '1' 
ORDER BY p.quantity DESC 
LIMIT 0, 4");

edited Sep 20, 2013 at 13:08

Daniel W.

32.6k15 gold badges99 silver badges155 bronze badges

answered Sep 20, 2013 at 13:03

marcovanbostin

18014 bronze badges

Collectives™ on Stack Overflow

Using Array Values in Mysql Query

4 Answers 4

8 Comments

Comments

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

8 Comments

Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related