Help with MySQL GROUP BY and SQL statement

Collapse
X
Collapse
 
  • Page of 1
  • Time
  • Show
Clear All
new posts
Previous template Next
  • gnawz
    New Member
    • Nov 2006
    • 64
    #1

    Help with MySQL GROUP BY and SQL statement

    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
    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']; }?> </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">&nbsp;</td>
    <td width="15">&nbsp;</td>
    <td width="15">&nbsp;</td>
    <td width="15">&nbsp;</td>
    <td width="15">&nbsp;</td>
    <td width="15">&nbsp;</td>
    <td width="15">&nbsp;</td>
    <td width="15">&nbsp;</td>
    <td width="15">&nbsp;</td>
    <td width="15">&nbsp;</td>
    <td width="15">&nbsp;</td>
    <td width="15">&nbsp;</td>
    <td width="15">&nbsp;</td>
    <td width="15">&nbsp;</td>
    <td width="15">&nbsp;</td>
    </tr>
  
     <?php
	} // end while


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

<a href="javascript:void(printSpecial())">Print this Page</a> 
<h4><a href="index.php">Back</a> to stock panel
</body>
</html>
    Tags: None
    • nomad
      Recognized Expert Contributor
      • Mar 2007
      • 664
      #2
      Question do you have a database set up?
      if you don't you will need at least two tables.
      One for the Product and the other for the category.
      Products would be apples, banana and so on.
      Category would be Fruit, Junk food.

      Then you would write a simple php code to retrieve the info.

      nomad

        Comment

        • GazMathias
          Recognized Expert New Member
          • Oct 2008
          • 228
          #3
          Originally posted by nomad
          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
          Hi there,

          I recently tackled ths problem myself in ASP, I've translated it to PHP for you, though I've used some test data from the MySQL site for this demonstration (view the pic below).

          Code:
          <table border ="1">
	
<?php

$sql = mysql_query("SELECT * FROM country ORDER BY Continent");

  $Cont =""; // We declare a variable not like any in our database, in this case a null string.
  
    While ($result = mysql_fetch_array($sql)) {
	  If ($Cont <> $result['Continent']) { // If this row's continent is not the same as the last row's...
	    echo "<tr colspan = '3'><td><h4>". $result['Continent']."</h4></td></tr>"; // we print a header.
	  }
	  $Cont = $result['Continent']; // We set the checking variable to this record's value, for the next row to check.  
      echo "<tr><td>".$result['Name']."</td>";
	    echo "<td>".$result['Region']."</td>"; // and then we print the data we want, regardless.
	  echo "<td>".$result['Population']."</td>";
	echo "</tr>";
   }
   
?>

</table>
          The result is:



          Hope that helps!

          Gaz

            Comment

            Previous template Next
            Working...