Duplicate entry '' for key 2

[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:

$sql = 'INSERT INTO article_fields VALUES("NULL","'.$new_text.'","'.$field_type.'")';
			$insert = mysql_query($sql) or die(mysql_error());

[kkeith29]

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:

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

[jack239]

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

[Rhyan]

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

$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.

[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?

[Rhyan]

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...

[cali_dotcom]

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.

[Rhyan]

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.

$sql = 'INSERT INTO article_fields VALUES (NULL, \''.$new_text.'\', \''.$field_type.'\')';

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

[cali_dotcom]

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'];

[cali_dotcom]

$sql = 'INSERT INTO article_fields VALUES (NULL, \''.$new_text.'\', \''.$field_type.'\')';

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

[Rhyan]

OK, what I mean is the following

$new_text is the variable name by which you refer to it in your code. The variable value is the value of $_GET['whatever'].

My question is - your string

INSERT INTO `article_fields`(`field_title`, `field_type`) VALUES("'.$new_text.'","'.$field_type.'")

looks exactly as you would have it typed into your code.
But when you call the page wich contains ECHO "INSERT STRING HERE $variable"; and PHP parses the code - the html output should be something like
INSERT STRING HERE my value.

So what bothers me in your post is that if the HTML output of the echo command is exactly as you have it posted here, there is something wrong in the formatting of your php string.

Do you understand me better now?

[kkeith29]

the ` (which are different than ') are not necessary, but I see phpMyAdmin and other programs use them a lot so it kind of became a habit of mine. it also helps me identify database and table names in the queries i write. i wrote the column names in so we could avoid silly errors that i figured s/he would have.

cali_dotcom:

if you have some code to look at, post it. maybe we can find if something else is preventing this from working, because i know the sql i posted works.

[correio24]

It seems your table is not auto-incrementing...
Try to add "AUTO_INCREMENT" to your primary key:

CREATE TABLE table (

id_primary INT UNSIGNED NOT NULL AUTO_INCREMENT...

[almostbob]

mysql_query(sprintf("insert into article_fields
(new_text, fields_type) values
('%s', '%s');",
$new_text, $field_type))