I have a table named user_data which contains 5 rows-id(primary key),name,address,phone,sex.When I try to insert values into the table via this query
mysqli_query($con,"INSERT INTO user_data VALUES ('Peter_malik', 'Griffin door',35897,'male')");
it doesnt work.But When I tried this one,it works.
mysqli_query($con,"INSERT INTO user_data (name,address,phone,sex) VALUES ('Peter_Gregory', 'Griffin door',35897,'male')");
I didnt understand what is the real issue behind this.I am using PHP 5.4.7 and XAMPP 1.8.1.
instead of this:
mysqli_query($con,"INSERT INTO user_data VALUES ('Peter_malik', 'Griffin door',35897,'male')");
Use this when the field is set as NOT NULL
mysqli_query($con,"INSERT INTO user_data VALUES (NULL, 'Peter_malik', 'Griffin door',35897,'male')");
Or use this when the field is set as NULL
mysqli_query($con,"INSERT INTO user_data VALUES (0, 'Peter_malik', 'Griffin door',35897,'male')")
See the mysql manual: http://dev.mysql.com/doc/refman/5.0/en/example-auto-increment.html
It does not insert in the first query since it assumes the first value i.e. 'Peter_malik' is for your primary key and it fails.
When you specify the column names in the query it knows which value is for which column.
I addition in the first case i.e.
'Peter_malik', 'Griffin door',35897,'male'
will refer to
id(pk),name,address,phone
So u need to pass the first value as NULL so that id gets auto incremented. So the first query should be as
VALUES (NULL, 'Peter_malik', 'Griffin door',35897,'male')
If your table has 5 columns id, name, address, phone and sex your staement has to provide 5 values, one for each column. Since your stament provides only 4 values, you need a column list to tell MySQL which values you do provide.
If your id column is a auto_increment column, you can provide null in your values clause.
So you have to say:
INSERT INTO user_data VALUES (NULL, 'Peter_malik', 'Griffin door',35897,'male');
OR
INSERT INTO user_data (name,address,phone,sex) VALUES
('Peter_malik', 'Griffin door',35897,'male')
You need to include the id column on your insert statement:
mysqli_query($con,"INSERT INTO user_data VALUES (null, 'Peter_malik', 'Griffin door',35897,'male')");
, provided that your id field is set to autoincrement! ;)
If you provide the ID also in your first query, it will work without any problem. Assuming that id=1
mysqli_query($con,"INSERT INTO user_data VALUES (1,'Peter_malik', 'Griffin door',35897,'male')");
As others said when you have not explicitly mentioned your column names in a query, you have to provide all the values.
when you are trying the following query then...
mysqli_query($con,"INSERT INTO user_data VALUES ('Peter_malik', 'Griffin door',35897,'male')");
In this query you only inserting 4 values in the table but the table has 5 field so it is causing problem because in the above query the value is inserting form first field & the sequence or datatype mismatching in the database because of this this query causing problem .
If u want insert wuthout specifing field then you may use the following query....
mysqli_query($con,"INSERT INTO user_data VALUES (0,'Peter_malik', 'Griffin door',35897,'male')");<br><br>
It Will work properly.
and in the your second query you also specifying the field name & corresponding their values so that's query not causing any problem.