Jump to content

In need of assistance with SQL/PHP loops


Recommended Posts

I'm designing a website for a wine wholesaler that needs to be able to update and display their product catalog in a certain specific way. The site in development is at www.metrocellars.com/new/

Let me first begin by outlining the database tables in use: 

  • location​ - a table of 6 records, the broader breakdown of geographic location (Brazil, California, etc.) These rows are used in the navigation menu, and each location has many "regions" referencing them.
  • region​ - the more specific area of origin of each product (wine). Each "Region" references one of the 6 broader "Locations".
  • product​ - The meat of the db, each Product record references a Producer/Winery (PID), a specific Region (RID - which also references a broader Location), and a specific Varietal (VID - Wine type/color). The left part of the image below shows part of the Product table.
  • winery​ - This table holds records of each Winery/Producer referenced by PID in Product table.
  • varietals - This table lists each type of wine, (Pinot, Merlot, etc) referenced by VID in the Product table.




On the right side of the above image, I included a visual of the relevant tables with FK relation lines. I haven't figured out which ON DELETE/UPDATE to use, as I got errors a couple times from child rows as I was assigning indexes/keys. I'm a little rusty with the SQL at the moment.


Using this model, I need to be able to output the data to a wine list page, but I am having problems putting together the right queries and loops to list the data in the requested format. Here is an example of what the page content should look like when the 'region' California (LID = 2) is clicked on the nav menu.



Slo Down Wines


Broken Dreams

Sexual Chocolate



Central Coast



Pinot Noir


Happy Canyon of Santa Barbara

Cimarone Winery


3CV Bank


3CV Cilla’s Blend


Le Clos Secret


Gran Premio


3CV Estate Sauvignon Blanc


Dragonette Cellars


GMS Rose


Sauvignon Blanc



Happy Canyon Vineyards


Ten Goal







Margerum Wine Company


Sybarite Sauvignon Blanc


Paso Robles

Spaceman Vineyards


Reserve Airspace


Santa Barbara County

Ampelos Cellars


Syrache - Syrah & Grenache




Margerum Wine Company


Chenin Blanc






Uber Reserve








Grenache Blanc


The Climb



Santa Lucia Highlands





Santa Maria

Tyler Winery


Bien Nacido Pinot Noir


Sta Rita Hills

Ampelos Cellars


Pinot Noir


Lambda Reserve


Rho Reserve





Dragonette Cellars




In the above list, each heading is a record of each "Region" with the same LID as California in the Location table. LID = 2, so I need to list each Region within California, and within each of those listings, list each Wine that has the same RID as that Region, but grouped by their respective Wineries. Basically, there are a few Wineries that have products (child rows I believe) with differing Regions of origin. So, there are products that can have the same PID, but different RID. 

You can get an idea of the format from the above example. My problem is, is how would I nest the loops and queries, and what joins would be necessary? Any and all help is a godsend. This has been quite a headache.

Link to comment
Share on other sites

Dude, you need to eat that elephant one byte at a time... Have you tried anything?  And if so, where are you getting stuck or what happens that isn't supposed to?  Break you process down in to steps and build your output from there.  Then you can test each step in the process.

Link to comment
Share on other sites


I suspect you data model isn't fully normalized


Winery (pid) 2 is always in region 4 so you don't need pid and rid in the product table.

   +-------< region
                +-----------< winery                       varietal
                                 |                            |
                                 +-------< product >----------+


I understand what you mean here, and the thing is, I got this request to develop the website and never got the requirements of the design spelled out clearly, so I just had to address the issues as they arose.  I did it just like that the first time, and made "Winery" reference "Region", which would limit each Winery to one region -- and only after the fact was I informed that the Region was not representative of any particular Winery, but was to be treated as the origins of the grapes used in making the wines. I didn't know anything about the business when I was given the project and basically developed the first prototype of the system based on a single product catalog for marketing, with what turned out to be a lot of irrelevant information like descriptions of the wines that they didn't want on the website after I had already done all that data entry. 


So, the model that I put together for the database structure is what I came up with after learning that a Winery (Or producer however you want to put it) doesn't necessarily provide wine from exclusively that region. The Region list breaks down the product list into groups of regions, as oppose to breaking down each region by winery. Essentially, Region just means where the grapes are grown, and a Winery can provide products from several regions. That's why I broke the tables down and assigned the relations that I did. I was sent an excel spreadsheet with the following columns: 

