group by in 1 query

samoht · September 20, 2007

Hello again,

still trying to get this query to split up my items and group all the products that belong to that item?

<?php
SELECT i.Name AS ItemName, pd.ItemId, pd.ProductId, pd.Name, pp.PriceSell, i.ImagetnURL, pd.QtyInStock 
FROM product pd, item i, productprice pp, itemfeatures itf, features f
WHERE f.Name = '$pageTitle'
			AND itf.FeatureId = f.FeatureId
			AND itf.ItemId = i.ItemId
			AND pd.ItemId = i.ItemId
			AND pd.ProductId = pp.ProductId
			AND ClientPriceCode = '$CPC'

I want to get my display to be something like:

<?php
echo "<td width=\"$columnWidth%\" align=\"center\">
		<p><a href=\"" . $_SERVER['PHP_SELF'] . "?c=$catId&p=$ProductId" . "\">
		<img src=\"$ImagetnURL\" border=\"0\"><br>$ItemName</a>";
//right here I want to add all the products that belong to this Item!
		<br><span class=\"cs\">$Size $PackageType - $PriceSell</span>";

any ideas??

darkfreaks · September 20, 2007

did you try chunk_split? read up on it.

samoht · September 21, 2007

Not sure if Chunk_Split will work

isn't that for breaking up long lines of text?

I want to have a loop or something get the products that share the same ItemId and display those together

so the out put would be:

Item#1 Name

product#1 for item #1 : productprice

product#2 for item #1 : productprice

product#3 for item #1 : productprice

etc.

Jessica · September 21, 2007

what about GROUP BY?

samoht · September 21, 2007

When I try GROUP BY

I get only one product per Item??

Jessica · September 21, 2007

Sorry, i don't understand the problem. Can you try to explain it more? What do you get, what do you want to get? Is the problem the SQL selecting the wrong things or you can't display them right?

samoht · September 21, 2007

um - I want a output that groups the many products to their 1 item

I think I need a loop through my query at the outset

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

Then some code to maybe set a variable for the current Item like

$currentItem = $ItemId;

Then my main echo:

<?php
echo "<td width=\"$columnWidth%\" align=\"center\">
		<p><a href=\"" . $_SERVER['PHP_SELF'] . "?c=$catId&p=$ProductId" . "\">
		<img src=\"$ImagetnURL\" border=\"0\"><br>$ItemName</a>";

and then some other loop that gets all the products while on this currentItem - and then at the end of the loop it looks for the next item.

mock out put:

KitKat, - 1st Item

Kingsize : $25.60 - 1st Product + product price

Funsize : $12.50 - 2nd etc

Almond Joy, - 2nd Item

Kingsize : $33.55 - 1st Product + price

Miniatures : $21.25 - 2nd etc.

does this help?

php_dave · September 21, 2007

Hi,

Something like this maybe? Not tested but along the right idea i think..

$group = "";

while ($data = dbFetchAssoc($result))
{
  if ($data['Item_Name'] != $group)
  {
     Echo header detail here
     $group = $data['Item_name'];
  }
  
Echo mutilple details here...
}

samoht · September 21, 2007

thanks for the help

I still have some problems though because of my structure (I think)

