Page 1 of 1

Duplicate entry '' for key 2

Posted: Mon Nov 17, 2008 9:28 pm
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());

Re: Duplicate entry '' for key 2

Posted: Mon Nov 17, 2008 9:35 pm
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.

Re: Duplicate entry '' for key 2

Posted: Mon Nov 17, 2008 10:46 pm
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')";

Re: Duplicate entry '' for key 2

Posted: Tue Nov 18, 2008 6:01 pm
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?

Re: Duplicate entry '' for key 2

Posted: Tue Nov 18, 2008 6:12 pm
by Syntac
Try it with a MyISAM table?

Re: Duplicate entry '' for key 2

Posted: Tue Nov 18, 2008 6:57 pm
by cali_dotcom
i actually tried it with most engine types in the php myadmin

Re: Duplicate entry '' for key 2

Posted: Tue Nov 18, 2008 7:19 pm
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.