Winery Name Varietal YEAR Label Location Region YEAR     Ampelos Cellars Pinot Noir 2011 Pinot Noir California Sta. Rita Hills  2011 15 None Ampelos Cellars Red Blend 2009 Syrache - Syrah & Grenache California Santa Barbara County 2009 18.67 None Stangeland Pinot Noir 2008 Pinot Noir Reserve Oregon Eola-Amity Hills 2008 23.33 None Stangeland Pinot Gris 2010 Pinot Gris Oregon Eola-Amity Hills 2010     Stangeland Pinot Noir 2011 Pinot Noir Oregon Willamette Valley 2011 13.33 12.66


There's a few of the rows in the spreadsheet. 

So, basically what I'm trying to do is be able to display the data on the different Location ($lid=2,3, etc) pages in the format that I posted above.


When the user uses the dropdown menu Wine List -> Locations -> California to display all products from those regions in California, with them grouped within by Winery. If Winery was assigned a Region ID, then each child product would be forced to be from that region, which isn't how it works I learned.


I've trouble enough tinkering with the SQL statement I'd use to select the data correctly, let alone which loops (while, for/each) would be best in this situation. The wines can also be displayed by their type, but I've got that part covered. It's this complication with the Products and Regions. I know that's going to involve some nests to a degree, I just have hit a wall on it.


Thanks for any and all help.



Link to comment
Share on other sites

I've also put together a nice table editor for admins to administrate the database contents, as the product list changes frequently. There are plenty of Wineries that only offer wine from one region, but there are also plenty that offer an array of products from several different regions. This table structure made it easy to make updates to the product list using the indexes and constraints.


If there's any good way to output the data as I've shown above, it will prevent a lot of redundancy and that's one of the main things i'm aiming for.

Link to comment
Share on other sites

SELECT * FROM winery
LEFT JOIN `metrocellars`.`product` ON `winery`.`pid` = `product`.`pid` 
LEFT JOIN `metrocellars`.`region` ON `product`.`rid` = `region`.`rid` 
 WHERE `region`.`lid` = 2

 Using this statement I can pull all records from the database in a format similar to the excel spreadsheet I have with the product list. This shows Winery Name for every product row. But what I need to do is pull all products from a particular set of regions (all regions with the matching lid=2) and group the results accordingly. Getting the sql statement is only step one, before I can start writing the php code to access the rows ... my problem is, how would I say in PHP: "For every Region, list distinct Wineries with Products that have matching Region ID". This really didn't sound that hard as I was putting it together.

Link to comment
Share on other sites

Okay, so I've got it. This is what I was looking for:

