Jump to content

group by in 1 query


samoht

Recommended Posts

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??

Link to comment
https://forums.phpfreaks.com/topic/70073-group-by-in-1-query/
Share on other sites

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.

Link to comment
https://forums.phpfreaks.com/topic/70073-group-by-in-1-query/#findComment-351980
Share on other sites

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?

Link to comment
https://forums.phpfreaks.com/topic/70073-group-by-in-1-query/#findComment-352007
Share on other sites

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...
}

 

Link to comment
https://forums.phpfreaks.com/topic/70073-group-by-in-1-query/#findComment-352016
Share on other sites

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:

 

grouppd.jpg

Link to comment
https://forums.phpfreaks.com/topic/70073-group-by-in-1-query/#findComment-352032
Share on other sites

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

<table width="100%" border="0" cellspacing="0" cellpadding="20">

</p></td><tr>

<td width="33%" align="center">

 

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

 

Any help?

Link to comment
https://forums.phpfreaks.com/topic/70073-group-by-in-1-query/#findComment-352056
Share on other sites

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

Link to comment
https://forums.phpfreaks.com/topic/70073-group-by-in-1-query/#findComment-352485
Share on other sites

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

Link to comment
https://forums.phpfreaks.com/topic/70073-group-by-in-1-query/#findComment-352737
Share on other sites

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.

Link to comment
https://forums.phpfreaks.com/topic/70073-group-by-in-1-query/#findComment-352779
Share on other sites

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.

 

 

Link to comment
https://forums.phpfreaks.com/topic/70073-group-by-in-1-query/#findComment-352784
Share on other sites

Archived

This topic is now archived and is closed to further replies.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.