Add column id to an existing table

Question

I a trying to add column id which will be a primary key with auto increment properties. So i execute this SQL query:

ALTER TABLE  `user_info` ADD  `id` INT NULL AUTO_INCREMENT PRIMARY KEY FIRST ;

The column is created successfully and even creates id for the already existing data.

But i am unable to insert anything inside the table anymore.

<?php
require "init.php";
$name=$_POST["user"];
$user_email=$_POST["user_email"];
$user_pass=$_POST["user_pass"];

$sql_query="SELECT * FROM user_info WHERE user_email='$user_email'";
$check=mysqli_fetch_array(mysqli_query($con,$sql_query));

if(isset($check)){
 $response["success"]=false;
 $response["message"]="Email already exists";
 echo json_encode($response);
}else{

 $sql_query="insert into user_info values('$name','$user_email','$user_pass');";

if(mysqli_query($con,$sql_query)){
 //echo "<h3> Data Insert success</h3>";
    $response["success"]=true;
 $response["message"]="Registration successful";
 echo json_encode($response);
}
else{
  $response["success"]=false;
 $response["message"]="Registration unsuccessful";
 echo json_encode($response);
}

}




?>

I googled around and found out that i could insert 0 or null in place of my id.

I purposefully read that :

In order to take advantage of the auto-incrementing capability of the column, do not supply a value for that column when inserting rows. The database will supply a value for you.

The code works well when i drop column id but when i add it, i get response as Registration unsuccessful within my json.

The workarounds from here or here did not seem to solve my problem.

What am i missing?

I'm not sure I am reading the sql correctly, but you should not allow a NULL value for the ID column. — jeroen, Mar 18 '16 at 09:30

score 6 · Accepted Answer · answered Mar 18 '16 at 09:26

You have four columns in your table now, however you only provide three values. Checking mysqli_error would give you an error message telling you this.

You can either change your SQL to send NULL for the id which will create an appropriate id:

insert into user_info values(null, 'Foo','Foo@example.com','password');

Or, even better is to tell MySQL which columns you are targeting. This means in the future that if you add columns your SQL wont break:

insert into user_info (name,email, password)
values('Foo','Foo@example.com','password');

Note: There is some issues with your code. You are vulnerable to SQL injection - if someone sends a malicious value it could run SQL commands you don't intend.
Additionally storing passwords in plain text is not a good idea. You should be hashing them with a secure algorithm. See: http://php.net/manual/en/faq.passwords.php

I am just trying to understand php-mysql intergration because i'm starting out. Thanks for your help. — Steve Kamau, Mar 18 '16 at 09:45
I am going to work on hashing passwords but what about sql injections? — Steve Kamau, Mar 18 '16 at 09:46
Either by using [mysqli_real_escape_string](http://php.net/manual/en/mysqli.real-escape-string.php) or by using [Prepared Statements](http://php.net/manual/en/mysqli.quickstart.prepared-statements.php). — Jim, Mar 18 '16 at 10:47

score 1 · Answer 2 · answered Mar 18 '16 at 09:29

Your INSERT syntax was correct before you added an ID column on first place:

$sql_query="insert into user_info values('$name','$user_email','$user_pass');";

But now you should specify the columns prior to the VALUES :

$sql_query="insert into user_info (name,email, password) values('$name','$user_email','$user_pass');";

Add column id to an existing table

2 Answers2