need help 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...
[code]
$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);
[/code]

any help?

[btherl]

If I understand correctly, you can do it like this.

[code=php:0]// Start first table
$last_product = null;
for ($i = 0; $i < $num_rows; $i++) {
  if ($row['product'] !== $last_product && $last_product !== null) {
    // End previous table and start new table
    $last_product = $row['product'];
  }
  // Display row
}
// End final table[/code]

[jsladek]

You sure that SQL works? Is your table name $regID ?

I always do this..
[code]  //create sql
    $sql_user_data="SELECT * FROM table_name";

  #print("$sql_user_data");
  // this statement actually executes the sql
  $result = mysql_query($sql_user_data);

  //access record info and build form
  while($row = mysql_fetch_array($result)) {
print("$row[image]<br>");
}

John
[/code]

[boo_lolly]

maybe this will shed some light on the situation... my SQL table looks like this:
[code]
+--------------+------+-------------+------------------+
|  CATEGORY  | ITEM |  QTY_REQ  |    STILL_NEEDS  |
+--------------+------+-------------+------------------+
|  kitchen    |  pan |      5      |        2        |
+--------------+------+-------------+------------------+
|  drapes    | sheet|    2      |        1        |
+--------------+------+-------------+------------------+
|  kitchen    |glass |      4      |          2      |
+--------------+------+-------------+------------------+
|  office    | pens |      3      |        1        |
+--------------+------+-------------+------------------+
[/code]

i need to write a loop to output an HTML table that looks like this...
[code]
+-------------------------------------------------------+
| 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        |
+-----------------+------------------+------------------+
[/code]


get it now?

[komquat]

Is someone selecting the catergory somewhere? 

[komquat]

How about something like this.  I did not test, just threw this together quick.


[code=php:0]
//create array
$cat_array = (cat1, cat2, cat2);
$num_cat = 3;

//Start you loop here
$i = 1;
for ($i <= $num_cat; $i++)
//create sql
     $display = "<table><tr><td colspan='3'>$cat_array[$i]</td></tr>";
$sql_user_data="SELECT * FROM table_name WHERE catergory = '$cat_array[$i]'";
$result = mysql_query($sql_user_data);
while($row = mysql_fetch_array($result)) {
$f1 = $row['f1'];
$f2 = $row['f2'];
$f3 = $row['f3'];

$display .= "<tr><td>$f1</td><td>$f2</td><td>$f3</td></tr>";

}
$display .= "</table>";

}

[/code]

[craygo]

Try This
[code]// Print Headers
print '<table width="500" align="center" cellspacing=0 cellpadding=0>';
print '<tr bgcolor="F98F5B">';
print '<td width=300 align=center>ITEM</td>';
print '<td width=100 align=center>QTY_REQ</td>';
print '<td width=100 align=center>STILL_NEEDS</td>';
print '</tr>';
print '</table>';
// Set initial group values
$lastcat = '';
// Query database
$sql = "SELECT * FROM ". $regID ." ";
$sql .= "GROUP BY CATAGORY, ITEM";
  $res = mysql_query($sql);
  $num_rows = mysql_num_rows($res);
if($num_rows > 1){
// Set initial row color
$bgcolor = "FFFFFF";
// Start Loop
    while ($rows = mysql_fetch_assoc($res)){
// Print Group Header
if ($rows['CATAGORY'] != $lastcat) {
// Print Group Name
print '<table width="500" align="center" cellspacing=0 cellpadding=0>';
print '<tr bgcolor=83EA44>';
print '<td colspan=3 align=left><strong>Catagory: '.$rows['CATAGORY'].'</strong></td>';
print '</tr>';
print '</table>';
}
// Alternate row color
if ($bgcolor == "#E0E0E0"){
  $bgcolor = "#FFFFFF";
} else {
  $bgcolor = "#E0E0E0";
}
// Print Database Details
print '<table width=500 align=center cellspacing=0>';
print '<tr bgcolor='.$bgcolor.'>';
print '<td width=300 align=center>'.$rows['ITEM'].'</td>';
print '<td width=100 align=center>'.$rows['QTY_REQ'].'</td>';
print '<td width=100 align=center>'.$rows['STILL_NEEDS'].'</td>';
print '</tr>';
print '</table>';
// Reset group values
$lastcat = $rows['CATAGORY'];
}
} else {
// Print No data message
print '<table width=650 align=center>';
print '<tr>';
print '<td align=center><strong>NO ITEMS!!</strong></td>';
print '</tr>';
print '</table>';
}
?>[/code]

Ray

[komquat]

Craygo, you are the best! 

[boo_lolly]

no one is selecting a category. the SQL table will always be changing (admin adding, editing, removing data), and i will never know exactly what's in there or isn't in there. so this loop needs to be 'intelligent' enough to recognize a change on it's own. either you guys don't understand what i'm trying to do, or i don't understand that you guys are helping me greatly... haha, either way, there's miscommunication =)

[komquat]

Cryago's above should work for you.  I made a quick little variation that should make it look just like you want, give it a shot, or else play with it to get it looking like you want, it should work.

