SQL Query and Displaying help

Hi Everyone,

I am really stuck as to how to do this. Take a look at this page:

http://www.freemanholland.com/hanging/product-range/

You can see a list of “Wines” going a long way down the page. Columns are Style, Name, Cost & Country.

Now this is displayed with the following SQL query:


    public function selectProductCategoriesByID($ID){

        $sql = "SELECT * FROM tbl_product_categories WHERE ID = $ID";
        $result = mysql_query($sql);
        return $result;
    }

And the $ID is retrieved via the URL ($_GET)…

Now what i need to do is GROUP the reults by the “Style” column. In my database i have the following:

ID, style, name, cost, country, date_added, catID, deleted

Each set of wines below to a specific catID, so its fairly simple. But if you look at the page the list goes on and on so i need to group these like so:

Aromatic, Aromatic Magnum, Blended, Chardonnay and so on…

The way i want this to be displayed is as such:

<h2>Aromatic</h2>

//List of wines

<br/>

<h2>Aromatic Magnum</h2>

//List of wines

<br/>

<h2>Blended</h2>

//List of wines

So how can i accomplish this? On the page i display the results i have this code:


        <?		
        $products = Products::selectAllProductsByCatID($_GET['catID']);
            $i=0;
      		while($row = mysql_fetch_array($products)){ 
               echo "<tr class=\\"row$i\\"><td class='tdrow'>$row[style]</td><td class='tdrow'>$row[name]</td><td class='tdrow'>&pound;$row[cost]</td><td class='tdrow'>$row[country]</td>";
               	$i++;
			    $i = $i % 2;
	        }
	      ?>	

How can i change this to show the wines how i want? Can someone please help?

Thanks again

Move the table start and table end tags so that they surround the while loop, then just before the table start tags place your heading.

Other more approved ways are to store the list of wines from the database in an array, and to then later on iterate through them for the display. That can mean more coding work for you, but it nicely separates the processes of gathering data, and displaying data.

See this: http://www.sitepoint.com/forums/showpost.php?p=4558479&postcount=3

Of course, you’ll have to tweak it a bit to make it work with your data.

Guido helped me with the problem - that’s what the post above is for - and it worked a dream - Ive used the code in the link above on my site - My problem was that i wanted to have headings the same as you but group them by a date.
My implementation of that code can be be seen at www.footballud.com and i can definitely recommend it as it will do exactly what you need and you can use one sql statement to get the full list.

Hey,

Right i am working on this now…

I have the following code:


        $products = Products::selectAllProductsByCatID($_GET['catID']);
            $i=0;
            $saveDate = '';
      		while($row = mysql_fetch_array($products)){ 
      		  if ($row['brand'] != $saveDate) {
			    echo "<h3>$row[brand]</h3>";
			    $saveDate = $row['brand'];
			  }
               echo "<tr class=\\"row$i\\"><td class='tdrow'>$row[brand]</td><td class='tdrow'>$row[name]</td><td class='tdrow'>&pound;$row[cost]</td><td class='tdrow'>$row[country]</td>";
               	$i++;
			    $i = $i % 2;
	        }

This is the SQL statement:


    public function selectAllProductsByCatID($catID){

        $sql = "SELECT * FROM tbl_products WHERE catID = $catID
        AND deleted = 0";
        $result = mysql_query($sql);
        return $result;
    }

If you take a look on this page:

http://www.freemanholland.com/hanging/product-range/?catID=1&type=White%20wine

It seems to be outputting the “brand” ok but now at the right location. the style column will be removed once i get this code working but you can see that the column is not separated by headings. The headings ALL appear at the top, now this is not right. The products should be listed WITHIN each heading.

Can anyone help me as to why this is not working??

Thanks

Hey,

Just so you know the column in my very first post called ‘style’ has not been changed to ‘brand’. Now i am getting closer to what i need to do, if you take a look at the page now:

http://www.freemanholland.com/hanging/product-range/?catID=4&type=Champagne

But now i get the right headings, but they repeat over and over, and again they are not grouped within each ‘brand’…

I added ‘ORDER BY brand’ to my SQL, and i get the result you can see in the link i have provided.

Can anyone please advise…?

Order by brand and then by name.

Then you can loop through all of the results, and when the brand changes you can take other action, such as showing it as a heading.

It’s a common situation, and in fact someone else was helped with this yesterday. The pseudo-code from that help should be of great help for you.

Hey,

