CODEWITHSUNDEEP
phppdomysqli
Aerdan
Aerdan

Reputation: 51

PDO Query Variable Issue?

In the following queries, I'm using entry_id values to from exp_relationships to find out the smallest value (a year) in exp_channel_data. I'll give you some dummy data.

entry_id 1 has a year of 2014 entry_id 2 has a year of 2012

The first query generates "1, 2" which I save for the next one to provide the WHERE parameters. If I were to type in "1, 2" instead of :showid, it works correctly and displays the 2012 since that is the first one in the ORDER BY I have set. But when the query runs as it is written below, it displays the 2014.

$sql = 'SELECT DISTINCT parent_id FROM exp_relationships WHERE child_id = :entry_id AND grid_field_id IN (90, 91)';
$stmt = $conn->prepare($sql);
$showid = "";

try {
    $stmt->execute(array('entry_id' => {entry_id}));
    $result = $stmt->fetchAll(PDO::FETCH_ASSOC);
    foreach ($result as $row) {
        $showid .= $row['parent_id'].", ";
    }
    $showid = substr($showid, 0, -2);
} catch(PDOException $e) { error_log($e->getMessage(),0); }

$sql = 'SELECT field_id_16 FROM exp_channel_data WHERE entry_id IN (:showid) ORDER BY field_id_16 ASC LIMIT 1';
$stmt = $conn->prepare($sql);
$year = "";

try {
    $stmt->execute(array('showid' => $showid));
    $result = $stmt->fetchAll(PDO::FETCH_ASSOC);
    foreach ($result as $row) {
        $year = $row['field_id_16'];
        echo $year;
    }
} catch(PDOException $e) { error_log($e->getMessage(),0); }

Upvotes: 0

Views: 29

Answers (1)

Ivan Yonkov
Ivan Yonkov

Reputation: 7034

This is because defaultly it will cast $showid to its primitive type, string or int. In you case I guess if will be the string 1, 2. This way you cannot just inject variable value in IN to simulate more than one value. Each placeholder is one value, and IN (:placeholder) will refer to only one thing. You need either to split as much placeholders as needed, i.e. in this particular case 2 placeholders IN (?, ?) and then implode your $showid string into array. Or implode $showid into array and then split it into the IN clause, which I consider the worse way of both.

Upvotes: 1

Related Questions