52

I need to insert a long row with 32 fields into a MySQL table.

I'd like to do something like this:

$sql="insert into tblname values (... 32 fields ...)";

Obviously, it works fine if the fields are in the same order as the MySQL table fields. But, my table has an auto-increment id as it's first field.

What I want is to fill in all table names but the first (id) one.

Suggestions?

Super Kai - Kazuya Ito
  • 22,221
  • 10
  • 124
  • 129
Paulo Bueno
  • 2,499
  • 6
  • 42
  • 68

5 Answers5

84

Just use NULL as your first value, the autoincrement field will still work as expected:

INSERT INTO tblname VALUES (NULL, ... 32 Fields ... )
Doug Neiner
  • 65,509
  • 13
  • 109
  • 118
  • Very good. I've also found an alternate solution as follow: $resultx = mysql_query( "SHOW TABLE STATUS LIKE 'diretorio'"); $auto_incr_val = mysql_result($resultx, 0, 'Auto_increment'); – Paulo Bueno Dec 09 '09 at 03:04
  • 13
    @Paulo: you have no idea what trouble you may open yourself up to by doing that. Use NULL - it's the way MySQL designed it to work. – gahooa Dec 11 '09 at 22:52
  • 1
    It is generally fine to use 0 to trigger auto_increment unless you enable NO_AUTO_VALUE_ON_ZERO mode. NULL, 0, and DEFAULT are all accepted values to trigger this. See http://dev.mysql.com/doc/refman/5.6/en/sql-mode.html#sqlmode_no_auto_value_on_zero for more info. – kojow7 Aug 24 '15 at 05:24
  • Similarly, NULL can be used for other columns which have default values defined, e.g. CURRENT_TIMESTAMP. – Sandeepan Nath Mar 25 '16 at 08:01
  • this null method does not work for me. i dont know why? – wisnshaftler Aug 28 '21 at 17:56
14

Insert NULL into the auto-increment field.

I recommend that unless this is a hack script, you use field names. The rationale is that your code will break if you ever add a field to the table or change their order.

Instead, be explicit with field names, and it will go much better in the future.

gahooa
  • 131,293
  • 12
  • 98
  • 101
0

Use NULL or 0 to insert an auto-incremented value as shown below:

                         -- Here
INSERT INTO tblname VALUES (NULL, ... 32 Fields ... )

                         -- Here
INSERT INTO tblname VALUES (0, ... 32 Fields ... )
Super Kai - Kazuya Ito
  • 22,221
  • 10
  • 124
  • 129
-1

We should omit any column values when we try without column name in insert query,

Advise if above information is wrong.

bharanikumar Bs
  • 183
  • 3
  • 8
  • 16
-3

Here's a simple shortcut that I've used:

$fieldlist=$vallist='';
foreach ($_POST as $key => $value) {
 $fieldlist.=$key.',';
 $vallist.='\''.urlencode($value).'\','; }
$fieldlist=substr($fieldlist, 0, -1);
$vallist=substr($vallist, 0, -1);
$sql='INSERT INTO customer_info ('.$fieldlist.') VALUES ('.$vallist.')';

Please note that this code would be vulnerable to SQL Injection and should be modified to accommodate PDO's, but I felt this simplified script would more directly answer the question with regards to the originally posted code.

Chaya Cooper
  • 2,566
  • 2
  • 38
  • 67
  • 2
    This code is vulnerable to SQL Injection. Read more here please: http://stackoverflow.com/questions/11939226/sql-injections-and-adodb-library-general-php-website-security-with-examples/12123649 – Ilia Ross Aug 26 '12 at 09:29
  • 1
    @IliaRostovtsev - While I agree with you and have switched over to PDO's in order to avoid SQL injection, I feel there's a benefit to answering the question as it pertains to the posted code – Chaya Cooper Nov 23 '14 at 01:49
  • 1
    Yes, probably. It also worth mentioning aboug the possible problems. (I didn't downvote, just in case) – Ilia Ross Nov 23 '14 at 07:45