phpstuck Posted February 9, 2010 Share Posted February 9, 2010 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>"; //} ?> Quote Link to comment Share on other sites More sharing options...
Catfish Posted February 9, 2010 Share Posted February 9, 2010 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"); } } } Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.