Query works in SQL and not in PDO

Question

I'm using PDO in PHP to connect to MySQL. My project is a chat app

Everytime someone sends a new message, I insert the message into table chat_messages, and I update a timestamp in table chat_users to indicate that that was the last time the user was active.

$sql = "INSERT INTO chat_messages
                (email_sender, message, sent) 
        VALUES  ('" . $email . "', '" . $message . "', '" . time() . "')";
$query = $pdo->prepare($sql);
if ($query->execute()) {
    $sql2 = "UPDATE chat_users SET last_active = UNIX_TIMESTAMP() 
                WHERE email = '$email'";
    $pdo->prepare($sql2)->execute();
    // to test my query syntax is correct:
    echo $sql2;
}

The timestamp is not being updated in the database. When i copy paste the output of echo $sql2 into phpMyAdmin, the query works.

What am i doing wrong?

Thank you

Hi, what value do you get for `last_active` when you execute through PHP? — Ermac, Oct 31 '18 at 22:40
@ankabout it remains unchanged in the database. Value doesnt change — HelpASisterOut, Oct 31 '18 at 22:46
what you get if you `print_r($pdo->errorInfo());` just after the second query? — Paolo, Oct 31 '18 at 22:55
@Paolo ErrorInfo() is an empty array, which indicates no errors. It's weird. — HelpASisterOut, Oct 31 '18 at 23:20

score 4 · Answer 1 · answered Oct 31 '18 at 22:42

You're really missing out on a lot of the benefit of using prepared statements if you concat variables into the SQL like that. If you prepare with placeholders and bind your values, it will probably take care of your problems as well as being more secure against SQL injections.

$sql = "INSERT INTO chat_messages(email_sender, message, sent) VALUES (?, ?, ?)";
$query = $pdo->prepare($sql);
if ($query->execute([$email, $message, $time])) {
    $sql2 = "UPDATE chat_users SET last_active = UNIX_TIMESTAMP() WHERE email = ?";
    $pdo->prepare($sql2)->execute([$email]);
    // to test my query syntax is correct:
    echo $sql2;
}

If your query is failing (and even if it isn't) you should set PDO to throw exceptions when queries fail, so you'll get a descriptive error message as to what went wrong.

Query works in SQL and not in PDO

1 Answers1