Nested loop ???

Ok, excuse bad english

Ok what iam trying to do is this;

i have a query to retrieve all products from catalogue (single table)

Then i also want to query another table for info based on ID of parent record (displayed in list).

<START Loop a for products pulled from TABLE A>
<product name>
<product image>
---<loop for detials based on id of parent loop and pulled from TABLE B>
----<detail 1>
----<detail 1>
----<detail 1>
---</loop for detials>
</Loop a for products>


Now i know how loops work, and have a grasp on mysql ect.

but i never use nested loop before, can someone point me in the direction of help/examples or a snip of code from somewhere showing me how this is done.

many thanks

pull from an array of structures maybe ?? *shrugs*

No. Stop. Learn SQL (the query language). Whenever you start thinking that you need to do a query and then loop over it to run more queries, that almost absolutely means that you need an inner join. In other words, once you understand joins, you can do this in one query instead of having to loop and run many separate queries.

lol ironically a join is what i try atm

Ok, i dont come much here for help, but i am in real need

I made 2 tables in MySql 5 database just to figure this out, this is my sql code;



This gets all data i need, like so;

http://img186.imageshack.us/img186/6776/showub9.jpg

But i need product name to be distinct (yes i know distinct clause to use with SELECT) but i can not get it to work.

Or i need to use CF to display the products distinctly.

i not ask for much help here, but i try to help others, please does someone know how to make this work ??


This is why i do not approach this problem in first place like this (with join) or with single querey (KITELESS)

i think i need to make nested loops with coldfusion, cause i cant see way of making this work using JOIN alone. i maybe need coldfusion to go through product distinctly and make array, then loop through descriptions, and place them in structure within array using pId as relationship. ???

Yes, since I assume the combination of productName, productImage, and detail are all unique, you can't use DISTINCT here. Just to be clear, if the name, image, and detail are the same for each product, you should be able to to do this with DISTINCT.

Anyway, to deal with the first situation, what you can do is order the result set, and then use <cfoutput group=""> like this: