Use WHERE IN with PDO PHP

Question

I am trying to build a shopping cart using PHP, I get a list of IDs from the products added to the basket, I then want to query my database with these IDS using a WHERE IN SQL statement to get the details of the items added to the basket.

At the moment my query just comes back with false.

if(isset($_SESSION["cart"])) {
                    foreach ($_SESSION["cart"] as $id => $value) {
                       $ids .= $id . ',';

                       $count += $value['quantity'];

                    } 

                       $query = $database->find_item_db($ids);



EDIT I have now changed my function to use the PDO syntax.

function find_item_db($product_code) {

    $query = substr($product_code, 0,-1);
    $product_codes = explode(",", $query);
    $product_code_new = "(".implode("', '", $product_codes).")";
    //we need to get product name and price from database.
    $sql = "SELECT * FROM `Sweets` WHERE `Sweet_ID` IN :id";
    $statement = $this->connection->prepare($sql);
    $statement->bindParam(':id', $product_code_new);
    $statement->execute();
    return $done = $statement->fetchAll();

    }

However this is still returning nothing, I get this error in my logs.

/var/www/html/sweetshop/partials/categories-nav.php(32): Database_Functions->find_item_db('1,10,6,23,')\n#2 /var/www/html/sweetshop/category.php(17): include('/var/www/html/s...')\n#3 {main}\n thrown in /var/www/html/sweetshop/classes/class-database-functions.php on line 139, referer: http://localhost/sweetshop/category.php?type=Chocolate

I know my connection works fine as all my other queries work perfectly.

you marked as PDO but using mysqli_ syntax => `bind_param` so there. checking for errors would have thrown you something about it. Those different APIs do **not** intermix. Not for connections, nor querying. — Funk Forty Niner, Feb 17 '16 at 15:37
Aside from what @Fred-ii- is saying, you are binding something that you're not even using. *Right Ralph?* — Jay Blanchard, Feb 17 '16 at 15:41
and we don't know which MySQL API you're using to connect with also or if the session was even started. Edit: *Right Sam* @JayBlanchard as in `$new` undefined variable *blah blah blah* and `:id`. — Funk Forty Niner, Feb 17 '16 at 15:42
Here, have a read => http://stackoverflow.com/questions/17498216/can-i-mix-mysql-apis-in-php as to what you can't do. — Funk Forty Niner, Feb 17 '16 at 15:44
This question contains too many syntax errors and quite a few unknowns. You will need to go through the related manuals on which MySQL API you are using to connect with and the one to query/bind with, taking into consideration that you CANNOT intermix those different APIs/functions. All we could do here is to point out those mistakes and you will need to further debug your code. I sincerely wish you well with this. — Funk Forty Niner, Feb 17 '16 at 15:50
Thank you for the the help, I have changed my question to reflect your help — pocockn, Feb 17 '16 at 16:12
From the PDO Manual [Note: Parameter markers can represent a complete data literal only. Neither part of literal, nor keyword, nor identifier, nor whatever arbitrary query part can be bound using parameters. For example, you cannot bind multiple values to a single parameter in the IN() clause of an SQL statement.](http://php.net/manual/en/pdo.prepare.php) This not withstanding the fact that you have incorrect syntax around the `IN` clause i.e. `IN (1,2,3)` Note the round brackets — RiggsFolly, Feb 17 '16 at 16:24

score 1 · Answer 1 · edited Jun 20 '20 at 09:12

1

1. Incorrect syntax

If $ids is something like:

$ids = "1,2,3,4,5";

Then the query is:

SELECT * FROM `Sweets` WHERE `Sweet_ID` IN (1,2,3,4,5)

Which is incorrect because each value needs to be wrapped in single quotes:

function find_item_db($product_code){
    $query = substr($product_code, 0,-1);

    //Wrap each product id
    $product_codes = explode("," $product_code);
    $product_codes = "'".implode("', '", $product_codes)."'";

    //.......
}

That way the query will read:

SELECT * FROM `Sweets` WHERE `Sweet_ID` IN ('1', '2', '3', '4', '5')`

2. Mixing SQL APIs

mysqli_* syntax and PDO syntax are not interchangeable. bind_param() is for PDO, however your query is using mysqli.

When you are using $statement->bind_param(':id', $new);, what are you binding? There is no :id value in the query, and therefore the line is unnecessary as well as incorrect SQL query format!

edited Jun 20 '20 at 09:12

Community

1
1

answered Feb 17 '16 at 15:38

Ben

8,894
7
44
80

1

you missed something VERY important here. See their code again. – Funk Forty Niner Feb 17 '16 at 15:39
1

*"bind_param is for PDO"* - Actually, that is for `for mysqli_` ;-) *"however your query is using mysqli"* - OP is also using a PDO function `fetchAll()`. So this question is extremely hard to solve, unknowing what their connection API is. Way too many errors and unknowns here. – Funk Forty Niner Feb 17 '16 at 15:46
1

You may have meant to say *"`bindParam()` is for PDO"* ;-) – Funk Forty Niner Feb 17 '16 at 15:47
@Fred-ii- I guess I didn't know where to start... Thanks for the heads up. I'm leaving the answer here because at least *some* of it can help OP, but (s)he needs more than this answer to solve their code! – Ben Feb 17 '16 at 15:52
The OP has enough to go on now. – Funk Forty Niner Feb 17 '16 at 15:53

Use WHERE IN with PDO PHP

1 Answers1

1. Incorrect syntax

2. Mixing SQL APIs