What is wrong with my PDO prepared statement?

Question

Obviously, I am preparing the statement wrong, but I am not certain what I am doing wrong.

These 2 code segments are identical, except for the second line.

This fails:

    $dbh = new PDO('mysql:host=localhost;dbname=' . $DB_Database, $DB_UserName, $DB_Password);
    $sth = $dbh->prepare("SELECT * FROM `PNB`.`Users` WHERE `Users`.`EMail` = :email OR `Users`.`Temp_EMail` = :temp_email");
    $sth->execute(array(':email' => $email, ':temp_email' => $email));
    $sth->setFetchMode(PDO::FETCH_ASSOC);
    $res = $sth->fetch();
    $dbh = null;

This hard-coded test works:

    $dbh = new PDO('mysql:host=localhost;dbname=' . $DB_Database, $DB_UserName, $DB_Password);
    $sth = $dbh->prepare("SELECT * FROM `PNB`.`Users` WHERE `Users`.`EMail` = 'me@example.com' OR `Users`.`Temp_EMail` = 'me@example.com'");
    $sth->execute(array(':email' => $email, ':temp_email' => $email));
    $sth->setFetchMode(PDO::FETCH_ASSOC);
    $res = $sth->fetch();
    $dbh = null;

What am I doing wrong?

Thanks!

UPDATE: Solved!

The exact issue is still unknown, but seems to be related to the 'excessive naming' suggested by user 'Your Common Sense' in the comments below.

This works just fine:

$dbh = new PDO('mysql:host=localhost;dbname=' . $DB_Database, $DB_UserName, $DB_Password);
$sth = $dbh->prepare("SELECT * FROM Users WHERE EMail=:email OR Temp_EMail=:temp_email");
$sth->execute(array(':email' => $email, ':temp_email' => $email));

Thanks to everyone. I learned lots AND resolved the issue.

Message to Your Common Sense; If you form your comment as an 'Answer', then I can accept it.

Btw, there is no use for such excessive naming. `"SELECT * FROM Users WHERE EMail = :email OR Temp_EMail = :temp_email"` is okay. — Your Common Sense, Feb 25 '13 at 19:21
Thanks. I suspected the excessive naming was no value add. Just wanted to be consistent with examples I was seeing while I debug this. — Eric, Feb 25 '13 at 19:49

score 0 · Answer 1 · answered Feb 25 '13 at 19:19

It's hard to answer on sight.
Your code seems okay to me. So, debugging seems the only way.

What am I doing wrong?

Always ask this question from your PDO.
Every time you're connecting to PDO, do it this way (also make sure you can see errors, either on-screen or logged):

error_reporting(E_ALL);
$opt = array(PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION);
$dsn = 'mysql:host=localhost;dbname=' . $DB_Database;
$dbh = new PDO($dsn, $DB_UserName, $DB_Password, $opt);

if there is an error - you'll be notified.
If there isn't - check typo-like problems.

Where will I be notified? I tried it, and there is no difference. The query fails, and I don't know why. — Eric, Feb 25 '13 at 19:46

mika · Answer 2 · 2013-02-25T19:39:06.270

0

Just a quick try - Do you get it right with these two lines ?

$sth = $dbh->prepare("SELECT * FROM `PNB`.`Users` WHERE `Users`.`EMail` = :email OR `Users`.`Temp_EMail` = :temp_email");
$sth->execute(array(':email' => 'me@example.com', ':temp_email' => 'me@example.com'));

in other words... Did you set your $email variable ?

edited Feb 25 '13 at 19:39

answered Feb 25 '13 at 19:26

mika

1,971
3
18
32

No. I get the same result. The query fails. Going to try the error reporting steps from user 'Your Common Sense'. – Eric Feb 25 '13 at 19:39

score 0 · Answer 3 · answered Feb 26 '13 at 00:32

try this

  $dbh = new PDO('mysql:host=localhost;dbname=' . $DB_Database, $DB_UserName, $DB_Password);
$sth = $dbh->prepare("SELECT * FROM `PNB`.`Users` WHERE `Users`.`EMail` = :email OR `Users`.`Temp_EMail` = :temp_email");
$sth->bindParam(':email', $email, PDO::PARAM_STR);
$sth->bindParam(':temp_email', $email, PDO::PARAM_STR);
$sth->execute();
$res = $sth->fetch(PDO::FETCH_ASSOC);
$dbh = null;

What is wrong with my PDO prepared statement?

3 Answers3