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.

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.

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.

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

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.