CODEWITHSUNDEEP
phppdomysqli
dev-null
dev-null

Reputation: 551

php - mysqli works but pdo doesn't

I'm trying to switch to pdo and have enjoyed success with it but my latest function will not work. When I revert to mysqli, however, it is fine. I'm sure I'm missing something, but what??

not working PDO:

$db = db_pdo();
$query = $db->prepare("select * from locks_for_sale where type_3=':search'");
$query->bindParam(":search", $sub_items[3]);
$query->execute();

if (!$result=$query->fetch()) {
    $print .= "<tr><td>&nbsp;</td><td><h3>No products currently available.</h3></td></tr>\n";
}

else {
    other code

Please note:
function db_pdo is included.
$sub_items[3] is a string.

working mysqli:

$db = db_conn();
$sql = "select * from locks_for_sale where type_3='".$sub_items[3]."'";
$query = $db->query($sql);

if (!$query->fetch_assoc()) {
    $print .= "<tr><td>&nbsp;</td><td><h3>No products currently available.</h3></td></tr>\n";
}

else {
    other code

Again db_conn is included.

I know that the result of this query is returning 2 items but the pdo version shows !$result.

Thanks in advance.

Upvotes: 1

Views: 546

Answers (3)

frugi
frugi

Reputation: 605

try bindValue (http://www.php.net/manual/en/pdostatement.bindvalue.php)

<?php
/* Execute a prepared statement by binding PHP variables */
$calories = 150;
$colour = 'red';
$sth = $dbh->prepare('SELECT name, colour, calories
    FROM fruit
    WHERE calories < :calories AND colour = :colour');
$sth->bindValue(':calories', $calories, PDO::PARAM_INT);
$sth->bindValue(':colour', $colour, PDO::PARAM_STR);
$sth->execute();
?>

See also: What is the difference between bindParam and bindValue?

Upvotes: 0

Marc B
Marc B

Reputation: 360662

Given your 00000 error code, that actually means the query succeeded. The problem is with your fetch logic:

if (!$query->fetch_assoc()) {
    ^--- remove

if the fetch succeeds, the call will return not-false, aka true. You then invert that with ! and turn it into a false, causing the other code to run.

Upvotes: 0

Michael Berkowski
Michael Berkowski

Reputation: 270617

Remove the quotes from the :search param:

$query = $db->prepare("select * from locks_for_sale where type_3=:search");
//--------------------------------------------------------------^^^^^^^^^^

If quoted, it would be treated as a literal string ':search', and you'll wind up with an error for binding an incorrect number of parameters.

If you are new to prepared statements, try not to think of them as the equivalent of placing variables into a SQL string as you would when concatenating or interpolating variables. Instead you are passing the parameter values directly into the database engine, which will in turn place them accordingly into the query. It becomes the RDBMS' responsibility to construct a valid statement with appropriate quoting.

Upvotes: 2

Related Questions