Duplicate entry '' for key 2

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
cali_dotcom
Forum Commoner
Posts: 49
Joined: Fri Aug 22, 2008 7:28 pm
Location: Rancho Cucamonga, CA

Duplicate entry '' for key 2

Post by cali_dotcom »

hi everyone,
i keep on getting this error when i try to insert a value into a table via php.
Duplicate entry '' for key 2

i've tried a lot of solutions from the web including changing the the primary key from int to bigint. i also flushed the database and deleted all entries from the database but i still got that error. i could insert records from the nysql command prompt but not from the php script.
does anyone know what might be wrong?
the table in question looks like this:

CREATE TABLE `article_fields` (
`id` int(11) NOT NULL auto_increment,
`field_title` varchar(75) NOT NULL,
`field_type` varchar(75) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=3 ;


and my php code to insert data into the table looks like this:

Code: Select all

$sql = 'INSERT INTO article_fields VALUES("NULL","'.$new_text.'","'.$field_type.'")';
            $insert = mysql_query($sql) or die(mysql_error());
User avatar
Syntac
Forum Contributor
Posts: 327
Joined: Sun Sep 14, 2008 7:59 pm

Re: Duplicate entry '' for key 2

Post by Syntac »

I don't think you're supposed to put quotes around NULL. Also, you're passing a string value to an int field.
User avatar
califdon
Jack of Zircons
Posts: 4484
Joined: Thu Nov 09, 2006 8:30 pm
Location: California, USA

Re: Duplicate entry '' for key 2

Post by califdon »

When using an INSERT SQL statement where you don't enumerate the fields, don't include a value for the auto-increment field.

Code: Select all

$sql = 'INSERT INTO article_fields VALUES("'.$new_text.'","'.$field_type.'")';
or easier, yet:

Code: Select all

$sql = "INSERT INTO article_fields VALUES('$new_text','$field_type')";
cali_dotcom
Forum Commoner
Posts: 49
Joined: Fri Aug 22, 2008 7:28 pm
Location: Rancho Cucamonga, CA

Re: Duplicate entry '' for key 2

Post by cali_dotcom »

i've tried every single suggestion i found on the web, but i still wont work. actually, it worked just one time and then i clicked again, and then it gave me the same error again. i,ve tried flushing the table, emptying the table checking and repairing the table from phpmyadmin.

does anyone know what could still be wrong?
User avatar
Syntac
Forum Contributor
Posts: 327
Joined: Sun Sep 14, 2008 7:59 pm

Re: Duplicate entry '' for key 2

Post by Syntac »

Try it with a MyISAM table?
cali_dotcom
Forum Commoner
Posts: 49
Joined: Fri Aug 22, 2008 7:28 pm
Location: Rancho Cucamonga, CA

Re: Duplicate entry '' for key 2

Post by cali_dotcom »

i actually tried it with most engine types in the php myadmin
User avatar
califdon
Jack of Zircons
Posts: 4484
Joined: Thu Nov 09, 2006 8:30 pm
Location: California, USA

Re: Duplicate entry '' for key 2

Post by califdon »

The only thing I can suggest is to separate the issue from your script. Write a completely separate script that does nothing but connect to the database and insert one row with constant values. When you try to debug a script, there are often hidden things elsewhere that complicate the problem. If you are still unable to insert a row, either you have a syntax error that somehow we're not seeing or there's something terribly wrong with MySQL, probably requiring reinstallation. I'd limit the test script to something as short as:

Code: Select all

<?php
$host='....';
$user='....';
$pwd='....';
mysql_connect($host,$user,$pwd) or die(mysql_error());
mysql_select_db('....');
$sql="INSERT INTO article_fields VALUES ('test value 1','test value 2')";
mysql_query($sql);
$sql="INSERT INTO article_fields (`field_title`,`field_type`) VALUES ('test value 3','test value 4')";
mysql_query($sql);
?>
and see if that inserts 2 new rows. It should.
Post Reply