CODEWITHSUNDEEP
phpmysqlsqlarrays
secondman
secondman

Reputation: 3287

Using Array Values in Mysql Query

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?

Upvotes: 3

Views: 14494

Answers (4)

Anthony
Anthony

Reputation: 37065

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

Upvotes: 1

marcovanbostin
marcovanbostin

Reputation: 180

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

Upvotes: 0

K T
K T

Reputation: 199

You can use IN clause.

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

Upvotes: 2

Jim
Jim

Reputation: 22656

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

Upvotes: 7

Related Questions