I’ve tried Order by brand, name and i now have the following code:


            $i=0;
            $saveBrand = '';
      		while($row = mysql_fetch_array($products)){ 
      		  if ($row['brand'] != $saveBrand) {
			    echo "<h3>$row[brand]</h3>";
			    $saveBrand = $row['brand'];
			  }
               echo "<tr class=\\"row$i\\"><td class='tdrow'>$row[brand]</td><td class='tdrow'>$row[name]</td><td class='tdrow'>&pound;$row[cost]</td><td class='tdrow'>$row[country]</td>";
               	$i++;
			    $i = $i % 2;
	        }

If you look at the page now the headings are ALL together at the top of the page. Am i missing something?

Thanks

I think that you’ve finished the while loop too early. The echo and $i stuff should be in there too, but you need to pay attention to HTML code structure. It’s not valid to put a heading where a table row is expected.

Are you wanting to close the table, show the heading, then start a new table after the heading?

If so, you’ll also want to check if it’s the first heading so that you don’t perform the close table part on the first heading.


            <?php
            
            // test data
            /*$products = array(
            	array('brand'=>'Aromatic','name'=>'(B) Arrogant Frog Ribet White Chardonnay Viognier 2008 13.5&#37;','cost'=>'10.00','country'=>'France')
            	,array('brand'=>'Aromatic','name'=>'(B) Arrogant Frog Ribet White Chardonnay Viognier 2008 13.5%','cost'=>'10.00','country'=>'France')
            	,array('brand'=>'Blended','name'=>'(B) Borgo Selene Bianco Sicilia','cost'=>'6','country'=>'Italy')
            	,array('brand'=>'Blended','name'=>'(B) Borgo Selene Bianco Sicilia','cost'=>'6','country'=>'Italy')
            	,array('brand'=>'Chardonnay','name'=>'(B) (B) Cobos Felino Chardonnay 2008 15%','cost'=>'13.50','country'=>'Argentina')
            	,array('brand'=>'Dry & Crisp','name'=>'(B) M Antigos Alvarinho 2008 13%','cost'=>'20.00','country'=>'Portugal')
            );*/
            
            echo
            	'<table cellspacing="0" width="100%">
            	    <thead>
            	    	<tr>
            	    		<th>Name</th>
            	    		<th>Cost:</th>
            	    		<th>Country</th>
            	    	</tr>
            	    </thead>
            	    <tbody>';
            $saveBrand = '';
            /*foreach($products as $row) {*/while($row = mysql_fetch_array($products)) {
            	
            	if($row['brand'] != $saveBrand) {      		
					printf(
						'<tr style="background-color: red;">
							<td colspan="3">%s</td>
						 </tr>'
						 ,$row['brand']
					);
					
					$cycle = false;
					$saveBrand = $row['brand'];         		
            	}      	
            	printf(
            		'<tr%s>
            			<td>%s</td>
            			<td>&pound;%s</td>
            			<td>%s</td>
            		 </tr>'
            		 ,$cycle =! $cycle?' style="background-color:#ccc;"':' style="background-color:#999;"'
            		 ,$row['name']
            		 ,$row['cost']
            		 ,$row['country']
            	);       	
            }      
            echo '</tbody>
            </table>';
            ?>

This is were an understanding of HTML comes in handy. You can replace the inline styles with classes at your discretion to create styling hooks for brand headings and alternating rows.

WOW, thank you so much for this, it works perfectly, i simplified the code to my understanding as i didnt know why %s was used. Here is my code:


<?
        $products = Products::selectAllProductsByCatID($_GET['catID']);
        ?>
			<table cellspacing="0" width="100%">
                <thead>
                    <tr>
                        <th style="width: 400px; text-align:left; color:#b92923; font-weight:bold">Name</th>
                        <th style="width: 103px; text-align:left; color:#b92923; font-weight:bold">Cost:</th>
                        <th style="color:#b92923; font-weight:bold; text-align:left;">Country</th>
                    </tr>
                </thead>
        <?
		//start iterating   
        $saveBrand = '';
        $i=0;
            while($row = mysql_fetch_array($products)) {      
                if($row['brand'] != $saveBrand) {              
                    echo "<tr class='tdrow' style='font-size: 16px; height: 20px; line-height: 38px; border-bottom:1px solid #999'><td>$row[brand]</td></tr>";               
                    $cycle = false;
                    $saveBrand = $row['brand'];                 
                }          
                echo "<tr class=\\"row$i\\"><td class='tdrow'>$row[name]</td><td class='tdrow'>&pound;$row[cost]</td><td class='tdrow'>$row[country]</td></tr>"; 
                $i++;
			    $i = $i % 2;           
                }      
            ?>	 
           </table> 

