array while loops and organizing

[lobster]

I give up...I'm going blind and it is probably really stupid.

Situation: I have items that belong to categories. Would liek to display items in chunks of categories.
ie:

Category 1
Product 11245
Product 25325
Category 2
Product 11154

etc...

Table 1: Products
Table 2: Category

Queries:
$prodresult = mysql_query("select * from PRODUCTS order by CATEGORY desc");
$prodnum_results = mysql_num_rows($prodresult);

 $tcatresult = mysql_query("select * from CATEGORY");

		<?  

			while ($cattitle = mysql_fetch_array($tcatresult)) 
			        { 
					echo "<tr><td colspan='9' class='maintext'><b>" . $cattitle['NAME'] ."</b></td></tr>";				
						while($prodrow = mysql_fetch_array($prodresult)){

					 if($prodrow['CATEGORY'] == $cattitle['CATEGORY'])  
					{ 
						echo "<tr bgcolor='#FFFFFF' style='padding-left:10px;'>";
						echo "<td class='xsmalltext'><b>". $prodrow['CODE'] . "</b></font></td>";
						echo "<td class='xsmalltext'><b>". $prodrow['NAME'] . "</b></font></td>";
						echo "<td class='xsmalltext'><b>". $prodrow['SUBNAME'] . "</b></font></td>";
						echo "<td class='xsmalltext'><b>". $prodrow['SIZE'] . "</b></font></td>";
						echo "<td class='xsmalltext'><b>". $prodrow['DESCRIPTION'] . "</b></font></td>";
						echo "<td class='xsmalltext'><b>". $prodrow['COST'] . "</b></font></td>";
						echo "<td class='xsmalltext'><b>";
						echo "<a href='update_product.php?id=";
						echo htmlspecialchars( stripslashes($prodrow['CODE']));
						echo "'>Update</a>"; 
						echo "</b></font></td>";
						echo "<td class='xsmalltext'><b>";
						echo "<a href='delete_product.php?id=";
						echo htmlspecialchars( stripslashes($prodrow['CODE']));
						echo "'>Delete</a>"; 
						echo "</b></font></td></tr>";
					} 
			} 
		}
	?>

What have I got backwards??? The categories are showing but not the items...or at least not all of them.

Thanks in advance

[paulnaj]

Think about the way you're calling on the data return from the two results. Remember, for one call to mysql_query, you only get one cycle through the rows.

I tend to think that it's usually easier to do this in two steps. There's probably a slicker way, all in one go. But at least here you can see what's going on.

//Build the data first
$categories = array();
$prodresult = mysql_query("select * from PRODUCTS order by CATEGORY desc, CODE asc");
while($prodrow = mysql_fetch_array($prodresult)){ 
	$categories[$prodrow['CATEGORY']][] = $prodrow;
}

// Display the data (I haven't altered any of the HTML at all)
foreach($categories as $cat_name => $cat_products){
  echo "<tr><td colspan='9' class='maintext'><b>" . $cat_name ."</b></td></tr>";                 

  foreach($cat_products as $prodrow){
    echo "<tr bgcolor='#FFFFFF' style='padding-left:10px;'>"; 
    echo "<td class='xsmalltext'><b>". $prodrow['CODE'] . "</b></font></td>"; 
    echo "<td class='xsmalltext'><b>". $prodrow['NAME'] . "</b></font></td>"; 
    echo "<td class='xsmalltext'><b>". $prodrow['SUBNAME'] . "</b></font></td>"; 
    echo "<td class='xsmalltext'><b>". $prodrow['SIZE'] . "</b></font></td>"; 
    echo "<td class='xsmalltext'><b>". $prodrow['DESCRIPTION'] . "</b></font></td>"; 
    echo "<td class='xsmalltext'><b>". $prodrow['COST'] . "</b></font></td>"; 
    echo "<td class='xsmalltext'><b>"; 
    echo "<a href='update_product.php?id="; 
    echo htmlspecialchars( stripslashes($prodrow['CODE'])); 
    echo "'>Update</a>";  

    echo "</b></font></td>"; 
    echo "<td class='xsmalltext'><b>"; 
    echo "<a href='delete_product.php?id="; 
    echo htmlspecialchars( stripslashes($prodrow['CODE'])); 
    echo "'>Delete</a>";  

    echo "</b></font></td></tr>"; 
  }
}

P.S. I can't debug this, there could be a typo or too.

[lobster]

OK. I have been plagued by this all day. I put the category names in a different table and reference the category code in the Products table. Which means that I would have to query the Category table in order to pull the names.

Should I just have put the category names in the PRODUCT table and left it at that?

[paulnaj]

The only reason you'd need to refer to the CATEGORY table is if you wanted to include the empty categories.

In which case ...

//Build the data first 
$prodresult = mysql_query("select * from PRODUCTS order by CODE asc"); 
while($prodrow = mysql_fetch_array($prodresult)){  

    $categories[$prodrow['CATEGORY']][] = $prodrow; 
} 

$catresult = mysql_query("select NAME from CATEGORY order by NAME"); 
while($catrow = mysql_fetch_row($catresult)){  

    $all_categories[] = $catrow[0];
} 