[code=php:0]
// Set initial group values
$lastcat = '';
// Query database
$sql = "SELECT * FROM ". $regID ." ";
$sql .= "GROUP BY CATAGORY, ITEM";
  $res = mysql_query($sql);
  $num_rows = mysql_num_rows($res);
if($num_rows > 1){
// Set initial row color
$bgcolor = "FFFFFF";
// Start Loop
    while ($rows = mysql_fetch_assoc($res)){
// Print Group Header
if ($rows['CATAGORY'] != $lastcat) {
// Print Group Name
print '<table width="500" align="center" cellspacing=0 cellpadding=0>';
print '<tr bgcolor=83EA44>';
print '<td colspan=3 align=left><strong>Catagory: '.$rows['CATAGORY'].'</strong></td>';
print '</tr>';
print '<tr bgcolor="F98F5B">';
print '<td width=300 align=center>ITEM</td>';
print '<td width=100 align=center>QTY_REQ</td>';
print '<td width=100 align=center>STILL_NEEDS</td>';
print '</tr>';
}
// Alternate row color
if ($bgcolor == "#E0E0E0"){
  $bgcolor = "#FFFFFF";
} else {
  $bgcolor = "#E0E0E0";
}
// Print Database Details
print '<tr bgcolor='.$bgcolor.'>';
print '<td width=300 align=center>'.$rows['ITEM'].'</td>';
print '<td width=100 align=center>'.$rows['QTY_REQ'].'</td>';
print '<td width=100 align=center>'.$rows['STILL_NEEDS'].'</td>';
print '</tr>';
print '</table>';
// Reset group values
$lastcat = $rows['CATAGORY'];
}
} else {
// Print No data message
print '<table width=650 align=center>';
print '<tr>';
print '<td align=center><strong>NO ITEMS!!</strong></td>';
print '</tr>';
print '</table>';
}
?>
[/code]

[boo_lolly]

i can't tell you guys how much i appreciate all of your help and input. but everytime i try it gives me an error.

[b]Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource in /../../../../registry.php on line 37
NO ITEMS!![/b]

[code]
<?php
//registry.php

@ $db = mysql_connect("yah", "blah", "blah");
if(!$db)
{
echo "Error: Could not connect to the database. Please try again later.";
exit;
}

mysql_select_db("registry_DB", $db);

$query = "SELECT * FROM ". $regID ." ORDER BY category";
$res = "mysql_query($query) or die(mysql_error().': '.$query)";


$lastcat = '';

$num_rows = mysql_num_rows($res); //<-- LINE 37
if($num_rows > 1){
// Set initial row color
$bgcolor = "FFFFFF";
// Start Loop
    while ($rows = mysql_fetch_assoc($res)){
// Print Group Header
if ($rows['CATAGORY'] != $lastcat) {
// Print Group Name
print '<table width="500" align="center" cellspacing=0 cellpadding=0>';
print '<tr bgcolor=83EA44>';
print '<td colspan=3 align=left><strong>Catagory: '.$rows['CATAGORY'].'</strong></td>';
print '</tr>';
print '<tr bgcolor="F98F5B">';
print '<td width=300 align=center>ITEM</td>';
print '<td width=100 align=center>QTY_REQ</td>';
print '<td width=100 align=center>STILL_NEEDS</td>';
print '</tr>';
}
// Alternate row color
if ($bgcolor == "#E0E0E0"){
  $bgcolor = "#FFFFFF";
} else {
  $bgcolor = "#E0E0E0";
}
// Print Database Details
print '<tr bgcolor='.$bgcolor.'>';
print '<td width=300 align=center>'.$rows['item'].'</td>';
print '<td width=100 align=center>'.$rows['qty_req'].'</td>';
print '<td width=100 align=center>'.$rows['still_needs'].'</td>';
print '</tr>';
print '</table>';
// Reset group values
$lastcat = $rows['category'];
}
} else {
// Print No data message
print '<table width=650 align=center>';
print '<tr>';
print '<td align=center><strong>NO ITEMS!!</strong></td>';
print '</tr>';
print '</table>';
}
?>
[/code]

[craygo]

if it is giving you that error then the query is not running or the sql statement is wrong. I see you echo'd out the sql statement, what is it saying??
are you sure $regID is being set??
$regID is the table name... why??? isn't the information always coming from the same table?? Also it is not going to work unless you group the info not order it.

Ray

[boo_lolly]

you're right the query WAS wrong, but i fixed it and it STILL didn't work. $regID is the name of the SQL table. i have a search/results page that returns a list of... well... results. haha. and EACH result is followed by a link. when a user clicks this link, it takes them to the page that you and i have been having problems with (registry.php). each search result has an affiliated table in the SQL database. the $regID is the name of the SQL table that matches the result clicked on the search/results page. as in, there will ALWAYS be SQL tables added and deleted and edited 'on the fly' with the CMS i'm building for this. these pages are designed for public use. i'm building an administrative CMS to edit the content of the SQL tables, therefore, changing what the public views.

[craygo]

When you echo the sql what are teh results. Sorry but don't check the forums on the weekend! :)

Ray