Cannot insert into database using php with PDO:: execute()

Question

I have created a simple mySQL database, and I am trying to insert some test data into it using PHP. When I ran the method on Firefox I got the following message, and I can not resolve this problem:

Warning: PDOStatement::execute() [pdostatement.execute]: SQLSTATE[HY093]: Invalid parameter number:

My method to insert sample into my datebase is:

public function confirmInsert(){
    $admin="admin";
    $pass="12345v";
    $mail="[email protected]";  
    $insertSQL = "INSERT INTO 'users' ('user_name', 'user_pass, 'user_email')
            VALUES (
                    :admin, 
                    :pass,
                    :mail)";
    try {

        $stmt = $this->db->prepare($insertSQL);
        $stmt ->bindParam(':user_name',$admin, PDO::PARAM_STR);
        $stmt ->bindParam(':user_pass', $pass, PDO::PARAM_STR);
        $stmt->bindParam(':user_email', $mail,PDO::PARAM_STR);
        $stmt->execute();
        $stmt->closeCursor();
        return TRUE;
        } catch (Exception $e) {
            $e -> getMessage();
        }
}

I am running on //localhost, and using apache 2.2 and php 5.2.17. Thanks!!

@icktoofay: and even more - there shouldn't be single quotes at all, but backticks (or nothing) — zerkms
– zerkms, Commented Jul 11, 2011 at 0:54

zman · Accepted Answer · 2011-07-11 04:28:02Z

3

'You don't have the same name for your parameters in the query and when you bind them. You also have to remove the quotes around the fields name in the query.

Should be better that way:

public function confirmInsert(){
    $admin='admin';
    $pass='12345v';
    $mail='[email protected]';  
    $insertSQL = "INSERT INTO users (user_name, user_pass, user_email)
            VALUES (
                    :admin, 
                    :pass,
                    :mail)";
    try {

        $stmt = $this->db->prepare($insertSQL);
        $stmt ->bindParam(':admin',$admin, PDO::PARAM_STR);
        $stmt ->bindParam(':pass', $pass, PDO::PARAM_STR);
        $stmt->bindParam(':mail', $mail,PDO::PARAM_STR);
        $stmt->execute();
        $stmt->closeCursor();
        return TRUE;
        } catch (Exception $e) {
            $e -> getMessage();
        }
}

edited Jul 11, 2011 at 4:28

zman

154 bronze badges

answered Jul 11, 2011 at 0:51

Julien Ducro

8541 gold badge9 silver badges26 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

zman Over a year ago

with your change, it does get rid of the error message, but it still doesnt insert anything into the database. The column names as you can see in the prepare string are: 'user_name', 'user_pass, 'user_email'. When binding with bindParam, admin, pass, mail, respectively, are we changing the column names??

Julien Ducro Over a year ago

No the column names stay the same, you add some errors in the usage of quotes, I've updated my answer

Jason Rogers · Accepted Answer · 2011-07-12 09:40:47Z

0

I don't yet have the rights to make comments, so i have to post this as an answer..

R.E. Pompom6784's answer, using bindParam() the variable name need not match the parameter:

$stmt->bindParam(':user_name', $admin, PDO::PARAM_STR);

Should work just fine.

You are binding :user_name to accept only the variable named $admin

..if that makes things a bit clearer.

If you would like some more information on this topic, here's a good article to read through:

Why you Should be using PHP’s PDO for Database Access

It includes examples where it is necessary to have matching variable and placeholder names.

edited Jul 12, 2011 at 9:40

answered Jul 12, 2011 at 9:24

Jason Rogers

1072 silver badges10 bronze badges

Collectives™ on Stack Overflow

Cannot insert into database using php with PDO:: execute()

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related