PDO bindParam not working in IN binding for sql query

Question

I'm trying to get some videos according to some attributes (age, year, countries). For some reason, even though I'm binding parameters to the query properly and have specified the query properly (u.country NOT IN ($countries_count)), I still get results for the country = U.S.A. Is there something work with my bindParam. Please Help.

<?php
    $parameters = json_decode(file_get_contents('php://input'), true);
    $age = $parameters["age"];
    $year = $parameters["year"];
    $countries = sizeof($parameters["countries"]) == 0 ? array("0") : $parameters["countries"];
    $countries_count = implode(",", array_fill(0, sizeof($countries), "?"));

    $sql = "SELECT
                v.title, u.name 
            FROM 
                video AS v JOIN user AS u ON v.id_user = u.id 
            WHERE 
                u.age <= ? AND YEAR(v.upload_date) >= ? AND 
                u.country NOT IN ($countries_count);";

    $connection = new PDO("mysql:host=localhost;dbname=data_base", "root", "");
    $statement = $connection->prepare($sql);
    $statement->bindParam(1, $age, PDO::PARAM_INT);
    $statement->bindParam(2, $year, PDO::PARAM_INT);
    foreach ($countries as $k => $x) {
        $statement->bindParam($k+3, $x, PDO::PARAM_STR);
    }
    $statement->execute();
    echo json_encode($statement->fetchAll());
?>

Nick · Accepted Answer · 2019-05-28 03:06:56Z

1

Your problem is that you are binding all the IN parameters to the same variable ($x) so they all end up with the same value. You can work around that by either changing to bindValue or by binding to the actual array value I.e.

$statement->bindParam($k+3, $countries[$k], PDO::PARAM_STR);

answered May 28, 2019 at 3:06

Nick

147k23 gold badges67 silver badges106 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Tim Morton Over a year ago

$x is being assigned in the foreach loop... so it should be the same as $countries[$k]

Nick Over a year ago

@TimMorton yes, it's value is the same (which is why swapping to bindValue will work) but bindParam forms a link to the variable, so at the end of the loop, all the bound parameters will point to the same thing, and all get the same value.

aman Over a year ago

Wow, it worked. Though I don't understand why my code in the question doesn't work. $x should have the exact same value as $countries[$k].

Nick Over a year ago

@Aman see my previous comment to Tim Morton. bindParam forms a link to the variable, so all your parameters are bound to $x, and when you execute the query it has the last value in the countries array, meaning you are effectively ignoring the other vaues in the array.

Tim Morton Over a year ago

@Nick Thanks for explaining. Reference instead of value... I completely missed any clues pointing to that.

user9032546 · Accepted Answer · 2019-05-28 02:38:37Z

1

Change bindParam to bindValue

If you want to use bindParam, change your sql to

u.age <= :age and
YEAR(v.upload_date) >= :year
...

Then bind param:

->bindParam(':age', $age)
->bindParam(':year', $year)

answered May 28, 2019 at 2:38

user9032546

2 Comments

aman Over a year ago

What About for the IN statement?

user9032546 Over a year ago

Cannot use bindParam with IN clause. Can use bindValue instead or inject in sql u.country NOT IN ('" . implode("','", $countries) . "')

Collectives™ on Stack Overflow

PDO bindParam not working in IN binding for sql query

2 Answers 2

5 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related