Customizing the "select_products" Stored Procedure to loop through an array?

[Hillbilly]

Hi, (Thanks for the great book )

I am trying to write a a stored procedure that will perhaps loop through an array of product ids and then JOIN tables in the database depending on what category the product is in (their may be multiple products in different categories). 

I am not sure if this possible - am I barking up the wrong tree?

Very simplified - this obviously doesn't work but perhaps you can get an idea of what I'm trying to achieve)

IN: product_ids_array VARCHAR (300) //Array of ids sent with the call 

BEGIN

// start loop here somehow ? Would I have to split ID array first? 

DECLARE master_category SMALLINT 
SELECT master_cat_id INTO  master_category FROM products WHERE id IN (products_ids_array); 

IF master_category = 1 THEN
SELECT p*, sp*
FROM products AS p
INNER JOIN specs_table_1 as sp ON sp.id = pr.specs_id
WHERE pr.id IN (products_ids_array);

ELSE 
SELECT p*, sp*
FROM products AS p
INNER JOIN specs_table_2 as sp ON sp.id = pr.specs_id
WHERE pr.id IN (products_ids_array);
ENDIF;

end loop here ??

END

Any help would be much appreciated.

[Larry]

Thanks for the nice words on the book! I don't quite follow what your goal is here, however. Could you maybe start by explaining the end result you're working towards?

[Hillbilly]

Thanks for responding Larry,

Basically I would like to return a list of products with their technical specifications.

The database has a list of products, each product belongs to a category and each category has a separate table for it's technical specifications.

For example:
Product (Winch 1) - Category (Winches) - Specifications (Winches Technical Specs)
Product (Hoist 1) - Category (Hoists) - Specifications (Hoists Technical Specs)
Product (Winch 2) - Category (Winches) - Specifications (Winches Technical Specs)
etc

I have a situation where I need to call products from both winches and hoists categories at the same time but I am unsure of how to write the Stored Procedure so it knows which Technical Specs table join to for each product?

P.S. The products table is linked to the categories table with an index (category_id) and to its specifications by an index (specs_id). So in this example "Winch 1" and "Hoist 1" could have the same Specs_id column value but these would be referring to different tables.

I hope that makes sense?

Edited June 10, 2019 by Hillbilly
add more details

[Hillbilly]

OK - I managed to return the products and the specifications in a big table as follows:

IN product_ids VARCHAR (300)
// the product ids and sku's sent concatenated in an array eg: "1-WIN01","123-HOIS98","206-WIN651"
// I concatenated the sku and ids as if I used the id on its own then the WHERE clause failed as it would find "1" in "123"

BEGIN
   set @query = concat("SELECT c.category, pr.*, psh.*, psw.*
FROM products AS pr
INNER JOIN categories AS c ON c.id = pr.category_id
LEFT JOIN product_specs_hoists AS psh ON psh.id = pr.specs_id
LEFT JOIN product_specs_winches AS psw ON psw.id = pr.specs_id
WHERE CONCAT(pr.id,pr.sku) IN (" , product_ids ,") AND pr.enabled=1");
    PREPARE stmt FROM @query;
    EXECUTE stmt ;
END

So now I can simply use the category result on the php page to decide which specifications to display.
If ($row['category']== "winches" {
// echo Winches Specifications;
} else {
//echo hoist specifications
}

Am I re-inventing the wheel here - is there an easier way to do this?

[Larry]

Off the top of my head and given the information provided I can't think of an obviously easier way. There's a lot to be said for "this works"!

[Hillbilly]

Thanks Larry,

One last question regarding the above. I just have one small issue in that some of the columns names in Winch Specifications table and the Hoist Specifications table are the same.

Is there any way to add a prefix to the column names of the entire table?

Eg: SELECT psw.* AS psw_*, psh.* AS psh_*   ...

(rather than listing every singles column with AS)

[Larry]

Ugh. I can see what you mean. There's no way to do this as far as I know. Sorry!