By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
437,949 Members | 1,855 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 437,949 IT Pros & Developers. It's quick & easy.

Help with MySQL GROUP BY and SQL statement

P: 64
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
Expand|Select|Wrap|Line Numbers
  1. <?
  2.     require_once '../../functions.php';
  3.  
  4. ?>
  5. <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
  6. <html xmlns="http://www.w3.org/1209/xhtml">
  7. <head>
  8. <meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1" />
  9. <title>STOCK FORM</title>
  10. <script language="JavaScript">
  11. var gAutoPrint = true; // Flag for whether or not to automatically call the print function
  12.  
  13. function printSpecial()
  14. {
  15.     if (document.getElementById != null)
  16.     {
  17.         var html = '<HTML>\n<HEAD>\n';
  18.  
  19.         if (document.getElementsByTagName != null)
  20.         {
  21.             var headTags = document.getElementsByTagName("head");
  22.             if (headTags.length > 0)
  23.                 html += headTags[0].innerHTML;
  24.         }
  25.  
  26.         html += '\n</HE' + 'AD>\n<BODY>\n';
  27.  
  28.         var printReadyElem = document.getElementById("printReady");
  29.  
  30.         if (printReadyElem != null)
  31.         {
  32.                 html += printReadyElem.innerHTML;
  33.         }
  34.         else
  35.         {
  36.             alert("Could not find the printReady section in the HTML");
  37.             return;
  38.         }
  39.  
  40.         html += '\n</BO' + 'DY>\n</HT' + 'ML>';
  41.  
  42.         var printWin = window.open("","printSpecial");
  43.         printWin.document.open();
  44.         printWin.document.write(html);
  45.         printWin.document.close();
  46.         if (gAutoPrint)
  47.             printWin.print();
  48.     }
  49.     else
  50.     {
  51.         alert("Sorry, the print ready feature is only available in modern browsers.");
  52.     }
  53. }
  54.  
  55. </script>
  56.  
  57. <style type="text/css">
  58.  
  59. .lines td {
  60. border: #666666 solid 1px;
  61. }
  62.  
  63. </style>
  64. </head>
  65.  
  66. <body>
  67.  
  68. <div id="printReady">
  69.  
  70. <table width="100%" border="0" class="lines">
  71.   <tr>
  72.     <?
  73.     $sqlTitle = "SELECT * FROM fragrancestock GROUP BY Category";
  74.     $result  = dbQuery($sqlTitle );
  75.     while($row = dbFetchArray($result))
  76.     {
  77.  
  78.  
  79.     ?>
  80.     <td colspan="2"><div align="center"><strong><? echo $row['Category']; }?> </strong></div></td>
  81.     <?
  82.     $thismonth = mktime(0,0,0,date("m"),date("d"),date("Y"));
  83.  
  84.     $nextmonth = mktime(0,0,0,date("m")+1,date("d"),date("Y"));
  85.  
  86.     $montheafternext = mktime(0,0,0,date("m")+2,date("d"),date("Y"));
  87.  
  88.     ?>
  89.     <td colspan="5"><div align="center"><strong><? echo date("F", $thismonth); ?></strong></div></td>
  90.     <td colspan="5"><div align="center"><strong><? echo date("F", $nextmonth); ?></strong></div></td>
  91.     <td colspan="5"><div align="center"><strong><? echo date("F",$montheafternext); ?></strong></div></td>
  92.   </tr>
  93.   <tr align="center">
  94.     <td width="250"><em><strong>Brand</strong></em></td>
  95.     <td width="15"><em><strong>Qty</strong></em></td>
  96.     <td width="15">Day</td>
  97.     <td width="15">Sold</td>
  98.     <td width="15">Day</td>
  99.     <td width="15">Sold</td>
  100.     <td width="15"><em><strong>Bal</strong></em></td>
  101.     <td width="15">Day</td>
  102.     <td width="15">Sold</td>
  103.     <td width="15">Day</td>
  104.     <td width="15">Sold</td>
  105.     <td width="15"><em><strong>Bal</strong></em></td>
  106.     <td width="15">Day</td>
  107.     <td width="15">Sold</td>
  108.     <td width="15">Day</td>
  109.     <td width="15">Sold</td>
  110.     <td width="15"><em><strong>Bal</strong></em></td>
  111.     </tr>
  112.     <?
  113.  
  114.     $sql = "SELECT * FROM fragrancestock ORDER BY Category ASC";
  115.     $result = dbQuery($sql);
  116.  
  117.     if (dbNumRows($result) > 0) {
  118.         $i = 0;
  119.  
  120.         while($row = dbFetchAssoc($result)) {
  121.             extract($row);
  122.  
  123.             if ($i%2) {
  124.                 $class = 'row1';
  125.             } else {
  126.                 $class = 'row2';
  127.             }
  128.  
  129.             $i += 1;
  130. ?>
  131.   <tr class="<?php echo $class; ?>">
  132.     <td width="250"><?php echo $Brand; ?></td>
  133.     <td width="15"><?php echo $Quantity; ?></td>
  134.     <td width="15">&nbsp;</td>
  135.     <td width="15">&nbsp;</td>
  136.     <td width="15">&nbsp;</td>
  137.     <td width="15">&nbsp;</td>
  138.     <td width="15">&nbsp;</td>
  139.     <td width="15">&nbsp;</td>
  140.     <td width="15">&nbsp;</td>
  141.     <td width="15">&nbsp;</td>
  142.     <td width="15">&nbsp;</td>
  143.     <td width="15">&nbsp;</td>
  144.     <td width="15">&nbsp;</td>
  145.     <td width="15">&nbsp;</td>
  146.     <td width="15">&nbsp;</td>
  147.     <td width="15">&nbsp;</td>
  148.     <td width="15">&nbsp;</td>
  149.     </tr>
  150.  
  151.      <?php
  152.     } // end while
  153.  
  154.  
  155. ?>
  156. </table>
  157. </div>
  158. <table width="100%" border="0">
  159.   <tr>
  160.     <td></td>
  161.   </tr>
  162.            <?php    
  163. } else {
  164. ?>
  165.   <tr>
  166.     <td>&nbsp;</td>
  167.   </tr>
  168.   <tr>
  169.   No stock items yet</td>
  170.        <?php
  171. }
  172. ?>
  173.     <td>&nbsp;</td>
  174.   </tr>
  175. </table>
  176.  
  177. <a href="javascript:void(printSpecial())">Print this Page</a> 
  178. <h4><a href="index.php">Back</a> to stock panel
  179. </body>
  180. </html>
  181.  
  182.  
