Must be an easier way? - Display data from database under Category Heading

[mat3000000]

Here is the code I am using, it works, but the problem is I have to update every page if I want to add a new category. There must be a quicker/easier way??

 

//^^ SET ARRAYS ^^

$result = mysql_query("SELECT * FROM stock");
while ($row = mysql_fetch_array($result)){
$id = $row['id'];
$cat = $row['category'];
$name = $row['name'];
$price = $row['price'];
$new = $row['new'];
$stock = $row['stock'];
$desc = $row['description'];
if($new==1){$new = 'NEW';}else{$new = '';}
if($desc==''){$go = 0;}else{$go = 1;}


if($cat=='SAWBENCHES WALL & PANEL SAWS'&&$stock>0){
if($go==1){
$catt1[]="<tr><td width='80%'><a href='viewitem.php?product=$id'>".$name."</a></td><td width='8%'>".$new."</td><td width='12%'>£".$price;	
}else{$catt1[]="<tr><td width='80%'>".$name."</td><td width='8%'>".$new."</td><td width='12%'>£".$price;}
}
if($cat=='PLANING MACHINES & MOULDERS'&&$stock>0){
if($go==1){$catt2[]="<tr><td width='80%'><a href='viewitem.php?product=$id'>".$name."</a></td><td width='8%'>".$new."</td><td width='12%'>£".$price;	
}else{$catt2[]="<tr><td width='80%'>".$name."</td><td width='8%'>".$new."</td><td width='12%'>£".$price;}	

//Up to $catt13...

//SAWBENCHES WALL & PANEL SAWS
echo '<h3>SAWBENCHES WALL & PANEL SAWS</h3><table width="800" border="1" cellspacing="0" cellpadding="4">';
foreach ($catt1 as $item){
  echo $item;
}echo '</table>';

//PLANING MACHINES & MOULDERS
echo '<h3>PLANING MACHINES & MOULDERS</h3><table width="800" border="1" cellspacing="0" cellpadding="4">';
foreach ($catt2 as $item){
  echo $item;
}echo '</table>';

//etc.......

[PFMaBiSmAd]

In general, your query should retrieve the rows you want in the order that you want them. You would then simply iterate over the rows once and output the data the way you want it.

 

If you want the categories in the order you have implied (non-alphabetical), you would either need an 'order' column in your table to hold numerical values that define the order the categories should be displayed as or have a category id (rather than the category title spelled out in every piece of data), which you can specifically list in an ORDER BY term to give the desired final order.  You could then use an ORDER BY term in your query to get the categories in the order that you want them and all the rows for each category under each category. If you actually want the categories alphabetically sorted, you would just use ORDER BY category in the query. I'm also going to guess that you want the produce names sorted alphabetically under each category?

 

To detect and output the category heading as you are iterating over the rows, see this recent post - http://www.phpfreaks.com/forums/index.php?topic=342450.msg1615618#msg1615618

[PFMaBiSmAd]

Additional to the above, your query would also only return rows that have stock > 0 (there's no point in retrieving data from a database unless you are going to use that data.) Unless, you want to specifically display an item, but indicate it is out of stock (which your code is not currently doing.)

[PFMaBiSmAd]

Your code would end up looking like -

 

<?php

//^^ SET ARRAYS ^^

$result = mysql_query("SELECT * FROM stock WHERE stock > 0 ORDER BY category,name");
$last_cat = null; // remember the last category
while ($row = mysql_fetch_array($result)){
$id = $row['id'];
$cat = $row['category'];
$name = $row['name'];
$price = $row['price'];
$new = $row['new'];
$stock = $row['stock'];
$desc = $row['description'];
$new = ($new==1) ? 'NEW' : ' ';
if($last_cat != $cat){
	// a new or the first category was found
	if($last_cat != null){
		// not the first category, close out the previous section
		echo "</table>\n";
	}
	$last_cat = $cat; // save the new category
	// output the heading/start a new section
	echo "<h3>$cat</h3><table width='800' border='1' cellspacing='0' cellpadding='4'>\n";
}
// output the data
if($desc==''){
	echo "<tr><td width='80%'>$name</td><td width='8%'>$new</td><td width='12%'>£$price</td></tr>\n";
} else {
	echo "<tr><td width='80%'><a href='viewitem.php?product=$id'>$name</a></td><td width='8%'>$new</td><td width='12%'>£$price</td></tr>\n";
}
}
// close out the last section
echo '</table>';
?>

[mat3000000]

I could kiss you

 

Thank you very much