Shopping Cart - best way of doing it?

[elem]

Hi guys,

I'm just building a shopping cart now, and am having a little trouble .

PHP Code:

foreach($_SESSION['cart'] as $product_id => $quantity) {
$query = $conn->prepare("SELECT id,make,model,price,availability,category,photo FROM products WHERE id=:product_id");
$query->bindValue(':product_id', $product_id);
... 


but what I'm getting instead of the list of the items is an error "SQLSTATE[HY093]: Invalid parameter number: parameter was not defined". But surely I do have it defined - even echoeing $product_id gives me a single digit of 4. What am I doing here wrong folks?

[TFlan]

PHP Code:

$query->bindValue(':product_id', $product_id, PDO::PARAM_INT); 


Try that

Edit:
Also consider what the poster below me stated - Without knowing what the code wrapping the code you posted is, the method he mentions is what you should strive for. The least amount of talk between the database and your website is best

[hinch]

rather than doing the same sql query n times for each itteration could you not just do a select where productid IN($array)

so you only run the query once rather than potentially hundreds of times.

[elem]

TFlan: yeah, I've tried that alright, but no luck ...

hinch: thanks for that tip, good thinking. so I'm trying:

PHP Code:

$query = $conn->prepare("SELECT id,make,model,price,availability,category,photo FROM products WHERE id IN :array");
$query->bindValue(':array', $_SESSION['cart'][$product_id]); 


But it's giving me error SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''Array'' at line 1.

What would be the right syntax here boys?

[TFlan]

PHP Code:

$query = $conn->prepare("SELECT id,make,model,price,availability,category,photo FROM products WHERE id IN (:array)");
$query->bindValue(':array', join(',', $_SESSION['cart'][$product_id])); 


Just forgot the join()

Edit::

Explanation:
What your original query had was this:

PHP Code:

"SELECT id,make,model,price,availability,category,photo FROM products WHERE id IN (Array)" 


If you echo'd your query string, that is what it would look like.

What you need is a string, so you need to implode() ( or as an alias, join() ) to convert the array into a string, with the appropriate delimiter ','

[elem]

I understand where you're coming from. I did exactly what you've posted, and I'm not getting any errors but also no results ... The array has got correct info though:

PHP Code:

Array ( [5] => 1 [3] => 4 


OK, I've managed to get the query log working. It's returning NULL value instead of a string from the array:

PHP Code:

SELECT id,make,model,price,availability,category,photo FROM products WHERE id IN (NULL) 


[elem]

I've noticed that after changing code to this:

PHP Code:

$query->bindValue(':array', join(',', $_SESSION['cart'])); 


I almost got what I wanted, although, it was still giving me the quantities of the products and not product_ids. so I've created a workaround:

PHP Code:

foreach($_SESSION['cart'] as $key => $value) {
$products .= ',' . $key;
}                            
                        
$query = $conn->prepare("SELECT id,make,model,price,availability,category,photo FROM products WHERE id IN (:products)");
$query->bindValue(':products',$products); 


but I probably didn't have to use the loop here, wasn't sure how to get it to work with implode function (anyone?).

The most crucial thing is though, I'm not getting any results. Here's query log:

PHP Code:

SELECT id,make,model,price,availability,category,photo FROM products WHERE id IN ('2,3,4,5') 


and I do have products with id's 2,3,4 and 5. what am I doing wrong here ? is it because of the two apostrophes right before and right after the values ?

[TFlan]

Since there are apostrophes it is being set as a string and not as separate integers, the structure of your database / column type.

Right now the query reads:

PHP Code:

SELECT [blah blah] FROM products WHERE id = '2,3,4,5' 


[elem]

so how can I get rid of the apostrophes while going through bindValue ?

[hinch]

$query->bindValue(:array, join(',', $_SESSION['cart']));

or perhaps $query->bindValue(":array", join(',', $_SESSION['cart']));

also why use join for putting the array together why not just use implode?

[Fou-Lu]

You cannot bind this way. Think of what the statement is doing, it is preparing the statement, and then providing "an" argument. When you say IN (?), that is a single parameter, and if you were to implode an array it would still be a string.
To bind using a prepared statement and an IN clause, you need to assemble the string first and then execute it. Fortunately, PDO does allow an array of bind unlike the MySQLi which does not (so its easier in PDO statements to do this).

PHP Code:

$aIds = array_keys($_SESSION['cart']);
$sPlaceholders = implode(', ', array_fill(0, count($aIds), '?')); // or use a string assembling
$query = $conn->prepare("SELECT id,make,model,price,availability,category,photo FROM products WHERE id IN ($sPlaceholders)");
$query->execute($aIds); 


Assuming that $_SESSION['cart'] uses the keys for the id to check the IN for.

[elem]

Thanks Fou-Lu, that worked a treat.

thanks everyone for help too!