need advice on nested loops.

[boo_lolly]

i have a mysql table with a few columns. some of the rows in this table might contain the same content in the same column. for instance. my column titles are 'uID' 'product' 'item' 'need' 'yah' 'blah' 'blah'... here's what i need i need some advice on how to design a loop (or set of loops) to output an HTML. i need to match all the rows in the table that have the same conent in the 'product' column. i do this by putting "ORDER BY product" in the sql command. the difficulty is involved in the display of the content. i want to write a loop that will output HTML tables. each HTML table will have a title that spans across the whole table. that title will be the product. then, display the rest of the information below it (still inside the HTML table). as soon as the mysql_fetch_array comes across a row in the sql table that has a new name for the 'product' column, end the HTML table, and start again. this time, display the NEXT product name as the title of the new HTML table. this loop will run until there are no more rows left in the mysql table. here's what i got so far...

$row = mysql_query("SELECT * FROM ". $regID ."") or die(mysql_error());

	$num_rows = mysql_num_rows($row);

		echo "Total Number of Items in this registry: ". $num_rows ."";
		
		echo "<TABLE BORDER=1><TR><TH COLSPAN=7 ALIGN=left>Category: ". $row['category'] ."</TH></TR>";

// BEGIN SOME TYPE OF LOOP HERE

		echo "<TR><TH>Item</TH><TH>Quantity Requested</TH><TH>Still Needs</TH><TH>Price</TH><TH>View</TH><TH>Quantity</TH><TH>Buy</TH></TR>";

// EXECUTE THIS LOOP (or some loop like it)
		for($i=0; $i < $num_rows; $i++)
		{
			$row = mysql_fetch_array($row);
			echo "<TR><TD>". $row['item'] ."</TD><TD>". $row['qty_req'] ."</TD><TD>". $row['still_needs'] ."</TD><TD>VAR price</TD><TD>VAR link</TD><TD>Input Field</TD><TD>Add to Cart</TD></TR>";
		}

// UNTIL IT REACHES A ROW WITH A NEW NAME IN ITS 'product' COLUMN THEN END TABLE...
	
		echo "</TABLE><br /><br />";
	
//DO THE WHOLE THING OVER AGAIN (back up to first loop)

// WHEN THE WHOLE THING IS COMPLETELY DONE, CLOSE DATABASE CONNECTION...

		mysql_close($db);

any help?

[boo_lolly]

for example... my SQL table looks like this:

+--------------+------+-------------+------------------+ 
|   CATEGORY   | ITEM |   QTY_REQ   |    STILL_NEEDS   |
+--------------+------+-------------+------------------+
|   kitchen    |  pan |      5      |        2         |
+--------------+------+-------------+------------------+
|   drapes     | sheet|     2       |         1        |
+--------------+------+-------------+------------------+
|  kitchen     |glass |      4      |          2       |
+--------------+------+-------------+------------------+
|   office     | pens |      3      |         1        |
+--------------+------+-------------+------------------+

i need to write a loop to output an HTML table that looks like this...

+-------------------------------------------------------+ 
| CATEGORY: drapes                                      |
+-------------------------------------------------------+ 
|      ITEM       |      QTY_REQ     |   STILL_NEEDS    |
+-----------------+------------------+------------------+
|    sheet        |        2         |        1         |
+-----------------+------------------+------------------+


+-------------------------------------------------------+ 
| CATEGORY: kitchen                                     |
+-------------------------------------------------------+ 
|      ITEM       |       QTY_REQ    |  STILL_NEEDS     |
+-----------------+------------------+------------------+
|     glass       |         4        |       2          |
+-----------------+------------------+------------------+
|     pan         |         5        |       2          |
+-----------------+------------------+------------------+


+-------------------------------------------------------+ 
| CATEGORY: office                                      |
+-------------------------------------------------------+ 
|     ITEM        |      QTY_REQ     |   STILL_NEEDS    |
+-----------------+------------------+------------------+
|     pens        |        3         |        1         |
+-----------------+------------------+------------------+

get it now?

[iamthwee]

for example... my SQL table looks like this:

+--------------+------+-------------+------------------+ 
|   CATEGORY   | ITEM |   QTY_REQ   |    STILL_NEEDS   |
+--------------+------+-------------+------------------+
|   kitchen    |  pan |      5      |        2         |
+--------------+------+-------------+------------------+
|   drapes     | sheet|     2       |         1        |
+--------------+------+-------------+------------------+
|  kitchen     |glass |      4      |          2       |
+--------------+------+-------------+------------------+
|   office     | pens |      3      |         1        |
+--------------+------+-------------+------------------+

i need to write a loop to output an HTML table that looks like this...

+-------------------------------------------------------+ 
| CATEGORY: drapes                                      |
+-------------------------------------------------------+ 
|      ITEM       |      QTY_REQ     |   STILL_NEEDS    |
+-----------------+------------------+------------------+
|    sheet        |        2         |        1         |
+-----------------+------------------+------------------+


+-------------------------------------------------------+ 
| CATEGORY: kitchen                                     |
+-------------------------------------------------------+ 
|      ITEM       |       QTY_REQ    |  STILL_NEEDS     |
+-----------------+------------------+------------------+
|     glass       |         4        |       2          |
+-----------------+------------------+------------------+
|     pan         |         5        |       2          |
+-----------------+------------------+------------------+


+-------------------------------------------------------+ 
| CATEGORY: office                                      |
+-------------------------------------------------------+ 
|     ITEM        |      QTY_REQ     |   STILL_NEEDS    |
+-----------------+------------------+------------------+
|     pens        |        3         |        1         |
+-----------------+------------------+------------------+

get it now?

The organisation of your data will be done in php, however, the tables you would need to use html. Can you create simple tables in html?

[iamthwee]

Oops I misunderstood you.

If you sort the columns by category you will notice all the ones where kitchen is will appear directly under the previous one. That means every category will be grouped together, making your task easier.

ie.

drapes
kitchen   
kitchen  <- Directly under the previous one
office

This is just a sort by string value of course.