Tell me more ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote 1 down vote favorite

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 foreach 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

share|improve this question

2 Answers

up vote 1 down vote accepted

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.

share|improve this answer
Great advice. Will try the PHPMyAdmin thing now! Thanks – drandom Feb 28 '11 at 22:20
Think there's a problem with MySQL: When I enter this: SELECT * FROM productOptions WHERE productOptionsID IN ('1,2,3,4') I only get one results back. that's this line in the script: $optionsquerysql = "SELECT * from productOptions WHERE productOptionsID IN ('". $list ."')"; have I got the query wrong? – drandom Feb 28 '11 at 22:22
Thanks Chris. Really appreciate your help. Will sanitize all data before site goes live. Thanks again – drandom Feb 28 '11 at 22:46
up vote 1 down vote

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

share|improve this answer
1  
Can to expand on this? I've never heard of a for-next loop! – drandom Feb 28 '11 at 22:17
done, post edited. – user247245 Feb 28 '11 at 23:45

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.