CODEWITHSUNDEEP
phpmysqlmysqlipdo
Kraang Prime
Kraang Prime

Reputation: 10479

PDO results are empty

I decided to flip from mysqli/mysqlnd to PDO, however I am encountering a problem I had the last time I did this. I am trying this again as it seems that PDO allegedly supports passing a variable that contains an array of values to the execute() param for binding to the query without having to use things like call_user_func_array.

The code I have for demonstration is :

$bind_arguments[] = "dogs";
$bind_arguments[] = "cats";
$bind_arguments[] = "birds";

$db = new PDO('mysql:dbname=' . SQL_DATA . ';host=' . SQL_SERVER, SQL_USERNAME, SQL_PASSWORD, array (
    PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION
)); 

$sql = 'SELECT `name` FROM `pets` WHERE `type` = ? OR `type` = ? OR `type` = ?';

$result = Array();
try {

    if($stmt = $db->prepare($sql)) {

        $stmt->execute($bind_arguments);
        $result = $stmt->fetchAll();


    }
} catch(PDOException $e) {
    echo 'Wrong SQL: ' . $sql . ' Error: ' . $e->getMessage(); exit;
}

$db = null;

var_export($result);  // null

I don't get any exceptions, however $result is null. If I do the regular query using Navicat (or using mysqli) It works!

See Example #5, which shows I should be able to do this (posting example from there here for reference) :

<?php
/* Execute a prepared statement using an array of values for an IN clause */
$params = array(1, 21, 63, 171);
/* Create a string for the parameter placeholders filled to the number of params */
$place_holders = implode(',', array_fill(0, count($params), '?'));

/*
    This prepares the statement with enough unnamed placeholders for every value
    in our $params array. The values of the $params array are then bound to the
    placeholders in the prepared statement when the statement is executed.
    This is not the same thing as using PDOStatement::bindParam() since this
    requires a reference to the variable. PDOStatement::execute() only binds
    by value instead.
*/
$sth = $dbh->prepare("SELECT id, name FROM contacts WHERE id IN ($place_holders)");
$sth->execute($params);
?>

Also see Example #1 posted below for convenience :

<?php
$sth = $dbh->prepare("SELECT name, colour FROM fruit");
$sth->execute();

/* Fetch all of the remaining rows in the result set */
print("Fetch all of the remaining rows in the result set:\n");
$result = $sth->fetchAll();
print_r($result);
?>

So why is this not working ? What am I doing wrong ?

Update

I made some typos when posting my code (which was stripped out of a larger class) for StackOverflow's MVCE requirements. These typos were not present in the original class. I have updated them in the code above. - sorry for any confusion this may have caused.

Upvotes: 1

Views: 527

Answers (1)

rypskar
rypskar

Reputation: 2092

You are assigning values to $bind_array and $bnid_array but are sending in $bind_arguments to execute(). Try changing $bnid_array to $bind_array and use $stmt->execute($bind_array);

Upvotes: 1

Related Questions