Help on MySQL GROUP BY and general sql

gnawz · October 10, 2008

I have a table that consists of 5 categories and each category has items called brands.

I want to be able to list the Category of each set of brands on top of the list using PHP and MySQL in tabular form

Some thing like this

Fruits

apples

mangoes

oranges

Snacks

hotdog

popcorn

cake

Below is my code

<?
require_once '../../functions.php';

?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1209/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1" />
<title>STOCK FORM</title>
<script language="JavaScript">
var gAutoPrint = true; // Flag for whether or not to automatically call the print function

function printSpecial()
{
if (document.getElementById != null)
{
	var html = '<HTML>\n<HEAD>\n';

	if (document.getElementsByTagName != null)
	{
		var headTags = document.getElementsByTagName("head");
		if (headTags.length > 0)
			html += headTags[0].innerHTML;
	}

	html += '\n</HE' + 'AD>\n<BODY>\n';

	var printReadyElem = document.getElementById("printReady");

	if (printReadyElem != null)
	{
			html += printReadyElem.innerHTML;
	}
	else
	{
		alert("Could not find the printReady section in the HTML");
		return;
	}

	html += '\n</BO' + 'DY>\n</HT' + 'ML>';

	var printWin = window.open("","printSpecial");
	printWin.document.open();
	printWin.document.write(html);
	printWin.document.close();
	if (gAutoPrint)
		printWin.print();
}
else
{
	alert("Sorry, the print ready feature is only available in modern browsers.");
}
}

</script>

<style type="text/css">

.lines td {
border: #666666 solid 1px;
}

</style>
</head>

<body>

<div id="printReady">

<table width="100%" border="0" class="lines">
  <tr>
<?
$sqlTitle = "SELECT * FROM fragrancestock GROUP BY Category";
$result  = dbQuery($sqlTitle );
while($row = dbFetchArray($result))
{


?>
    <td colspan="2"><div align="center"><strong><? echo $row['Category']; /////Title of category here///////}?> </strong></div></td>
<?
$thismonth = mktime(0,0,0,date("m"),date("d"),date("Y"));

$nextmonth = mktime(0,0,0,date("m")+1,date("d"),date("Y"));

$montheafternext = mktime(0,0,0,date("m")+2,date("d"),date("Y"));

?>
    <td colspan="5"><div align="center"><strong><? echo date("F", $thismonth); ?></strong></div></td>
    <td colspan="5"><div align="center"><strong><? echo date("F", $nextmonth); ?></strong></div></td>
    <td colspan="5"><div align="center"><strong><? echo date("F",$montheafternext); ?></strong></div></td>
  </tr>
  <tr align="center">
    <td width="250"><em><strong>Brand</strong></em></td>
    <td width="15"><em><strong>Qty</strong></em></td>
    <td width="15">Day</td>
    <td width="15">Sold</td>
    <td width="15">Day</td>
    <td width="15">Sold</td>
    <td width="15"><em><strong>Bal</strong></em></td>
    <td width="15">Day</td>
    <td width="15">Sold</td>
    <td width="15">Day</td>
    <td width="15">Sold</td>
    <td width="15"><em><strong>Bal</strong></em></td>
    <td width="15">Day</td>
    <td width="15">Sold</td>
    <td width="15">Day</td>
    <td width="15">Sold</td>
    <td width="15"><em><strong>Bal</strong></em></td>
    </tr>
<?

$sql = "SELECT * FROM fragrancestock ORDER BY Category ASC";
$result = dbQuery($sql);

if (dbNumRows($result) > 0) {
	$i = 0;

	while($row = dbFetchAssoc($result)) {
		extract($row);

		if ($i%2) {
			$class = 'row1';
		} else {
			$class = 'row2';
		}

		$i += 1;
?>
  <tr class="<?php echo $class; ?>">
    <td width="250"><?php echo $Brand; ?></td>
    <td width="15"><?php echo $Quantity; ?></td>
    <td width="15"> </td>
    <td width="15"> </td>
    <td width="15"> </td>
    <td width="15"> </td>
    <td width="15"> </td>
    <td width="15"> </td>
    <td width="15"> </td>
    <td width="15"> </td>
    <td width="15"> </td>
    <td width="15"> </td>
    <td width="15"> </td>
    <td width="15"> </td>
    <td width="15"> </td>
    <td width="15"> </td>
    <td width="15"> </td>
    </tr>
  
     <?php
} // end while


?>
</table>
</div>
<table width="100%" border="0">
  <tr>
    <td></td>
  </tr>
           <?php	
} else {
?>
  <tr>
    <td> </td>
  </tr>
  <tr>
  No stock items yet</td>
   <?php
}
?>
    <td> </td>
  </tr>
</table>

<a href="javascript:void(printSpecial())">Print this Page</a> 
<h4><a href="index.php">Back</a> to stock panel
</body>
</html>

]

Sign In

Help on MySQL GROUP BY and general sql

Recommended Posts

gnawz

Link to comment

Share on other sites

Join the conversation

Browse

Activity

Important Information