php mysql INSERT INTO column if exist

Question

in a php file that use this line of code to insert some text in the table TABONE

mysql_query("INSERT INTO `TABONE` VALUES ('$q1', '$q2', '$q3',  '$q4',  '$q5')") ;

the code work fine because "we receive 5 variables and this table contain 5 columns"

but sometimes we receive 5 variables for a table that contain only 3 columns and in this case we just want to insert the first 3 variables ('$q1', '$q2', '$q3')in the 3 columns.

in this case

mysql_query("INSERT INTO `TABONE` VALUES ('$q1', '$q2', '$q3',  '$q4',  '$q5')") ;

doesn't work - How to correct this.

Please stop writing new code with the ancient mysql_* functions. They are no longer maintained and community has begun the deprecation process. Instead you should learn about prepared statements and use either PDO or MySQLi. If you care to learn, here is a quite good PDO-related tutorial. Edit: You should really be explicitly specifying which column which value belongs to. Relying on current column ordering is dangerous. — user1233508
– user1233508, Commented Jul 29, 2012 at 11:32

eggyal · Accepted Answer · 2012-07-29 11:37:41Z

As stated in the manual:

If you are not running in strict SQL mode, any column not explicitly given a value is set to its default (explicit or implicit) value. For example, if you specify a column list that does not name all the columns in the table, unnamed columns are set to their default values. Default value assignment is described in Section 11.5, “Data Type Default Values”. See also Section 1.8.6.2, “Constraints on Invalid Data”.

If you want an INSERT statement to generate an error unless you explicitly specify values for all columns that do not have a default value, you should use strict mode. See Section 5.1.6, “Server SQL Modes”.

Use the keyword DEFAULT to set a column explicitly to its default value. This makes it easier to write INSERT statements that assign values to all but a few columns, because it enables you to avoid writing an incomplete VALUES list that does not include a value for each column in the table. Otherwise, you would have to write out the list of column names corresponding to each value in the VALUES list.

You can also use DEFAULT(col_name) as a more general form that can be used in expressions to produce a given column's default value.

Therefore, you can:

name the columns and omit those for which you want default values:

INSERT INTO TABONE (colA, colB, colC) VALUES ('$q1', '$q2', '$q3')

Or, if you prefer the SET syntax:

INSERT INTO TABONE SET colA='$q1', colB='$q2', colC='$q3'

explicitly set the other columns to their default values with the DEFAULT keyword:
```
INSERT INTO TABONE VALUES ('$q1', '$q2', '$q3', DEFAULT, DEFAULT)
```

Collectives™ on Stack Overflow

php mysql INSERT INTO column if exist

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related