Using a table row as a heading?

[phpstuck]

Ok here's my problem. I am running an inventory database (MySQL) using PHP to generate several different reports.

 

I have a row in my table named CAT, I would like to generate a report using the CAT as a header. It would pull a CAT name from the table, echo it as a header and then list each item with that particular CAT name.

 

making it look like this:

 

Canned Food:

 

Kroger, Sweet Corn  11 Cans

Kroger, Early Peas    13 Cans

etc...

 

Boxed Food:

 

Kraft, Mac & Cheese      24 Boxes

Kroger, Saltine Crackers  8 boxes

etc...

 

Where as canned foods and boxed foods are CAT (category) entries from the database.

 

I have tried using the following code, but it falls very short.

 

$sql = mysql_query(
"ALTER TABLE inven ORDER BY cat");




echo "<br><hr>";


$deflist=mysql_query(
        "SELECT quant, brand, descrip, size, flavor, cat FROM inven");

while ($all = mysql_fetch_array($deflist)) {
  $quant=$all['quant'];
  $brand=$all['brand'];
  $descrip=$all['descrip'];
  $size=$all['size'];
  $flavor=$all['flavor'];
  $cat=$all['cat'];

//{ echo "<p>".$cat."</p>"; }

//echo "<b><font face='arial' size='2'><u>(".$quant.
//") </b></font></u><font face='arial' size='2'>".$brand.", ".
//" - "."<font color='grey' size='-1'><i>".$descrip." - ".$flavor.
//"</i></font> - <i><font color='purple' size='-3'></i>".$size.
//"</font><br>";
//}
?>

[Catfish]

I think you mean 'cat' is a field name (or 'column'), not a row.

 

I find the easiest way to output data like this is to store it in a format that will make the outputting easier.

$sql = mysql_query(
"ALTER TABLE inven ORDER BY cat");
echo "<br><hr>";
$deflist=mysql_query(
        "SELECT quant, brand, descrip, size, flavor, cat FROM inven");
while ($all = mysql_fetch_array($deflist)) {
   $results[$all['cat']][] = array ('quant' => $all['quant'], 'brand' => $all['brand'], 'descrip' => $all['descrip'], 'size' => $all['size'], 'flavor' => $all['flavor']);
}

print_r($results);

//{ echo "<p>".$cat."</p>"; 

//echo "<b><font face='arial' size='2'><u>(".$quant.
//") </b></font></u><font face='arial' size='2'>".$brand.", ".
//" - "."<font color='grey' size='-1'><i>".$descrip." - ".$flavor.
//"</i></font> - <i><font color='purple' size='-3'></i>".$size.
//"</font><br>";
//}
?>

 

you will see the contents of $results array is formatted in the format:

$results['cat'][int]['otherFieldNames']

 

where int is an integer value for each result under the category 'cat'.

 

example:

$results['Canned Food'][0]['quant'] = '11 Cans'

$results['Canned Food'][0]['brand'] = 'Kroger'

$results['Canned Food'][0]['flavour'] = 'Sweet Corn'

etc.

 

to output the values, you can simply use some loops:

foreach ($results as $catName => $catData)
{
   print('<b>'.$catName.'</b><br/><br/>'."\n");
   foreach ($catData as $itemNum => $itemData)
   {
      // if you want to access the row data in this loop, use the following method:
      print($itemData['brand'].', '.$itemData['flavour'].' - '.$itemData['quant'].'<br/>'."\n"); // etc. (you must code the field names in hard coded this way)
      foreach ($itemData as $fieldName => $value)
      {
          // this will output all the data of the row, but it is outputting 1 field at a time. you aren't accessing all the field names at once like the parent loop does
         print($fieldName.': '.$value.'<br/>'."\n");
      }
   }
}