DaniWeb Forum Index > Web Development > PHP

Duplicate entry '' for key 2

•

Join Date: Apr 2008

Posts: 41

Reputation: cali_dotcom is an unknown quantity at this point

Solved Threads: 0

cali_dotcom cali_dotcom is offline

Offline

Light Poster

Duplicate entry '' for key 2

Nov 17th, 2008

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:

 Help with Code Tags 
 sql Syntax (Toggle Plain Text) 
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 ;
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:

 Help with Code Tags 
 PHP Syntax (Toggle Plain Text) 
$sql = 'INSERT INTO article_fields VALUES("NULL","'.$new_text.'","'.$field_type.'")';
			$insert = mysql_query($sql) or die(mysql_error());
$sql = 'INSERT INTO article_fields VALUES("NULL","'.$new_text.'","'.$field_type.'")';
			$insert = mysql_query($sql) or die(mysql_error());

Last edited by peter_budo; Nov 20th, 2008 at 12:42 pm. Reason: Keep It Organized - For easy readability, always wrap programming code within posts in [code] (code blocks) and [icode] (inline code) tags.

•

Join Date: Jun 2007

Posts: 1,233

Reputation: kkeith29 has a spectacular aura about

Solved Threads: 168

kkeith29

Offline

Nearly a Posting Virtuoso

Re: Duplicate entry '' for key 2

Nov 18th, 2008

you can't have the same primary key twice. hence the "duplicate entry". you are setting it as NULL. just omit the primary key entry in the values all together.

like this:

Help with Code Tags

PHP Syntax (Toggle Plain Text)

$sql = "INSERT INTO `article_fields` (`field_title`,`field_type`) VALUES ('{$new_text}','{$field_type}')";

Last edited by kkeith29; Nov 18th, 2008 at 12:10 am.

•

Join Date: Aug 2008

Posts: 14

Reputation: jack239 is an unknown quantity at this point

Solved Threads: 1

jack239

Offline

Newbie Poster

Re: Duplicate entry '' for key 2

Nov 18th, 2008

Because you have set the primary key to be auto incremented so you don't need to pass anything here.

Stuck in the middle of Java/PHP/Ajax development?

For free help on any technology and free articles visit http://www.techcubetalk.com

•

Join Date: Oct 2006

Posts: 232

Reputation: Rhyan is an unknown quantity at this point

Solved Threads: 24

Rhyan

Offline

Posting Whiz in Training

Re: Duplicate entry '' for key 2

Nov 18th, 2008

Keith,

You can insert records in a table exactly as cali_dotcom does, it is not oblgatory to do it by mentioning the column names where you want to insert data. Your approach is OK when you want to insert data in different order than the columns in the table.
The problem with the script is that "NULL" is different from NULL without quotes. MySQL considers "NULL" to be a string and does not validate it as the value NULL.

So this should work perfectly

 Help with Code Tags 
 PHP Syntax (Toggle Plain Text) 
$sql = 'INSERT INTO article_fields VALUES(NULL,"'.$new_text.'","'.$field_type.'")';
$sql = 'INSERT INTO article_fields VALUES(NULL,"'.$new_text.'","'.$field_type.'")';

Also, when inserting numbers it is good to insert them not surrounded by "" as MySQL validates numbers correctly.

" Of all the things I've lost,
I miss my mind the most...."
Mark Twain

•

Join Date: Apr 2008

Posts: 41

Reputation: cali_dotcom is an unknown quantity at this point

Solved Threads: 0

cali_dotcom cali_dotcom is offline

Offline

Light Poster

Re: Duplicate entry '' for key 2

Nov 18th, 2008

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?

•

Join Date: Oct 2006

Posts: 232

Reputation: Rhyan is an unknown quantity at this point

Solved Threads: 24

Rhyan

Offline

Posting Whiz in Training

Re: Duplicate entry '' for key 2

Nov 18th, 2008

I suggest you do the following :

echo $sql;

Then simply copy the string from your browser and paste it into phpmyadmin sql statement and run it.
If it shouts an error, and it is still the same, paste here the sql string and we'll see. I don't see anything wrong there, but you never know, a single ' may ruin the whole thing...

" Of all the things I've lost,
I miss my mind the most...."
Mark Twain

•

Join Date: Apr 2008

Posts: 41

Reputation: cali_dotcom is an unknown quantity at this point

Solved Threads: 0

cali_dotcom cali_dotcom is offline

Offline

Light Poster

Re: Duplicate entry '' for key 2

Nov 18th, 2008

i ran this code on php myadmin:
INSERT INTO `article_fields`(`field_title`, `field_type`) VALUES("'.$new_text.'","'.$field_type.'")
and it worked. the problem is running that code from a php script. did i mention that i was using the zend framework.

•

Join Date: Oct 2006

Posts: 232

Reputation: Rhyan is an unknown quantity at this point

Solved Threads: 24

Rhyan

Offline

Posting Whiz in Training

Re: Duplicate entry '' for key 2

Nov 18th, 2008

No, you did not mention the ZEND.
However, this insert as you have typed it contains the variable names instead of variable values. Is this the php code or the result from the php echo command?

I am not 100% sure you need the table and field names surrounded by ' ', but, still if it works, maybe it is ok.
Why don't you try this code. It works fine for me in multiple pages, so I have it verified numerous times.

 Help with Code Tags 
 PHP Syntax (Toggle Plain Text) 
$sql = 'INSERT INTO article_fields VALUES (NULL, \''.$new_text.'\', \''.$field_type.'\')';
$sql = 'INSERT INTO article_fields VALUES (NULL, \''.$new_text.'\', \''.$field_type.'\')';

I think that this code will be ok also for the zend framework.

" Of all the things I've lost,
I miss my mind the most...."
Mark Twain

•

Join Date: Apr 2008

Posts: 41

Reputation: cali_dotcom is an unknown quantity at this point

Solved Threads: 0

cali_dotcom cali_dotcom is offline

Offline

Light Poster

Re: Duplicate entry '' for key 2

Nov 18th, 2008

•

Originally Posted by Rhyan

However, this insert as you have typed it contains the variable names instead of variable values. Is this the php code or the result from the php echo command?

.

i'm not sure what you mean but those are vaiables i got from a command like:

$new_text = $_GET['new_text'];

•

Join Date: Apr 2008

Posts: 41

Reputation: cali_dotcom is an unknown quantity at this point

Solved Threads: 0

cali_dotcom cali_dotcom is offline

Offline

Light Poster

Re: Duplicate entry '' for key 2

#10

Nov 18th, 2008

•

Originally Posted by Rhyan

Help with Code Tags

PHP Syntax (Toggle Plain Text)
$sql = 'INSERT INTO article_fields VALUES (NULL, \''.$new_text.'\', \''.$field_type.'\')';
$sql = 'INSERT INTO article_fields VALUES (NULL, \''.$new_text.'\', \''.$field_type.'\')';

i just tried this code but it still didn't work.