While Loop within While Loop - should I be using Foreach Loop?

I'm trying to get data from multiple tables in MySQL (The tables include option data for each item available to buy on my ecommerce site). The values are being pulled from a shopping cart session as follows:

foreach($_SESSION['cart'] as $id => $data)
            {

            // Calulate total for each item
            $subtotal = $data['quantity'] * $data['price'];
            $total += $subtotal;

            // This gets the Cart sessions unique ID, so I can get the options from the database using it!
            $sessionidforoptions = $data['uniquesessid'];


            //Out put shopping cart data
            echo    "TABLE ROWS WITH EACH ITEM GO HERE (Product, Qty, Price for item etc...";

I then use the $sessionidforoptions value to get the options from the database:

// Get options IDs from Database
            foreach($_SESSION['sessionoptions'][$sessionidforoptions] as $id2 => $data2) { $$id2 = $data2; }

                if (isset($option1) && ($option1 != "")) { $list = $option1; }
                if (isset($option2) && ($option2 != "")) { $list .= ",".$option2; }
                if (isset($option3) && ($option2 != "")) { $list .= ",".$option3; }

// Query Database
$optionsquerysql = "SELECT * from productOptions WHERE productOptionsID IN ('". $list ."')";
$optionsresultsql= mysql_query($optionsquerysql) or die(mysql_error());

Then output the options:

while   ($optionssql = mysql_fetch_array($optionsresultsql)) {

                            $optionNamesID = $optionssql["optionNamesID"];
                            $optionValue = $optionssql["optionValue"];

                            // Get option names from databsae
                            $optionsnamesquerysql = "SELECT * from optionNames WHERE optionNamesID = ".$optionNamesID."";
                            $optionsnamesresultsql= mysql_query($optionsnamesquerysql) or die(mysql_error());

                                //Output options names + options
                                while   ($optionnamessql = mysql_fetch_array($optionsnamesresultsql)) {

                                        $optionName = $optionnamessql['optionName'];    
                                        echo $optionName.': '.$optionValue.'<br />';                            
                                }

                    }

This almost works! The session has 3 options stored for each item in the shopping cart (size, colour, etc)

I get the following:

Item 1 - £20.00 Size: Small

Item 2 - £22.00 Size: Medium

Item 3 - £45.00 Size: Large

This is what I should get:

Item 1 - £20.00 - Size: Small - Colour: Black - Belt: Small

Item 2 - £22.00 - Size: Medium - Colour: Blue - Belt: Medium

Item 3 - £45.00 Size: Large - Colour: Pink - Belt: Large

As you can see, the last while loop is only outputting the first option each time

while   ($optionnamessql = mysql_fetch_array($optionsnamesresultsql)) { OPTION OUTPUT }

Should I be using fore开发者_Go百科ach here instead of a while loop?

Really appreciate any advice anyone can offer. I realise my code isn't very clean. I'm still learning... I'm sorry about this.

Thanks for any advice you can offer

Try printing the values of $optionsquerysql and $optionsnamesquerysql as you are looping over your data. Then make sure the SQL statements look like you would expect them to. Finally, copy the SQL and run it against the database directly using PHPMyAdmin, SQLYog, or just the mysql command line utility. Make sure the results that you get back are what you expect.

Technically speaking, the control structures you are using should work as you expect them to.

UPDATE

I don't think your query SELECT * from productOptions WHERE productOptionsID IN ('". $list ."') will work like you expect it to. Whatever the value of $list is, you are putting it inside a set of single quotes in your SQL string, so MySQL will be treating it as a single string value which it then tries to convert to an integer. I suspect that is your problem. Try removing the single quotes so the query is just SELECT * from productOptions WHERE productOptionsID IN (". $list .").

As a side note, you should read up on SQL injection attacks and make sure you are sanitizing your data before using it in SQL queries.

Personally, I only use while when size of loop cannot be pre-calculated.

For-next is a great thing.

//edit//

a basic for-next-loop:

for($i=0;$i<10;$i++) {
  echo $i . '<br />';
}

now, not always do you know how many times you can go (no end value), like when reading chunks of bigger files. However, recordsets fall somewhere in between.

Most often, recordsets aren't to big and can easily be preloaded into an array, as

$rs = array();
while ($rs[] = mysql_fetch_assoc($result));

Now $rs contains a flexible array with all your records, and you may perform calculations, advanced field-modifications or whatever before output, all with for-next...

for($i=0;$i<sizeof($rs);$i++) {
  //creating a new 'field'
  $rs[$i]['Fullname'] = $rs[$i]['Firstname'] . ' ' . $rs[$i]['Lastname'];
}

At output, just loop again, with for-next

(The next-word isn't used in php since it's a form of derivate of C, just a '}'. Basic and Pascal uses it however)

redgards, //t