Now this gives me what i want however i wish to alter something but i’m not sure as to how it should be done.

If you look on this page for example:

http://www.freemanholland.com/hanging/product-range/?catID=1&type=White%20wine

The table headings Name, Cost, and Country appear only once at the top and then the “brand” heading with the wine lists. I want to re-arrange the structure so it shows the “brand” heading first and THEN Name, Cost, Country, THEN the Winelist. And keep repeating this…

So i would want it like this:

Aromatic //this is the “brand” heading

Name, Cost, Country //these are the table header headings

Show the wine list //Show the winelist

And then repeat this process over and over depending on how many brands there are.

How can i do this?

Thanks again


<?

        $products = Products::selectAllProductsByCatID($_GET['catID']);

        ?>

            <table cellspacing="0" width="100&#37;">

                <thead>

                    <tr>

                        <th style="width: 400px; text-align:left; color:#b92923; font-weight:bold">Name</th>

                        <th style="width: 103px; text-align:left; color:#b92923; font-weight:bold">Cost:</th>

                        <th style="color:#b92923; font-weight:bold; text-align:left;">Country</th>

                    </tr>

                </thead>

        <?

        //start iterating   

        $saveBrand = '';

        $i=0;

            while($row = mysql_fetch_array($products)) {      

                if($row['brand'] != $saveBrand) {              

                    echo "<tr class='tdrow' style='font-size: 16px; height: 20px; line-height: 38px; border-bottom:1px solid #999'><td>$row[brand]</td></tr><tr><td>Product</td><td>Cost:</td><td>Country</td></tr>";               

                    $cycle = false;

                    $saveBrand = $row['brand'];                 

                }          

                echo "<tr class=\\"row$i\\"><td class='tdrow'>$row[name]</td><td class='tdrow'>&pound;$row[cost]</td><td class='tdrow'>$row[country]</td></tr>"; 

                $i++;

                $i = $i % 2;           

                }      

            ?>     

           </table>

That would be the quick and dirty considering one header per table. The alternate is to break up each brand into its own table.

I prefer the alternate you have suggested, would i be able to break up my existing display into separate tables of their own brand?

Would this be difficult to do?

Hey,

Actually breaking the tables up into their own brand may not be necessary, however i have one minor aspect left to complete this page…

I want to check to see if there is only ONE brand then there is no need to show the heading, so for example this page:

http://www.freemanholland.com/hanging/product-range/?catID=3&type=Rose%20wine

The client has asked to remove the “Rose” heading as its no necessary. If there is more than one then headings are fine.

So this is my code so far, would this be quite simple to do? Would i need to do mysql_num_rows? How can i do this?


<?
        $products = Products::selectAllProductsByCatID($_GET['catID']);
        ?>
			<table cellspacing="0" width="100%">
                <thead>
                    <tr>
                        <th style="width: 400px; text-align:left; color:#b92923; font-weight:bold">Name</th>
                        <th style="width: 103px; text-align:left; color:#b92923; font-weight:bold">Cost:</th>
                        <th style="color:#b92923; font-weight:bold; text-align:left;">Country</th>
                    </tr>
                </thead>
        <?
		//start iterating   
        $saveBrand = '';
        $i=0;
            while($row = mysql_fetch_array($products)) {      
                if($row['brand'] != $saveBrand) {              
                    echo "<tr class='tdrow' style='font-size: 16px; height: 20px; line-height: 38px; border-bottom:1px solid #999'><td>$row[brand]</td></tr>";               
                    $cycle = false;
                    $saveBrand = $row['brand'];                 
                }          
                echo "<tr class=\\"row$i\\"><td class='tdrow'>$row[name]</td><td class='tdrow'>&pound;$row[cost]</td><td class='tdrow'>$row[country]</td></tr>"; 
                $i++;
			    $i = $i % 2;           
                }      
            ?>	 
           </table> 

Thanks again

You could add a count field to your SQL results, calling it count(brand) AS brandCount

Hey,

I have tried doing this:


        $sql = "SELECT *, count(brand) AS brandName FROM tbl_products WHERE catID = $catID
        AND deleted = 0 ORDER BY brand, name";

But i get the following error:

Mixing of GROUP columns (MIN(),MAX(),COUNT(),…) with no GROUP columns is illegal if there is no GROUP BY clause