Oct 10 '08 #1
Share this Question
Share on Google+
2 Replies


nomad
Expert 100+
P: 664
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
Oct 10 '08 #2

GazMathias
Expert 100+
P: 189
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).

Expand|Select|Wrap|Line Numbers
  1. <table border ="1">
  2.  
  3. <?php
  4.  
  5. $sql = mysql_query("SELECT * FROM country ORDER BY Continent");
  6.  
  7.   $Cont =""; // We declare a variable not like any in our database, in this case a null string.
  8.  
  9.     While ($result = mysql_fetch_array($sql)) {
  10.       If ($Cont <> $result['Continent']) { // If this row's continent is not the same as the last row's...
  11.         echo "<tr colspan = '3'><td><h4>". $result['Continent']."</h4></td></tr>"; // we print a header.
  12.       }
  13.       $Cont = $result['Continent']; // We set the checking variable to this record's value, for the next row to check.  
  14.       echo "<tr><td>".$result['Name']."</td>";
  15.         echo "<td>".$result['Region']."</td>"; // and then we print the data we want, regardless.
  16.       echo "<td>".$result['Population']."</td>";
  17.     echo "</tr>";
  18.    }
  19.  
  20. ?>
  21.  
  22. </table>
The result is:



Hope that helps!

Gaz
Oct 16 '08 #3

Post your reply

Sign in to post your reply or Sign up for a free account.