Session is returning just 1 row while there are 3

[donboe]

I need to return all items from a session. To make that happen I query the database with the following method:

    public function get_cart_items($ids)
    {        
        $sql = "SELECT id
                     , name
                     , price
                  FROM products
                 WHERE id IN (?)
              ORDER BY name";
              
        $stmt = $this->pdo->prepare($sql);
        $stmt->execute(array($ids));
        
        return $stmt->fetchAll(PDO::FETCH_ASSOC);
    }

In the Controller I use:

        if(count($_SESSION['cart_items'])>0){
            $ids = "";
            foreach($_SESSION['cart_items'] as $id => $value){
                $ids = $ids . $id . ",";
            }
            $ids = rtrim($ids, ',');
        }
        
        $heading    = 'Producten in je cart';
        $cart_items = $this->shop->get_cart_items($ids);
                
        $pagina_vars = array (
            'body_id'   => 'shop',
            'meta_tags' => $this->page->get_pagina_content($this->pageId)    
        );
        
        $this->render('cart', compact('heading','cart_items') + $pagina_vars);

This is just returning one item from the cart, while it should return 3 (I have added a counter to the page to test)

        if(isset($_SESSION["cart_items"]))
        {
            $cart_count = count($_SESSION['cart_items']);
        }
        else
        {
            $cart_count=0;
        }

and that gives me indeed 3. So there must be something wrong in the Model and/or Controller. Only I don’t see what?

Thank you in advance

[StarLion]

You’ve run into the classic problem with prepared statements - dynamic query sizing.

Your query states:

$sql = "SELECT id
             , name
             , price
          FROM products
         WHERE id IN (?)
      ORDER BY name";

So you’ve specified a single input.

You’ve tried to compensate for this by building a string in your controller, but unfortunately that’s not how the SQL works.

Lets say I have 3 items - 1,2 and banana. (I dont know if your ID’s are all numbers, but for this problem it doesnt actually matter).

Your controller builds the string “1,2,banana”, and sends it to the function.

The query receives it, sticks it into the query, and you get:

$sql = "SELECT id
             , name
             , price
          FROM products
         WHERE id IN ("1,2,banana")
      ORDER BY name";

but… that isnt what the query is expecting. Note that the IN function is receiving only ONE value to search for (the quotes set it off as a single string). Because you’ve sent 1 string, you get 1 answer.

Instead, you’ll need to dynamically shape your query, and instead of passing a single string, pass an array of ID’s into the prepared statement. Lets get crafty with some array functions.

$sql = "SELECT id
             , name
             , price
          FROM products
         WHERE id IN (".implode(",",array_fill(0,count($ids),"?")).")
      ORDER BY name";

This will build a query string (which you can see if you echo $sql) that looks something like

    $sql = "SELECT id
                 , name
                 , price
              FROM products
             WHERE id IN (?,?,?)
          ORDER BY name";

Which then can be filled by your $ids array, and turns the previous input (assuming you turned your 1 and 2 into strings… if the ID’s are all ints, leave them ints and the query wont have quotes) into:

SELECT id
                 , name
                 , price
              FROM products
             WHERE id IN ("1","2","banana")
          ORDER BY name";

Which should produce the desired result set.

[donboe]

Hi StarLion. Thank you very much for the reply! I just adjusted the model in the way you described.

        $sql = "SELECT id
                     , name
                     , price
                  FROM products
                 WHERE id IN (".implode(",",array_fill(0,count($ids),"?")).")
              ORDER BY name";
              
        $stmt = $this->pdo->prepare($sql);
        $stmt->execute(array($ids));
        
        return $stmt->fetchAll(PDO::FETCH_ASSOC);

and left the controller how it was:

       
        if(count($_SESSION['cart_items'])>0){
            $ids = "";
            foreach($_SESSION['cart_items'] as $id => $value){
                $ids = $ids . $id . ",";
            }
            $ids = rtrim($ids, ',');
        }
        
        $heading    = 'Producten in je cart';
        $cart_items = $this->shop->get_cart_items($ids);
                
        $pagina_vars = array (
            'body_id'   => 'shop',
            'meta_tags' => $this->page->get_pagina_content($this->pageId)    
        );
        
        $this->render('cart', compact('heading','cart_items','cart_count') + $pagina_vars);

But still get just 1 id in return. Or do I misunderstand you somewhere? I cleared the cache and started from the beginning.

If i did something wrong excuse my ignorance

[StarLion]

The controller needs to send an array of ID’s.

    $cart_items = array();
    if(count($_SESSION['cart_items'])>0)
       $cart_items = $this->shop->get_cart_items(array_keys($_SESSION['cart_items']));

[donboe]

Hi StarLion. Thanks again for the reply.

Oops I now get two errors:

Array to string conversion in C:\wamp\www\Rosarosas\private\includes\classes\Model\Shop.php on line 60

and

nvalid parameter number: number of bound variables does not match number of tokens in C:\wamp\www\Rosarosas\private\includes\classes\Model\Shop.php on line 60

Line 60 is WHERE id IN (“.implode(”,“,array_fill(0,count($ids),”?“)).”)

[StarLion]

does $_SESSION[‘cart_items’] exist as an array?

[donboe]

How do i test that?

[StarLion]

just inside your function, var_dump($ids); and see what it says it contains.

[donboe]

This is what i get back

array (size=2)
0 => int 5
1 => int 4

Edit I just added another item to the cart and when I go to the cart it returns 3 so it seems it exists as an array

[StarLion]

That… makes no sense then.

Try doing it this way and see which step it’s balking on…

$quests = array_fill(0,count($ids),"?");
$quests = implode(",",$quests);
$sql = "SELECT id , name, price FROM products WHERE id IN ($quests) ORDER BY name";

[donboe]

Hi StarLion thanks again for the reply.

@StarLion

Try doing it this way and see which step it’s balking on…

$ds = array_fill(0,count($ids),“?”);
$ids = implode(“,”,$quests);
$sql = “SELECT id , name, price FROM products WHERE id IN ($ids) ORDER BY name”;

I just tried that as well but keep getting the same two errors.

Array to string conversion in C:\wamp\www\Rosarosas\private\includes\classes\Model\Shop.php on line 64

and

Invalid parameter number: number of bound variables does not match number of tokens in C:\wamp\www\Rosarosas\private\includes\classes\Model\Shop.php on line 64

Edit: I changed

$quests = array_fill(0,count($ids),“?”);
$quests = implode(“,”,$ids);

to just

$quests = implode(', ', $ids);

public function get_cart_items(array $ids)
    {
        $quests = implode(', ', $ids);        
        $sql = "SELECT id
                     , name
                     , price
                  FROM products
                 WHERE id IN ($quests)
              ORDER BY name"
              
        $stmt = $this->pdo->prepare($sql);
        $stmt->execute(array($quests));
        
        return $stmt->fetchAll(PDO::FETCH_ASSOC);
    }

and now it is working Thank you so much you for all your input StarLion. It is very much appreciated

[StarLion]

that… sort of defeats the purpose of prepared statements, but… it will work.

[donboe]

It indeed does, and I’m happy about it for now. Later on I will for sure gor deeper into this mather. Again thanks a lot