on duplicate key

[php_b34st]

I have a table in my db:

 

code | name | club | price | points

 

At the moment code is set as primary key so when I use the following script the table gets updated correctly:

 

$query = "INSERT INTO playerlist (code, name, club, price, points, position) 
		  VALUES ('$listitem[0]', '$listitem[1]', '$listitem[2]', '$listitem[3]', '$listitem[4]', '$position')
		  ON DUPLICATE KEY UPDATE points=$listitem[4]";
	mysql_query($query) or die('Error, insert query failed');

 

however now I am trying to add an id auto increment primary key column but the above script stops working correctly as its now referencing from id rather than code. Instead of updating a previous entry it adds another. Is there a way around this?

 

thanks

[kratsg]

Umm, this is a little confusing, clarify some points:

 

1) Are you trying to insert a row, and if the code ID already exists, update that record instead of adding a new one? And if that code ID does not already exist, create a new record?

2) What exactly is failing, the script or the fact that the database is not auto-incrementing?

[php_b34st]

Sorry if I was not clear.

 

1. Yes I am trying to update the database the code and name will never change so i decided to update using code as the key however if there is a new name i need to insert a new row.

 

2. The problem is when i add the id auto increment column it does not update anymore it just adds new rows even if there is already a similar entry

 

just to clarify I am adding a new column called id rather than updating the coce column so the new table will look like this:

 

id | code | name | club | price | points

 

and id is now the primary key rather than code (the code is a pre defined code, id will auto increment and be there for reference to other tables)

[kratsg]

1. Yes I am trying to update the database the code and name will never change so i decided to update using code as the key however if there is a new name i need to insert a new row.

So, you want it to add a new row if it has the same code but a different name?

 

2. The problem is when i add the id auto increment column it does not update anymore it just adds new rows even if there is already a similar entry

That's because you are executing an "INSERT INTO" query. It literally means to insert a new row.

 

Just perform two queries instead, this is just something too.. complicated for a simple task.

 

Do the following:

 

UPDATE the row with the corresponding information, use mysql_affected_rows, if this is 0, just do an insert. I've never used an ON DUPLICATE KEY ever, and from the looks of it in the manual, it's way too complicated :-P

[php_b34st]

1. Yes I am trying to update the database the code and name will never change so i decided to update using code as the key however if there is a new name i need to insert a new row.

So, you want it to add a new row if it has the same code but a different name?

 

No I want to update the row if it has the same code, I want to insert a new row if there is a new code

 

Thanks for the help I will try to use mysql_affected_rows as you suggested