// Display the data (I haven't altered any of the HTML at all) 
foreach($all_categories as $cat_name){ 
  echo "<tr><td colspan='9' class='maintext'><b>" . $cat_name ."</b></td></tr>";                  

  if(array_key_exists($cat_name, $categories)){
    $cat_products = $categories[$cat_name];
    foreach($cat_products as $prodrow){ 
      echo "<tr bgcolor='#FFFFFF' style='padding-left:10px;'>";  

      echo "<td class='xsmalltext'><b>". $prodrow['CODE'] . "</b></font></td>";  

      echo "<td class='xsmalltext'><b>". $prodrow['NAME'] . "</b></font></td>";  

      echo "<td class='xsmalltext'><b>". $prodrow['SUBNAME'] . "</b></font></td>";  

      echo "<td class='xsmalltext'><b>". $prodrow['SIZE'] . "</b></font></td>";  

      echo "<td class='xsmalltext'><b>". $prodrow['DESCRIPTION'] . "</b></font></td>";  

      echo "<td class='xsmalltext'><b>". $prodrow['COST'] . "</b></font></td>";  

      echo "<td class='xsmalltext'><b>";  

      echo "<a href='update_product.php?id=";  

      echo htmlspecialchars( stripslashes($prodrow['CODE']));  

      echo "'>Update</a>";   

      echo "</b></font></td>";  

      echo "<td class='xsmalltext'><b>";  

      echo "<a href='delete_product.php?id=";  

      echo htmlspecialchars( stripslashes($prodrow['CODE']));  

      echo "'>Delete</a>";   

      echo "</b></font></td></tr>";  

    } 
  }
}

[lobster]

actually I need to refer to the category table in order to print out the category name instead of the category ID.

The last bunch of code does iterate through all of the cateories but the products are no longer showing.

I appreciate your help...I am trying to see if I can figure it out.

[paulnaj]

No problem.

I might have got a bit mixed up with the fieldnames. Could you show me all the field definitions from both tables (names and types).

P.

[lobster]

CREATE TABLE PRODUCTS (
CODE int(10) NOT NULL default '0',
NAME varchar(20) NOT NULL default '',
SUBNAME varchar(20) default NULL,
DESCRIPTION varchar(255) default NULL,
SIZE varchar(40) default NULL,
COST varchar(10) default '0',
CATEGORY smallint(2) NOT NULL default '0',
PRIMARY KEY (CODE),
UNIQUE KEY CODE (CODE)
) TYPE=MyISAM COMMENT='Products';

CREATE TABLE CATEGORY (
CATEGORY int(20) NOT NULL default '0',
NAME varchar(40) NOT NULL default '',
PRIMARY KEY (CATEGORY,CATEGORY),
UNIQUE KEY CODE (CATEGORY)
) TYPE=MyISAM;

I am new with tables and I know a little about foreign keys but I dont think I can use them with this version of MySQL.

[paulnaj]

Ok. I did get mixed up. The foriegn key is CATEGORY in both tables. Is that right?

Notice, though, that in PRODUCTS, it's a smallint(2), but in CATEGORY, it's a int(20).
There's no reason why they shouldn't have exactly the same definition.

I think this should work, though.

//Build the data first 
$prodresult = mysql_query("select * from PRODUCTS order by CODE asc");  

while($prodrow = mysql_fetch_array($prodresult)){   

    $categories[$prodrow['CATEGORY']][] = $prodrow;  

}  

$catresult = mysql_query("select NAME, CATEGORY from CATEGORY order by CATEGORY");  

while($catrow = mysql_fetch_array($catresult)){   

  echo "<tr><td colspan='9' class='maintext'><b>" . $catrow['NAME'] ."</b></td></tr>";                   

  if(array_key_exists($catrow['CATEGORY'], $categories)){ 
    $cat_products = $categories[$catrow['CATEGORY']]; 
    foreach($cat_products as $prodrow){  

      echo "<tr bgcolor='#FFFFFF' style='padding-left:10px;'>";   

      echo "<td class='xsmalltext'><b>". $prodrow['CODE'] . "</b></font></td>";   

      echo "<td class='xsmalltext'><b>". $prodrow['NAME'] . "</b></font></td>";   

      echo "<td class='xsmalltext'><b>". $prodrow['SUBNAME'] . "</b></font></td>";   

      echo "<td class='xsmalltext'><b>". $prodrow['SIZE'] . "</b></font></td>";   

      echo "<td class='xsmalltext'><b>". $prodrow['DESCRIPTION'] . "</b></font></td>";   

      echo "<td class='xsmalltext'><b>". $prodrow['COST'] . "</b></font></td>";   

      echo "<td class='xsmalltext'><b>";   

      echo "<a href='update_product.php?id=";   

      echo htmlspecialchars( stripslashes($prodrow['CODE']));   

      echo "'>Update</a>";    

      echo "</b></font></td>";   

      echo "<td class='xsmalltext'><b>";   

      echo "<a href='delete_product.php?id=";   

      echo htmlspecialchars( stripslashes($prodrow['CODE']));   

      echo "'>Delete</a>";    

      echo "</b></font></td></tr>";   

    }  

  } 
}

[lobster]

I will fix the db int issue...

Thanks so much for your help...that worked like a charm!

[paulnaj]

It's a pleasure.