开发者

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.

0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