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?

Answer 1

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

Answer 2

You can use IN clause.

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