Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 12 of 12
  1. #1
    New Coder
    Join Date
    Sep 2011
    Posts
    35
    Thanks
    18
    Thanked 1 Time in 1 Post

    Question Shopping Cart - best way of doing it?

    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?

  • #2
    New Coder
    Join Date
    Dec 2012
    Location
    USA
    Posts
    82
    Thanks
    3
    Thanked 17 Times in 17 Posts
    PHP Code:
    $query->bindValue(':product_id'$product_idPDO::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
    Last edited by TFlan; 01-11-2013 at 03:59 PM.

  • #3
    Regular Coder hinch's Avatar
    Join Date
    Sep 2005
    Location
    UK
    Posts
    923
    Thanks
    25
    Thanked 80 Times in 80 Posts
    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.
    A programmer is just a tool which converts caffeine into code

    My work: http://www.fcsoftware.co.uk && http://www.firstcontactcrm.com
    My hobby: http://www.angel-computers.co.uk
    My life: http://www.furious-angels.com

  • Users who have thanked hinch for this post:

    elem (01-12-2013)

  • #4
    New Coder
    Join Date
    Sep 2011
    Posts
    35
    Thanks
    18
    Thanked 1 Time in 1 Post
    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?

  • #5
    New Coder
    Join Date
    Dec 2012
    Location
    USA
    Posts
    82
    Thanks
    3
    Thanked 17 Times in 17 Posts
    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 ','
    Last edited by TFlan; 01-11-2013 at 06:36 PM.

  • Users who have thanked TFlan for this post:

    elem (01-12-2013)

  • #6
    New Coder
    Join Date
    Sep 2011
    Posts
    35
    Thanks
    18
    Thanked 1 Time in 1 Post
    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] => [3] => 
    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
    Last edited by elem; 01-11-2013 at 08:32 PM.

  • #7
    New Coder
    Join Date
    Sep 2011
    Posts
    35
    Thanks
    18
    Thanked 1 Time in 1 Post
    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 ?

  • #8
    New Coder
    Join Date
    Dec 2012
    Location
    USA
    Posts
    82
    Thanks
    3
    Thanked 17 Times in 17 Posts
    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 blahFROM products WHERE id '2,3,4,5' 

  • #9
    New Coder
    Join Date
    Sep 2011
    Posts
    35
    Thanks
    18
    Thanked 1 Time in 1 Post
    so how can I get rid of the apostrophes while going through bindValue ?

  • #10
    Regular Coder hinch's Avatar
    Join Date
    Sep 2005
    Location
    UK
    Posts
    923
    Thanks
    25
    Thanked 80 Times in 80 Posts
    $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?
    A programmer is just a tool which converts caffeine into code

    My work: http://www.fcsoftware.co.uk && http://www.firstcontactcrm.com
    My hobby: http://www.angel-computers.co.uk
    My life: http://www.furious-angels.com

  • Users who have thanked hinch for this post:

    elem (01-14-2013)

  • #11
    God Emperor Fou-Lu's Avatar
    Join Date
    Sep 2002
    Location
    Saskatoon, Saskatchewan
    Posts
    16,979
    Thanks
    4
    Thanked 2,659 Times in 2,628 Posts
    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(0count($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.

  • Users who have thanked Fou-Lu for this post:

    elem (01-14-2013)

  • #12
    New Coder
    Join Date
    Sep 2011
    Posts
    35
    Thanks
    18
    Thanked 1 Time in 1 Post
    Thanks Fou-Lu, that worked a treat.

    thanks everyone for help too!


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •