php - prepare and execute keeps executing without adding new values

Question

This has annoyed me for a while now, I have tried this:

            $stmt = self::$conn->prepare("
            INSERT INTO `database`.`table` (
            `facebook_id` , 
            `email` ,
            `first_name` ,
            `last_name` ,
            `gender`
            )
            VALUES (
            ':id', NULL , ':first_name', ':last_name', NULL
            );
            ");

            $stmt->bindParam(':id', $id);
            $stmt->bindParam(':first_name', $first_name);
            $stmt->bindParam(':last_name', $last_name);
            $stmt->execute();

The bindParam part clearly don't work since id becomes 0 and first and last name becomes :first_name and :last_name in the database. So I tried this:

            $stmt->execute(array(
                ':id' => 457897541,
                ':first_name' => $first_name,
                ':last_name' => $last_name
            ));

again I get id = 0, and first andn last name = :first_name, :last_name in the database! What am I doing wrong here?

You don't need to quote your bound parameters in the SQL statement. Doing so turns them into strings, I believe, rather than variables to be interpreted. — andrewsi, May 27 '13 at 12:30
It annoyed you for a while but you didn't try to do it the way they tell you in the manual. Because, why use manual, it's useless, better ask at SO. — N.B., May 27 '13 at 13:12
I tried of course todo it as in the manuel, I didn't notice the little ' difference.. Is that so weird @N.B.. Instead of helping other people you used your time to insult people you don't know over the internet. Idiot. Thanks andrewsi. — ganjan, May 27 '13 at 15:04
I helped you. Next time you don't read something and things don't work, you'll remember that someone told you that you're stupid. On the other hand, you have the ability to unstupify yourself. Whether you do it or not is up to you. I know it sucks to be told to read, but yes - read and do as it says in the manual. And do it carefully. And if you do that, the amount of douchebags like me will go to 0. — N.B., May 27 '13 at 15:06

score 2 · Accepted Answer · answered May 27 '13 at 12:37

When you're preparing a statement, you don't need to quote the bound parameters. Indeed, as @Voictus says, you must not - it turns them from variables to be interpreted into actual strings. The prepare() call will take care of the quoting for you.

You should have this instead:

        $stmt = self::$conn->prepare("
        INSERT INTO `database`.`table` (
        `facebook_id` , 
        `email` ,
        `first_name` ,
        `last_name` ,
        `gender`
        )
        VALUES (
        :id, NULL , :first_name, :last_name, NULL
        );
        ");

Additinally - if you've got fields in your insert that are NULLs, you might as well just omit them.

php - prepare and execute keeps executing without adding new values

1 Answers1