In need of assistance with SQL/PHP loops

[xxRickterxx]

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.

 

California

Slo Down Wines

http://www.slodownwines.com/

Broken Dreams

Sexual Chocolate

 

 

Central Coast

Jackhammer

http://jackhammerwine.com

Pinot Noir

 

Happy Canyon of Santa Barbara

Cimarone Winery

http://www.cimarone.com/

3CV Bank

 

3CV Cilla’s Blend

 

Le Clos Secret

 

Gran Premio

 

3CV Estate Sauvignon Blanc

 

Dragonette Cellars

http://dragonettecellars.com/

GMS Rose

 

Sauvignon Blanc

 

 

Happy Canyon Vineyards

http://happycanyonvineyard.com/

Ten Goal

 

Merlot

http://happycanyonvineyard.com/

Piocho

 

 

Margerum Wine Company

 

Sybarite Sauvignon Blanc

 

Paso Robles

Spaceman Vineyards

 

Reserve Airspace

 

Santa Barbara County

Ampelos Cellars

 

Syrache - Syrah & Grenache

 

 

 

Margerum Wine Company

http://www.margerumwinecompany.com/

Chenin Blanc

 

M5

 

Riesling

 

Uber Reserve

 

 

 

Tercero

http://tercerowines.com/

Grenache

 

Grenache Blanc

 

The Climb

 

 

Santa Lucia Highlands

Jackhammer

http://jackhammerwine.com

Chardonnay

 

Santa Maria

Tyler Winery

 

Bien Nacido Pinot Noir

 

Sta Rita Hills

Ampelos Cellars

http://ampeloscellars.com/

Pinot Noir

 

Lambda Reserve

 

Rho Reserve

 

Viognier

 

 

Dragonette Cellars

http://dragonettecellars.com/

 

 

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.

[ginerjm]

Sounds like you started in way over your head.

[Barand]

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.

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

[rwhite35]

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.

[xxRickterxx]

 

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.

location
   |
   +-------< 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.

 

-Rick

[xxRickterxx]

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.

[xxRickterxx]

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.

[xxRickterxx]

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'];
		
		// ----------------------
		// -------- LOOP LVL 2: WINERIES W/ PRODUCTS FROM REGION
		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. 

http://www.metrocellars.com/new/list.php?lid=2

 

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.

[kicken]

"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:

$wineList=array();
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.

[Barand]

alternative method

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

$sql = "SELECT  l.location
        , r.region
        , w.winery_name
        , w.url
        , p.label
        FROM
            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";
}