[QUESTION Pt.3] - Two Query loops in one.

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
iknownothing
Forum Contributor
Posts: 337
Joined: Sun Dec 17, 2006 11:53 pm
Location: Sunshine Coast, Australia

[QUESTION Pt.3] - Two Query loops in one.

Post by iknownothing »

Go Down for Question 2.

Hey Guys,
I have two tables in the database, one is the product information, and if the product is in a food group, a nutrition guide table is also used.

What I need is to have the Product information loop in a while statement, so it shows all products, and at the same time, it has to pull all rows from the nutrition guide table that refer to a particular food when it gets to that food product, so esentially, it is a loop inside another loop.

Eg.

Code: Select all

+----------+----------+
|    id    | product  |
+----------+----------+
+----------+----------+
|     1    |  apple   |
+----------+----------+
|     2    |  orange  |
+----------+----------+

Code: Select all

+----------+----------+----------+----------+----------+
|     id   |  prodid  |    cat   | serving  |  whole   |
+----------+----------+----------+----------+----------+
+----------+----------+----------+----------+----------+
|     1    |    1     |   fat    |   .5g    |   1.5g   |
+----------+----------+----------+----------+----------+
|     2    |    1     |   sugar  |    20g   |    40g   |
+----------+----------+----------+----------+----------+
So, using the example, the loop would first bring out APPLE, then loop the nutrtional guide table to bring out FAT and then SUGAR and then onto ORANGE in the product table again.

Does anyone know how I could do this?

Last edited by iknownothing on Thu Aug 30, 2007 7:00 pm, edited 3 times in total.
User avatar
John Cartwright
Site Admin
Posts: 11470
Joined: Tue Dec 23, 2003 2:10 am
Location: Toronto
Contact:

Post by John Cartwright »

Your best bet is probably using a LEFT JOIN to attach all the nutritional information to a product then grouping the output appropriatly using php.

Example:

Code: Select all

$sql = '
SELECT * FROM `products` 
LEFT JOIN `nutritions` ON `products`.`id` = `nutritions`.`prodid`
ORDER BY `products`.`id` DESC';

$result = mysql_query($sql) or die(mysql_error()); 

$last = '';
while ($row = mysql_fetch_assoc($result)) {
   //if we've displayed header for this product, do not display 
   if ($row['prodid'] != $last) {
      echo '<h3>'. $row['product'].'</h3>';
   }

   echo '- '. $row['cat'] .'<br />';

   //keep track of the last used prodid
   $last = $row['prodid'];
}
Should output something like

Apple
- Fat
- Sugar

Orange
- Fat
- Sugar
- etc
User avatar
iknownothing
Forum Contributor
Posts: 337
Joined: Sun Dec 17, 2006 11:53 pm
Location: Sunshine Coast, Australia

Post by iknownothing »

Thanks for that, exactly what I was looking for.
User avatar
iknownothing
Forum Contributor
Posts: 337
Joined: Sun Dec 17, 2006 11:53 pm
Location: Sunshine Coast, Australia

Post by iknownothing »

Question2.

Hey Guys,

The above works for me fine when unformatted, but when formatted, I have a few items below the Nutritional data, such as a submit button etc. When using the above code, after the first loop of the nutritional data, it then throws the formatted data below the submit button etc, when it is supposed to stay with the first row of nutritional data, just on a new row.

Is there anything anyone can think of to help this? Is there a way to count the rows pulled ONLY from the nutritional table, on the same query, so that perhaps, i can add the formatting below the data, once its hit its max rows only.

Thanks.
timvw
DevNet Master
Posts: 4897
Joined: Mon Jan 19, 2004 11:11 pm
Location: Leuven, Belgium

Post by timvw »

Actually, that's algorithms 101 class. Simply keep track of the last displayed product in your loop...

If the last and the current are not the same, it means that you're about to display a new product (meaning that you should probably end the last paragraph and start a new paragraph or whatever.. )
User avatar
iknownothing
Forum Contributor
Posts: 337
Joined: Sun Dec 17, 2006 11:53 pm
Location: Sunshine Coast, Australia

Post by iknownothing »

Thanks for your help guys. I now have another problem. I need to get data from 3 tables now. I googled and found some answers, and come up with this SQL:

Code: Select all

SELECT * FROM `products` 
LEFT JOIN `nutritionguide` 
ON `products`.`id` = `nutritionguide`.`prodid` 
LEFT JOIN `clothing` 
ON `products`.`id` = `clothing`.`idd` 
WHERE `products`.`category` = 'supplements' 
ORDER BY `products`.`title`
It seems to be semi-working, but some of the values aren't being echoed from the third table (clothing (clothing is used for SIZE and FLAVOURS for this section (usually used for SIZE and COLORS). I just need to know if this SQL SHOULD work, because if so, its my code thats the problem, and not the SQL. If there is a better way to do the SQL, could someone point me in the right direction, thanks.
Post Reply