Alexandr Krivosheev Alexandr Krivosheev - 4 years ago 74
MySQL Question

mysql && php issue with id

i am getting the following error in terminal

127.0.0.1:36368 [500]: /create_account.php - Uncaught PDOException: SQLSTATE[21S01]: Insert value list does not match column list: 1136 Column count doesn't match value count at row 1 in /home/hue/social_network/classes/DB.php:10
Stack trace:
#0 /home/hue/social_network/classes/DB.php(10): PDOStatement->execute(Array)
#1 /home/hue/social_network/create_account.php(7): DB::query('INSERT INTO use...', Array)
#2 {main}
thrown in /home/hue/social_network/classes/DB.php on line 10


Here is my php code:

<?php
include('classes/DB.php');
if (isset($_POST['createaccount'])) {
$username = $_POST['username'];
$password = $_POST['password'];
$email = $_POST['email'];
DB::query('INSERT INTO users VALUES (:username, :password, :email)',
array(':username'=>$username, ':password'=>$password, ':email'=>$email));
echo "Success!";
}
?>

<h1>Register</h1>
<form action="create_account.php" method="post">
<input type="text" name="username" value="" placeholder="Username ..."><p />
<input type="password" name="password" value="" placeholder="Password ..."><p />
<input type="email" name="email" value="" placeholder="someone@somesite.com"><p />
<input type="submit" name="createaccount" value="Create Account">
</form>


db class:

<?php
class DB {
private static function connect() {
$pdo = new PDO('mysql:host=127.0.0.1;dbname=social_network;charset=utf8', 'root', 'helloworld');
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
return $pdo;
}
public static function query($query, $params = array()) {
$statement = self::connect()->prepare($query);
$statement->execute($params);
// $data = $statement->fetchAll();
// return $data;
}
}


i tried to INSERT values for id : ":id" and "\'\'" , but nothing change. my id field is auto_increment

Answer Source

Unless you're inserting all columns you need to specify which ones you're using:

INSERT INTO users (username, password, email) VALUES (:username, :password, :email)

You're omitting the column specification, so the default likely includes id and a bunch of other things.

Also before you go and write your own ORM accidentally keep in mind there are several out there that are feature-complete, battle-tested, and documented: Doctrine, Propel and Eloquent are just a few examples.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download