CODEWITHSUNDEEP
phpmysqlpdoprepared-statement
Troy D
Troy D

Reputation: 401

Why do I get data from a PHP PDO direct query versus an error when using a prepared statement?

I am attempting to run a very basic query using PHP's PDO module:

SELECT product_id, product, unit_price
FROM products
WHERE type = *[some_user_selected_product_type]*

(The product type is a tinytext column in the database, and I'm requesting the data via an HTTP POST.)

When I run the basic query, I get exactly what I expect.

$type = 'widgets';
$result = $db->query("SELECT product_id, product, unit_price FROM products WHERE type = '" . $type . "';";);
$result = $result->fetchAll(PDO::FETCH_OBJ);
echo json_encode($result);

Yields the sample data I put in the database...

[{"product_id":"1004","product":"dingus","unit_price":"22.00"}, {"product_id":"1005","product":"thingy","unit_price":"10.00"}]

However, when I try to use a prepared statement to do the identical query, I get an error when I try to fetchAll, stating that the operation can't be performed on a boolean.

$type = 'widgets';
$query = $db->prepare("
            SELECT product_id, product, unit_price FROM products WHERE type = ?
            ");
$result = $query->execute([$type, ]);
// echo var_dump($result);
$result = $result->fetchAll(PDO::FETCH_OBJ);
echo json_encode($result);

Yields...

Fatal error: Call to a member function fetchAll() on boolean in C:\xampp\htdocs\DM\db_utils.php on line 125

What am I missing here? Everything I read suggests that the queries should yield identical results. I even pulled the queries from the DB logs, and they are, in fact, identical.

3 Query

SELECT product_id, product, unit_price
FROM products
WHERE type = 'widgets'

4 Query

SELECT product_id, product, unit_price 
FROM products 
WHERE type = 'widgets'

Upvotes: 1

Views: 148

Answers (4)

Ferrybig
Ferrybig

Reputation: 18834

You need to call fetchAll on the prepared query object, instead of boolean that gets returned by execute.

$query->execute();
$query->fetchAll();

Upvotes: 2

Praveen Kumar
Praveen Kumar

Reputation: 2408

Try this.... 

$type = 'widgets';
$query = $db->prepare("SELECT product_id, product, unit_price FROM products WHERE type =?");
$query->bindParam(1, $type, PDO::PARAM_STR);
$query->execute();
// echo var_dump($result);
$result = $query->fetchAll(PDO::FETCH_OBJ);
echo json_encode($result);

Upvotes: 0

Kunal Awasthi
Kunal Awasthi

Reputation: 320

you are not binding params right remove , from array [$type,]. 

$type = 'widgets';
$query = $db->prepare("
        SELECT product_id, product, unit_price FROM products WHERE type = ?
        ");
$result = $query->execute(array($type));
// echo var_dump($result);
$result_set = $result->fetchAll(PDO::FETCH_ASSOC);
echo json_encode($result_set);

Upvotes: 2

Cristian Programador
Cristian Programador

Reputation: 51

Try with PDO::FETCH_ASSOC

$result = $result->fetchAll(PDO::FETCH_ASSOC);

Upvotes: 1

Related Questions