combining 2 tables using a 3rd table as the link table.
Hi there, I have a right hand side section that will display a selection of products and feedback (see the attached screenshot) - Currently its working since I am using 2 recordsets (one for the products and one for the feedback) but since I am using this approach it means I cannot mix up the products together with the feedback (with 2 seperate recordsets it would display all products first and then display all feedback directly under).
both the recordsets are using a link table (called featured) see attached screenshot) - The featured table uses FeatureType to determine 1 if its a product and 2 if its feedback) (currently im not using Featured Position). And FeatureTypeID is either the ID for Products table or Feedback table.
im also using a variable to store the server request URL so that I can then pull in different productd and feedback depending on the page.
heres my current recordset:
$basefile = basename ( $_SERVER["REQUEST_URI"] );
mysql_select_db($database_dinkydb, $dinkydb);
$query_rsFeaturedProducts = "SELECT * FROM featured INNER JOIN products ON featured.FeaturedTypeID = products.ProductID WHERE FeaturedLocale = '$basefile'";
$rsFeaturedProducts = mysql_query($query_rsFeaturedProducts, $dinkydb) or die(mysql_error());
$row_rsFeaturedProducts = mysql_fetch_assoc($rsFeaturedProducts);
$totalRows_rsFeaturedProducts = mysql_num_rows($rsFeaturedProducts);
mysql_select_db($database_dinkydb, $dinkydb);
$query_rsFeaturedFeedback = "SELECT * FROM featured INNER JOIN feedback ON featured.FeaturedTypeID = feedback.FeedbackID WHERE FeaturedLocale = '$basefile'";
$rsFeaturedFeedback = mysql_query($query_rsFeaturedFeedback, $dinkydb) or die(mysql_error());
$row_rsFeaturedFeedback = mysql_fetch_assoc($rsFeaturedFeedback);
$totalRows_rsFeaturedFeedback = mysql_num_rows($rsFeaturedFeedback);
and here is my actual php code:
<?php if ($totalRows_rsFeaturedProducts > 0) { // Show if recordset not empty ?>
<div class="clearfix"></div>
<div class="feature-heading">Featured Product</div>
<?php do { ?>
<p class="feature-name"><?php echo $row_rsFeaturedProducts['ProductName']; ?></p>
<div class="clearfix"></div>
<p class="feature-price">From only £<?php echo $row_rsFeaturedProducts['ProductPrice']; ?></p>
<p class="feature-image"><a href="../product.php?Product=<?php echo $row_rsFeaturedProducts['ProductLink']; ?>"><img src="../images/products/large/<?php echo $row_rsFeaturedProducts['ProductImage']; ?>" width="250" alt="<?php echo $row_rsFeaturedProducts['ProductImageTitle']; ?>"/></a></p>
<?php } while ($row_rsFeaturedProducts = mysql_fetch_assoc($rsFeaturedProducts)); ?>
<?php } // Show if recordset not empty ?>
<?php if ($totalRows_rsFeaturedFeedback > 0) { // Show if recordset not empty ?>
<div class="feedback-heading">What our Customers say!</div>
<div class="clearfix"></div>
<p class="feedback-other"><a href="../what-our-customers-say.php">Read other Customer Reviews</a></p>
<?php do { ?>
<div class="clearfix"></div>
<p class="feedback-quote"><?php echo $row_rsFeaturedFeedback['FeedbackText']; ?></p>
<p class="feedback-name"><strong><?php echo $row_rsFeaturedFeedback['FeedbackSalutation']; ?> <?php echo $row_rsFeaturedFeedback['FeedbackFirstName']; ?> <?php echo $row_rsFeaturedFeedback['FeedbackLastName']; ?> | <?php echo $row_rsFeaturedFeedback['FeedbackLocation']; ?></strong></p>
<p class="feedback-date"><?php echo date('jS M Y',strtotime($row_rsFeaturedFeedback['FeedbackDate'])); ?></p>
<?php } while ($row_rsFeaturedFeedback = mysql_fetch_assoc($rsFeaturedFeedback)); ?>
<?php } // Show if recordset not empty ?>
So what I want to do is create only ONE recordset and only ONE section of PHP code so that it either displays products or feedback. Obviously I can write the php code to display either products or feedback.
But Im having trouble created the ONE recordset so that it links products, feedback using the featured table.
I tried different joins etc but no luck,
someone told me I me I should use sub-queries to do what I require, but not really used sub-queries before.
So my question is how do I construct my recordset set to use Products, Feedback and Featured Tables so I can then display both products and feedback in a completely random way on my page (so for example down the right column of website the order could be something like this (Product ; Product ; Feedback ; Product ; Feedback; Feedback ; Product ; Feedback ; Product) (rather then display all products first and then all feedback under which is currently what my page is doing).
Chris
