Reduce number of MySQL Queries and Output Data in Correct Order
My main issue is the number of times I query the database (see below). Also, I would like to check that the current product (optionsToProducts.productID) has options for the current optionName before outputting the select statement! See the final image below to see the blank select box...
I have 8 tables in total, but the 3 that matter are:
optionNames
http://www.grabb.co.uk/stack/001.png
productOptions
http://www.grabb.co.uk/stack/002.png
optionsToProducts
http://www.grabb.co.uk/stack/003.png
<?php
$i=0;
$optionsquery = "SELECT * FROM optionNames WHERE categoryID = ".$categoryID."";
$optionsresult= mysql_query($optionsquery) or die(mysql_error());
while ($optionnames = mysql_fetch_array($optionsresult)) {
$i++;
$optionname = $optionnames["optionName"];
$optionID = $optionnames["optionNamesID"];
//echo $optionname."<br />";
?>
<label for="option<?php echo $i; ?>"><?php echo $optionname; ?></label>
<select name="option<?php echo $i; ?>" id="<?php echo $i; ?>">
<?php
//$optionvalues = "SELECT * FROM (optionsToProducts,productOptions) WHERE optionsToProducts.productID = ".$productID." AND productOptions.optionNamesID = ".$optionID."";
//echo $optionvalues."<br />开发者_JAVA百科;<br />";
$optionvalues = "SELECT * FROM optionsToProducts WHERE productID = ".$productID."";
$valuesresult= mysql_query($optionvalues) or die(mysql_error());
while ($optionvals = mysql_fetch_array($valuesresult)) {
$valueName = $optionvals["optionValue"];
$valueID = $optionvals["productOptionsID"];
//echo $valueName."<br />";
$optionfinal = "SELECT * FROM productOptions WHERE productOptionsID = ".$valueID." AND optionNamesID = ".$optionID."";
$finalresult= mysql_query($optionfinal) or die(mysql_error());
while ($optionlast = mysql_fetch_array($finalresult)) {
$optionValueName = $optionlast["optionValue"];
$optionValueID = $optionlast["productOptionsID"];
$num_rows = mysql_num_rows($finalresult);
?>
<option value="<?php echo $optionValueID; ?>"><?php echo $optionValueName; ?></option>
<?php
}
}
echo "</select>";
}
?>
final Output:
http://www.grabb.co.uk/stack/004.png
As always, your help is appreciated. Thank you.
Since you tagged this question with the join
tag, you probably know you need to write a join query to get what you need.
<?php
$i=0;
$query = "SELECT options.optionName, options.optionNamesID, po.optionValue, po.productOptionsID
FROM optionNames AS options
INNER JOIN productOptions AS po ON po.optionNamesID=options.optionNamesID
INNER JOIN optionsToProducts AS otp ON otp.productOptionsID=po.productOptionsID
WHERE otp.productID=" . (int) $productID
. " AND options.categoryID=" . (int) $categoryID;
$result = mysql_query($query);
if($result) {
$rows = array();
while($row = mysql_fetch_assoc($result) ) {
$rows[] = $row;
}
$i = 0;
$optionId = null;
foreach($rows as $row) {
if($optionId != $row['optionNamesID']) {
$optionId = $row['optionNamesID'];
?>
<label for="option<?php echo $optionId; ?>"><?php echo $row['optionName']; ?></label>
<select name="option<?php echo $optionId; ?>" id="<?php echo $optionId; ?>">
<?php } ?>
<option value="<?php echo $row['productOptionsID']; ?>"><?php echo $row['optionValue']; ?></option>
<?php
//Close select element when the optionNamesID changes or on the last row
if( (isset($rows[$i + 1]) && $rows[$i + 1]['optionNamesID'] != $optionId) ||
!isset($rows[$i + 1]) ) { ?>
</select>
<?php }
$i++;
}
} else {
//Debug query, remove in production
echo mysql_error();
}
?>
I also made some small changes - I use the optionNamesID in the select and label tag names - I don't know how you knew previously which select belonged to which option. I also assumed that categoryID and productID came from somewhere, since it's not specified in the code.
Pushing all the rows to an array at the beginning is optional, but it makes the code a bit more organized (since you can check ahead in the array to see where to close the select tags).
NOTICE - this code is untested so there could some minor typos. Please make the needed corrections if necessary.
精彩评论