<?php 
if ($numProduct > 0 ) {

$i = 0;
$group = "";
while ($row = dbFetchAssoc($result)) {

	extract($row);
	if($Id != $group)
	{
  		if ($Size == 'Varies')
  		{
  			$Size = '';
  		}
  		if ($PackageType=='Bulk') 
  		{
  			$PackageType = "per lb";
  		}		
  		if ($ImagetnURL) 
  		{
  			$ImagetnURL = $path.'images/item/' . $ImagetnURL;
  		} else {
  			$ImagetnURL = $path . 'images/no-image-small.png';
  		}
  	
  		if ($i % $productsPerRow == 0) 
  		{
  			echo '<tr>';
  		}
  
  		// format how we display the price
  		$PriceSell = displayAmount($PriceSell);
  		
  		echo "<td width=\"$columnWidth%\" align=\"center\">
  			<p><a href=\"" . $_SERVER['PHP_SELF'] . "?c=$catId&p=$ProductId" . "\">
  			<img src=\"$ImagetnURL\" border=\"0\"><br>$ItemName</a>";

			$group = $Id;
		}  
  		// if the product is no longer in stock, tell the customer
  		if ($QtyInStock <= 0) 
  		{
  			echo "<br>Out Of Stock";
  		}

	  echo "<br><span class=\"cs\">$Size $PackageType - $PriceSell</span>";
  		//echo "</p></td>\r\n";
  	
  		if ($i % $productsPerRow == $productsPerRow - 1) 
  		{
  			echo '</tr>';
  		}

	$i += 1;
}
echo "</p></td>\r\n";
if ($i % $productsPerRow > 0) {
	echo '<td colspan="' . ($productsPerRow - ($i % $productsPerRow)) . '"> </td>';
}

the $i is counting how many items I have displayed so the user can set up the page as desired

<?php
$productsPerRow = 3;
$productsPerPage = 27;

these variables should really be called itemsPerRow/PerPage since I am trying to group the products under the item.

but any way this code creates this page:

samoht · September 21, 2007

This almost works perfect

<?php
$i = 0;
$group = "";
while ($row = dbFetchAssoc($result)) {

	extract($row);
	//this is attempting to group products under item
	if($Id != $group)
	{
  		echo "</p></td>";
  		if ($ImagetnURL) 
  		{
  			$ImagetnURL = $path.'images/item/' . $ImagetnURL;
  		} else {
  			$ImagetnURL = $path . 'images/no-image-small.png';
  		}
  	
  		if ($i % $itemsPerRow == 0) 
  		{
  			echo '<tr>';
  		}
  		//Item info first
  		echo "\n<td width=\"$columnWidth%\" align=\"center\">
  			<p><a href=\"" . $_SERVER['PHP_SELF'] . "?c=$catId&p=$ProductId" . "\">
  			<img src=\"$ImagetnURL\" border=\"0\"><br>$ItemName</a>";

  			
  		$i += 1;			
			$group = $Id;
		}  
		//Now Product Info...
  		// if the product is no longer in stock, tell the customer
  		if ($QtyInStock <= 0) 
  		{
  			echo "<br>Out Of Stock";
  		}
		if ($Size == 'Varies')//threw this in because varies is not a good name
  		{
  			$Size = '';
  		}
  		if ($PackageType=='Bulk') //per lb is more descrptive
  		{
  			$PackageType = "per lb";
  		}		
		// format how we display the price
  		$PriceSell = displayAmount($PriceSell);

	  echo "<br><span class=\"cs\">$Size $PackageType - $PriceSell</span>";
  		//echo "</p></td>\r\n"; since we have multiple products we cant end here

}
echo "</p></td>\r\n";

the only problem is at the start of my table I get

</p></td><tr>

but I dont know how else to insert these closing tags??

Any help?

samoht · September 21, 2007

Still wondering how I can skip

<?php echo "</p></td>";

on the first go around.

Any help??

php_dave · September 21, 2007

Hey,

Is the above the complete code? I cant see why you have to have Close tags at the beginning of the while loop - or are they supposed to close something above the loop?

As a messy hack - you could do something like

$tags = 0;

While (.../)
{
// normal code
if ($tags > 0)
{
echo ("</td></p>");
}
// rest of code
$tags++;
}

As I say not the prettiest - but without seeing all of your code I cant suggest anything else.

HTH

Dave

samoht · September 22, 2007

that is pretty much all my code but here is the rest;

<?php
if (!defined('WEB_ROOT')) {
exit;
}

$productsPerRow = 3;
$productsPerPage = 27;

//$productList    = getProductList($catId);
$children = array_merge(array($catId), getChildCategories(NULL, $catId));
$children = ' (' . implode(', ', $children) . ')';



$QSelect 	= "SELECT i.Name AS ItemName, pd.ItemId as Id, pd.ProductId, pd.Name, pd.PriceRetail, pp.PriceSell, i.ImagetnURL, QtyInStock, pack.PackageType, s.Name as Size ";
$QFrom 		= "FROM product pd, item i, productprice pp, itemfeatures itf, features f, packaging pack, size s, brands b";
$QWhere		= "WHERE f.Name = '$pageTitle'
			AND pd.PackageId = pack.PackageId
			AND itf.FeatureId = f.FeatureId
			AND itf.ItemId = i.ItemId
			AND i.BrandId = b.BrandId
			AND pd.SizeId = s.SizeId
			AND pd.ItemId = i.ItemId
			AND pd.ProductId = pp.ProductId
			AND ClientPriceCode = '$CPC'";
$QOrderBy	= "ORDER BY pd.Name";

# Add Brand to query.
if (!isset($_GET['brand']) || (isset($_GET['brand']) && $_GET['brand']=="ALL"))
	unset($_SESSION['brand']);
elseif (isset($_GET['brand']))
	$_SESSION['brand'] = $_GET['brand'];

if (isset($_SESSION['brand'])) {
	$QWhere .= $QWhere == '' ? "WHERE i.Code = '$_SESSION[brand]'" : " AND b.Code = '$_SESSION[brand]'";

	# Get name of brand being displayed.
	$qryBrand = "SELECT Name FROM brands WHERE Code = '$_SESSION[brand]'";
	$rsBrand = mysql_query($qryBrand) or die(mysql_error());
	$row_rsBrand = mysql_fetch_assoc($rsBrand);
	$Brand = $row_rsBrand['Name'];
	mysql_free_result($rsBrand);
}

# Add Size to query.
if (!isset($_GET['size']) || (isset($_GET['size']) && $_GET['size']=="ALL"))
	unset($_SESSION['size']);
elseif (isset($_GET['size']))
	$_SESSION['size'] = $_GET['size'];

if (isset($_SESSION['size'])) {
	$QWhere .= $QWhere == '' ? "WHERE pd.SizeId = '$_SESSION[size]'" : " AND s.SizeId = '$_SESSION[size]'";

	# Get name of brand being displayed.
	$qrySize = "SELECT Name FROM size WHERE SizeId = '$_SESSION[size]'";
	$rsSize = mysql_query($qrySize) or die('bad size'.mysql_error());
	$row_rsSize = mysql_fetch_assoc($rsSize);
	$totalRows_rsSize = mysql_num_rows($rsSize);
	$Size = $row_rsSize['Name'];
	mysql_free_result($rsSize);
}


$sql = "SELECT i.Name AS ItemName, pd.ItemId as Id, pd.ProductId, pd.Name, pd.PriceRetail, pp.PriceSell, i.ImagetnURL, QtyInStock, pack.PackageType, s.Name as Size 
	$QFrom
	$QWhere 
	$QOrderBy";
$result     = dbQuery(getPagingQuery($sql, $productsPerPage));
$pagingLink = getPagingLink($sql, $productsPerPage, "g=$cgId");
$numProduct = dbNumRows($result);

// the product images are arranged in a table. to make sure
// each image gets equal space set the cell width here
$columnWidth = (int)(100 / $productsPerRow);
?>
<h1><?php 
switch($cgId){
case 13: 	echo $pageTitle ."!"; break;
case 2: 	echo $pageTitle ."!"; break;
case 7: 	echo $pageTitle ."!"; break;
case 5: 	echo "Over The Counter"; break;
default: 	echo $pageTitle ." Candy!"; break;
} ?></h1>
<p><?php if(isset($_SESSION['brand'])) echo "Brand $Brand "; ?>
<?php if(isset($_SESSION['size'])) echo ": $Size"; ?></p>
<p align="right"><?php echo $pagingLink; ?></p>
<p> </p>
<table width="100%" border="0" cellspacing="0" cellpadding="20">
<?php 
if ($numProduct > 0 ) {

$i = 0;
$group = "";
while ($row = dbFetchAssoc($result)) {

	extract($row);
	if($Id != $group)
	{
		echo "</p></td>";

	if ($ImagetnURL) {
		$ImagetnURL = $path . 'images/item/' . $ImagetnURL;
	} else {
		$ImagetnURL = $path . 'images/no-image-small.png';
	}

	if ($i % $productsPerRow == 0) {
		echo '<tr>';
	}


	echo "<td width=\"$columnWidth%\" align=\"center\" valign=\"top\">
		<p><a href=\"" . $_SERVER['PHP_SELF'] . "?c=$catId&p=$ProductId" . "\">
		<div class=\"pdimg\"><img src=\"$ImagetnURL\"></div><br><h4>$ItemName</h4></a>";
		//<select style=\"width:175px; font-size:9px;\">";

		$i += 1;
		$group = $Id;
	}


	// if the product is no longer in stock, tell the customer
	if ($QtyInStock <= 0) {
		echo "<br>Out Of Stock";
	}
	if ($Size == 'Varies'){
		$Size = '';
	}
	if ($PackageType=='Bulk') {
		$PackageType = "per lb";
	}

	// format how we display the price
	$PriceSell = displayAmount($PriceSell);

	//echo "<option onclick=\"javascript:location='http://server2".$_SERVER['PHP_SELF']."?c=$catId&p=$ProductId"."';\">$Size $PackageType - $PriceSell</option>";					
	echo "<br><div class=\"cs\"><a href=\"".$_SERVER['PHP_SELF']."?c=$catId&p=$ProductId"."';\">$Size $PackageType - $PriceSell</a></div>";		

}
echo "</select></p></td>\r\n";
if ($i % $productsPerRow > 0) {
	echo '<td colspan="' . ($productsPerRow - ($i % $productsPerRow)) . '"> </td>';
}

} else {
?>
<tr><td width="100%" align="center" valign="center">No products in this category</td></tr>
<?php	
}	
?>
<tr><td> </td></tr>
</table>
<p align="center"><?php echo $pagingLink; ?></p>

The closing tags should be placed on the last product of the Item so my html should look like:

      <tr>
        <td width="33%" align="center" valign="top">
          <p>//here the p is started...
            <a href="/bcdist/catalog.php?c=0&p=8455">
          <div class="pdimg">
            <img src="/bcdist/images/item/NSTLY-1001tn.jpg">
          </div><br>
          <h4>
            100 Grand Bar
          </h4></a><br>
          <div class="cs">
            <a href="/bcdist/catalog.php?c=0&p=8455';">King Size 24 Count - $34.14</a>
          </div><br>
          <div class="cs">
            <a href="/bcdist/catalog.php?c=0&p=8456';">36 Count -$32.66</a>
          </div>
         </p>//here the p is closed
        </td>
        <td width="33%" align="center" valign="top">
          <p>
            <a href="/bcdist/catalog.php?c=0&p=11081">
          <div class="pdimg">
            <img src="/bcdist/images/item/AL-2773tn.jpg">
          </div><br>
          <h4>
            2.5 Oz. Milk Choc Turkey
          </h4></a><br>
          <div class="cs">
            <a href="/bcdist/catalog.php?c=0&p=11081';">1 lb - $2.40</a>
          </div>
        </td>
       </p>
      </tr>

The above is just an example with 2 items (normally the </tr> wont come until the 3rd item.

Thanks for the help

cybersmoker · September 22, 2007

Hello samoht,

I have a similar question. Please note that I am a noob.

I have 1 table for categories and 1 for the sub category items.

The first loop goes thru the categories and grabs all the sub category items and parses them

However the output I have continues even though there are no more items in the table.

$base = '<!-- New Submenu -->' . $newline . '<div id=' . chr(34) . 'dropmenu' ;
		$baseplus = chr(34) . ' class=' . chr(34) . 'dropmenudiv' . chr(34) . ' style =' . chr(34) . 'width: 150px;' . chr(34) . '>' ;
		$line = chr(9) . '<a href=' . chr(34);
		$lineplus = chr(34) . ' target=' . chr(34) . 'detail' . chr(34) . '>' ;
		$linemore = '<' . chr(47) . 'a>' . $newline;
		$last = 0;
		$count = 0;
		$cycled = 0;
		while ($result_row = mysql_fetch_row(($result)))
		{
			//load all submenu items
			$output = '<div id=' . chr(34) . 'dropmenu' .  $result_row[0] . chr(34) . ' class=' . chr(34) . 'dropmenudiv' . chr(34) . ' target=detail width=150px;>' . $newline;
			$query = "SELECT * From tblSubCategories WHERE (((tblSubCategories.CategoryID) = " . $result_row[0] . ")) Order by tblSubCategories.Order;";
			$subresults = mysql_query( $query );
			if ( !$subresults)
			{
				// bad query
				die ("Could not query the database: <br />" . mysql_error());
			}
			while ($sub_row = mysql_fetch_row(($subresults)))
			{
				$count++;
				$output = $output .  '     <a href=' . chr(34) . $sub_row[4] . chr(34) . '>' . $sub_row[3] . '</a>' . $newline; 
			}
			if ($count == 1)
			{
				//skip
				$count = 0;
				echo $count . ' gotcha';
			}else
			{
				echo $output . '</div>' . $newline;
			}
		}

Which produces the following results:

<div class="chromestyle" id="chromemenu" width="100%">
            <ul>

<li><a href="home.html" target="detail">Home</a></li>
<li><a href="" rel="dropmenu2" target="detail">About</a></li>

<li><a href="" rel="dropmenu3" target="detail">Tile & Grout</a></li>
<li><a href="" rel="dropmenu4" target="detail">Auto Detailers</a></li>
<li><a href="" rel="dropmenu5" target="detail">Carpet Cleaners</a></li>
<li><a href="" rel="dropmenu6" target="detail">One Piece Extractors</a></li>
<li><a href="" rel="dropmenu7" target="detail">Insta-Finish Detailing Products</a></li>
</ul>

</div>
<div id="dropmenu1" class="dropmenudiv" target=detail width=150px;>
</div>
<div id="dropmenu2" class="dropmenudiv" target=detail width=150px;>
     <a href="contact_a_better_cleaning_system.html">Contact Us</a>
     <a href="google.html">Google</a>
     <a href="detailing_cleaners_on_autopia.html">Autopia.org</a>
     <a href="financing_with_discount_detail_machines.html">Financing Center</a>
     <a href="payment_methods_on_discount_detail_machines.html">Payment Methods on Discount Detail Machines</a>

</div>
<div id="dropmenu3" class="dropmenudiv" target=detail width=150px;>
     <a href="tile.html">See All Tile Equipment</a>
     <a href="m12.html">Mytee M-12 Series</a>
     <a href="8904.html">Mytee 8904 Spinner</a>
     <a href="th40.html">TurboForce TH-40 Spinner</a>
</div>
<div id="dropmenu4" class="dropmenudiv" target=detail width=150px;>
     <a href="hot_water_detailers.html">See All Detailers</a>

     <a href="heated_detailers_compare.html">Compare Mytee Models</a>
     <a href="hp100.html">Mytee HP100 Gran Prix 2</a>
     <a href="hp60.html">Mytee HP 60</a>
     <a href="8070.html">Mytee Lite 2 - 8070</a>
     <a href="8020.html">Mytee Lite 2 - 8020</a>
</div>
<div id="dropmenu5" class="dropmenudiv" target=detail width=150px;>

     <a href="hot_water_carpet_cleaners.html">See All Carpet Extractors</a>
     <a href="1003dx.html">Mytee 1003DX-450</a>
     <a href="1001dx.html">Mytee 1001DX-450</a>
</div>
<div id="dropmenu6" class="dropmenudiv" target=detail width=150px;>
</div>
<div id="dropmenu7" class="dropmenudiv" target=detail width=150px;>
</div>
<div id="dropmenu8" class="dropmenudiv" target=detail width=150px;>
</div>
<div id="dropmenu9" class="dropmenudiv" target=detail width=150px;>

</div>
<div id="dropmenu10" class="dropmenudiv" target=detail width=150px;>
</div>
<div id="dropmenu11" class="dropmenudiv" target=detail width=150px;>
</div>
<div id="dropmenu12" class="dropmenudiv" target=detail width=150px;>
</div>
<div id="dropmenu13" class="dropmenudiv" target=detail width=150px;>
</div>
<div id="dropmenu14" class="dropmenudiv" target=detail width=150px;>
</div>
<div id="dropmenu15" class="dropmenudiv" target=detail width=150px;>
</div>
<div id="dropmenu16" class="dropmenudiv" target=detail width=150px;>
</div>
<div id="dropmenu17" class="dropmenudiv" target=detail width=150px;>
</div>

<div id="dropmenu18" class="dropmenudiv" target=detail width=150px;>
</div>

Notice the extra drop menus which are empty.

The only items in the main table are in the <li> about 7 of them.

Thanks for your help.

cybersmoker · September 22, 2007

I have found a work around in changing the if ($count >0 ) to ==0.

That did the trick after some debugging due to echo

I hesitate to thing the load that these extra loops would put if this was a more serious application.

Any advice that could be given would be appreciated.

Thanks.

Sign In

group by in 1 query

Recommended Posts

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Join the conversation

Important Information