What do i need to do? Do i need to GROUP BY all the fields in my table?

Fields in my table are as follows:

ID, brand, name, cost, country, date_added, catID, deleted

Any ideas?

The people in our MySQL forum may well have better ideas about this, but what can work is to inner join a sub-select as the count.


SELECT tblproducts.*, brands.brandCount
FROM tbl_products INNER JOIN (
    SELECT brand, count(brand) as brandCount
    FROM tbl_products
    GROUP BY brand
) AS brands on tbl_products.brand = brands.brand
WHERE
    catID = $catID
    AND deleted = 0
ORDER BY brand, name

That should be close to what will work. Keeping in mind that this is the PHP Forum, I’ll be interested to hear what people with more SQL expertise have to say.

Hey,

Well i have applied that SQL statement to my code, and it seems to work, however it does not display how i wanted.

For example on this page There is no need to have the “Dessert” heading, so i need to now check to see if the count of brand = 1 then dont show the heading, so any ideas how i can do this with the code i have shown below?


    public function selectAllProductsByCatID($catID){

        $sql = "SELECT tbl_products.*, brands.brandCount
				FROM tbl_products INNER JOIN (
				    SELECT brand, count(brand) AS brandCount
				    FROM tbl_products
				    GROUP BY brand
				) AS brands ON tbl_products.brand = brands.brand
				WHERE
				    catID = $catID
				    AND deleted = 0
				ORDER BY brand, name";
        $result = mysql_query($sql) or die(mysql_error());
        return $result;
    }


        <?		
        $products = Products::selectAllProductsByCatID($_GET['catID']);
        ?>
			<table cellspacing="0" width="100%">
                <thead>
                    <tr>
                        <th style="width: 400px; text-align:left; color:#b92923; font-weight:bold">Name</th>
                        <th style="width: 103px; text-align:left; color:#b92923; font-weight:bold">Cost:</th>
                        <th style="color:#b92923; font-weight:bold; text-align:left;">Country</th>
                    </tr>
                </thead>
        <?
		//start iterating   
        $saveBrand = '';
        $i=0;
            while($row = mysql_fetch_array($products)) {      
                if($row['brand'] != $saveBrand) {              
                    echo "<tr class='tdrow' style='font-size: 16px; height: 20px; line-height: 38px; border-bottom:1px solid #999'><td>$row[brand]</td></tr>";               
                    $cycle = false;
                    $saveBrand = $row['brand'];                 
                }          
                echo "<tr class=\\"row$i\\"><td class='tdrow'>$row[name]</td><td class='tdrow'>&pound;$row[cost]</td><td class='tdrow'>$row[country]</td></tr>"; 
                $i++;
			    $i = $i % 2;           
                }      
            ?>	 
           </table> 

So the logic should be, if there is more than one “brand” then don’t show.

Can someone help?

Hey,

I’ve noticed something, i tried echoing out the bradnCount and i got 24 on this page:

http://www.freemanholland.com/hanging/product-range/?catID=6&type=Dessert

But, there should only be ONE as for every row returned the brand is the same. So i think i need to alter the SQL statement somehow. I tried doing this a specific section of the code which makes sense:


            while($row = mysql_fetch_array($products)) {  
                if($row['brand'] != $saveBrand) { 
	        		if($row['brandCount'] > 1){           
	                    echo "<tr class='tdrow' style='font-size: 16px; height: 20px; line-height: 38px; border-bottom:1px solid #999'><td>$row[brand]</td></tr>";               
	                    $cycle = false;
	                    $saveBrand = $row['brand'];   
	                }              
                }          
                echo "<tr class=\\"row$i\\"><td class='tdrow'>$row[name]</td><td class='tdrow'>&pound;$row[cost]</td><td class='tdrow'>$row[country]</td></tr>"; 
                $i++;
			    $i = $i % 2;   
			    echo "<tr><td>brandCount is $row[brandCount]</td></tr>";         
            }

So the second IF checks to see if brandCount is more than 1 which it should not be on the page i have gave a link to above. Then if it is it should read out the headings…

Why does this not work?? Notice i tried echoing out the brand Count to see what was going on and it returns 24 which is the number of rows returned which i don’t want, i need to number of brand types as such…

Please help :confused:

As it’s not the brand column that you want to count, what is it that you do want to count? The number of different brands that have been returned?

This really is not the right place for this discussion. Our MySQL forum is where you will find people who are more expert such aspects of MySQL.