PHP：PDO MySQL错误

I don't really know MySQL but I try.

I have this script in PHP

$sql = $DB->prepare("INSERT INTO `users`(`id`, `firstname`, `lastname`, `email`, `password`) VALUES ($this->firstname, $this->lastname, $this->email, $this->password))");

and when I use

print_r($sql->errorInfo());

It is giving me this error

Warning: PDOStatement::execute(): SQLSTATE[HY093]: Invalid parameter number: no parameters were bound in C:\Bitnami\wampstack-5.5.27-0\apache2\htdocs\OOPLoginegister.php on line 115 Array ( [0] => HY093 [1] => [2] => )

If anyone could help me, I would appreciate it very much.

Thank you.

EDIT: I changed it to

$sql = $DB->prepare("INSERT INTO `users`(`firstname`, `lastname`, `email`, `password`) VALUES ($this->firstname, $this->lastname, $this->email, $this->password))");

And now it's giving me

Array ( [0] => 42000 [1] => 1064 [2] => You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '@gmail.com, ff0b80f26259f9c0178aeed5198bac48))' at line 1 )

You are misusing the prepare() function. When using prepared statements, you are supposed to use either ? or :name as placeholders for your values. This prevents you from constructing a malicious SQL query from user input.

Also, you are listing 5 fields, but only give 4 values. If id is an AUTO_INCREMENT field then it can just be omitted from the query.

Finally, you had too many ) in your query,

$sql = $DB->prepare("INSERT INTO `users`(`firstname`, `lastname`, `email`, `password`)
    VALUES (:firstname, :lastname, :email, :password)");

Now you just pass an array of values to execute() to bind to the placeholders.

$sql->execute(array(
    'firstname' => $this->firstname,
    'lastname' => $this->lastname,
    'email' => $this->email,
    'password' => $this->password
));

P.S. Your original code didn't work because you forgot to put quotes around your strings.

INSERT INTO `users` (`email`) VALUE ('test@example.com');

Your ID-column does not have a corresponding value to insert, if that column is auto-incrementint you can skip it, like so:

$sql = $DB->prepare("INSERT INTO `users`(`firstname`, `lastname`, `email`, `password`) VALUES ($this->firstname, $this->lastname, $this->email, $this->password))");

you need as many values to insert, as you have columns

Assuming your using PDO, you need to bind parameters with a prepared statement.

Here's an example, using the PDO::prepare documentation for reference:

$statement = $DB->prepare("INSERT INTO `table` (`id`) VALUES (?)");

$statement->execute(array($user_id));

Additional example using mysqli as an alternative...

$statement = $DB->prepare("INSERT INTO `table` (`id`) VALUES (?)");

$statement->bind_param("i", $user_id);

$statement->execute();

You need to bind each of your parameters instead of putting them inline your prepared statement.

An Insert query need to have the same parameters in table fields and values in the same order.

So, if you have id, firstname, lastname, email, password you need to have idValue, firstnameValue, lastnameValue, emailValue, passwordValue

A good way to try if query is well formed is do an echo $sql or a var_dump($sql) and paste the result on a sql ID query like Mysql Workbench or HeidiSql