PHP/SQL – querying relational DB for IDs but being able to query those IDs in seperate DB using ORDER BY

I have an ecommerce site where I have some products that can be in multiple categories.

I therefore have a products table, a categories table, and a product_categories table.

So what I do is query the product_categories table for the category ID of the category I want and get an array of product IDs like so:

$product_ids = [];
$params = [$category_id];
$sql = "SELECT * FROM product_categories WHERE category_id=?";
$stmt = DB::run($sql,$params);
while($row = $stmt->fetch(PDO::FETCH_ASSOC)){
    $product_id = $row['product_id'];
    array_push($product_ids,$product_id);
}

I then have a show_products function which takes the array and spits out all of the products. However, this function has a foreach statement like so:

foreach($ids as $id){
    $params = [$id];
    $sql = "SELECT * FROM products WHERE id=?";
    $stmt = DB::run($sql,$params);
    while ($row = $stmt->fetch(PDO::FETCH_ASSOC)){
        //show products
    }
}

This is fine and works but I want to be able to determine the order of which the products are displayed, so in my products table I have an ‘order_by’ column and would like to do something like SELECT * FROM products WHERE id=? ORDER BY order_by ASC.

Obviously the way I’m set up at the moment means that it is doing the query for 1 id at a time so it can’t order them in this way.

I was thinking to get around this I would need to query myproducts like I’ve done above but instead of showing the products at this stage I would get the order_by column and create another array and sort them by this order nand then actually use that array to query again and show the products.

Is there a better way of doing this?

**Note: I would like to keep my show_products function seperate for use in other parts of my site as it is fairly complicated (gets ‘as low as’ price and stock levels etc). So I want to be able to just pass an array of IDs to this function.

Answer

This is my final SQL query.

SELECT product_categories.product_id
FROM product_categories INNER JOIN products ON product_categories.product_id=products.id
WHERE product_categories.category_id=?
ORDER BY products.order_by ASC

Advertisement

Answer