// Products by Region/Location
// -----------------------------------------------
if(isset($_GET['lid'])) {
	$lid = $_GET['lid'];
	$sql_location = "SELECT * FROM `location`
		WHERE `location`.`lid` = ".$lid."";	
	$location = mysql_query($sql_location) or die(mysql_error());
	// Location Header
	while($l = mysql_fetch_array($location))	{
		print "<h1>".$l['location']."</h1><hr />";
	// List Regions within Location
	$sql_regions = "SELECT * FROM `region`
		LEFT JOIN `location` ON `region`.`lid` =`location`.`lid`
		WHERE `region`.`lid` = ".$lid."";
	$region = mysql_query($sql_regions) or die(mysql_error());
// ----------------------
// -------- LOOP LVL 1: REGIONS
	while($r = mysql_fetch_array($region))	{
		//	Display Region heading
		print "<br /><h2>";
		print $r['region'];
		print "</h2><hr />";
		// SELECT all Wineries having Products in Region (RID)
		$sql_winery = "SELECT DISTINCT * FROM winery
		WHERE `winery`.`pid` IN 
		(SELECT `product`.`pid` FROM `product` WHERE `product`.`rid` = ".$r['rid'].")";	
		$winery = mysql_query($sql_winery) or die(mysql_error());
		// Initialize variable
		$rid = $r['rid'];
		// ----------------------
		while($w = mysql_fetch_array($winery))	{
			// Print Winery Name with hyperlink
			print "<a href='http://".$w['url']."' target='_blank'>";
			print $w['winery_name'];
			print "</a>  - ";
			print $w['url'];
			$sql_product = "SELECT * FROM product
				WHERE `product`.`pid` IN 
				(SELECT `winery`.`pid` FROM `winery` WHERE `winery`.`pid` = ".$w['pid'].")
				AND `product`.`rid` = ".$rid."";
			$products = mysql_query($sql_product) or die(mysql_error());
			print "<p class='products'>";
			// ----------------------
			// -------- LOOP LVL 3 : WINERY
			while($p = mysql_fetch_array($products))	{
				print $p['label'] ." - ".$p['year'];
				print "<br />";
				}	// -------- END LOOP LVL 3
			print "</p>";
			}	// -------- END LOOP LVL 2

		}	// -------- END LOOP LVL 1
}	// ----- END Region Listing

// -----------------------------------------------
// ---------- Varietal page, categorized by color
// -----------------------------------------------	
if(isset($_GET['cid'])) {
	$cid = $_GET['cid'];
	$colorsql = "SELECT * FROM `varietal` WHERE color = ".$cid."";
	$cquery = mysql_query($colorsql) or die(mysql_error());
	if($cid == 1) {
		$color = "Red";
	} elseif($cid == 2) {
		$color = "Rose";
	} elseif($cid == 3)	{
		$color = "White";	
	print "<h1>".$color."</h1><hr />";
	// ----------------------
	// -------- LOOP LVL 1
	while($c = mysql_fetch_array($cquery)) {
		print "<h2>".$c['varietal']."</h2><p class='products'>";	
		$vcpsql = "SELECT * FROM `product` 
			LEFT JOIN `varietal` ON `product`.`vid` = `varietal`.`vid`
			LEFT JOIN `winery` ON `product`.`pid` = `winery`.`pid`
			WHERE `varietal`.`vid` = ".$c['vid']."
			ORDER BY `winery`.`winery_name`";
		$vc = mysql_query($vcpsql) or die(mysql_error());
		// ----------------------
		// -------- LOOP LVL 2
		while($v = mysql_fetch_array($vc)) {
			print "<a href='http://".$v['url']."' target='_blank'>";
			print $v['winery_name'];
			print "</a>  -  ";
			print $v['label'] ." ". $v['year'];
			print "   <span class='flag'>";
			print $v['rating']."   ".$v['flag'];
			print "</span><br />";
		}	// -------- END LOOP LVL 2
		print "</p>";
	}	// -------- END LOOP LVL 1

// End Varietals -----------------------------------------------

This is probably a lot of calls to the database, but I'm just happy to have it finally output the data in the format I need it. As I said before, each Product has an RID, not Winery, because several products can have the same PID but differing RID. 



Ampelos Cellars sells wines from both Sta. Rita Hills, and Santa Barbara County. There are several products from different regions but same winery. This was the problem I was having. I believe I solved it myself, anyway thanks for the replies.

Link to comment
Share on other sites

"For every Region, list distinct Wineries with Products that have matching Region ID". This really didn't sound that hard as I was putting it together.

You'd group things in a multi-dimensional array as you read the result set from the database. Something like:

while ($row=mysql_fetch_assoc($result)){
   $regionId = $row['RID'];
   $wineryId = $row['WID']; 
   $productId = $row['PID'];

   if (!isset($wineList[$regionId])){
      $wineList[$regionId] = $row;

   if (!isset($wineList[$regionId]['wineries'][$wineryId])){
      $wineList[$regionId]['wineries'][$wineryId] = $row;

   $wineList[$regionId]['wineries'][$wineryId]['products'][$productId] = $row;
Then you just loop through the levels of $wineList to output the information.

foreach ($wineList as $regionId=>$regionInfo){
   //output region title
   foreach ($regionInfo['wineries'] as $wineryId=>$wineryInfo){
      //output winery title
      foreach ($wineryInfo['products'] as $productId=>$productInfo){
         //Output product information
That way you only have one query to the DB to request the master un-grouped list. Much more efficient that way than sending a bunch of requests to the DB.
Link to comment
Share on other sites

alternative method

$db = new mysqli(HOST, USERNAME, PASSWORD, 'metrocellars');

$sql = "SELECT  l.location
        , r.region
        , w.winery_name
        , w.url
        , p.label
            location l
            INNER JOIN region r USING (lid)
            INNER JOIN product p USING (rid)
            INNER JOIN winery w USING (pid)
            INNER JOIN varietal v USING (vid)
        WHERE lid = 2
        ORDER BY r.region, w.winery_name, v.varietal
$prevRegion = $prevWinery = '';
$res = $db->query($sql);
while (list($loc,$rgn, $wnry, $url, $label) = $res->fetch_row()) {
    if ($prevRegion != $rgn) {
        echo "<h3>$rgn</h3>\n";
        $prevRegion = $rgn;
        $prevWinery = '';
    if ($prevWinery != $wnry) {
        echo "<a href='$url'>$wnry</a><br>\n";
        $prevWinery = $wnry;
    echo "<span style='margin-left:15px;'>$label</span><br>\n";

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

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