Repeat SQL select statement until condition is met

Question

Please see image below: To get the ID's of Mazda's parent categories I would use the following:

SELECT `parent_id` FROM `product_categories` WHERE `category_id` = 8
if result is not equal to 0 repeat select statement

Is it considered bad practice to loop a SQL select statement until a condition is met? In other words; should I redesign the product categories table?

Many thanks for your advice.

Answer 1

Yes, in this case it may be warranted to add another field that contains the top-most parent.

This of course introduces redundancy, as the information is already available in the table, but sometimes that is an acceptable tradeoff for performance.

You would add a field for the topsmost parent like this:

category_id  category_name  parent_id  top_id
------------ -------------- ---------- -------
1            CARS_          0          1
4            smartphones    0          4
5            Japanese-      1          1
7            Lexus          5          1
8            Mazda          5          1
9            Korean         1          1
10           Toyota         5          1

That can be used for getting the topmost parent for a single item, or for getting all items that belong to the same parent, which would be even more complicated with the original layout.

Answer 2

Well, using this kind of query may complicate processing the results. I strongly and personally use recursive functions instead of query loops.

more info : What is a RECURSIVE Function in PHP?