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?

Answer 1

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.