using mysql_insert_id($CONN) with ON DUPLICATE KEY UPDATE query

[Greywacke]

all of a sudden this stopped working - in that the mysql_insert_d($conn) function is now returning the LAST_INSERT_ID value generated and not the ON DUPLICATE KEY UPDATE id.here is the sql query run, now its returning an id that does not exist in the table.

		// create consumer		$csql = "INSERT INTO 					19_consumers (						`text_ConsumerE-Mail`, 						text_ConsumerName, 						text_ConsumerPhone, 						text_ConsumerCity, 						bigint_ConsumerRegion, 						tinyint_ConsumerStatus					) 					VALUES (						\"".$consumeremail."\", 						\"".$consumerfullname."\", 						\"".$consumercell."\", 						\"".$city_town."\", 						".$region.", 						0					) 					ON DUPLICATE KEY UPDATE 						`text_ConsumerE-Mail` = 							\"".$consumeremail."\", 						text_ConsumerName = 							\"".$consumerfullname."\", 						text_ConsumerPhone = 							\"".$consumercell."\", 						text_ConsumerCity = 							\"".$city_town."\", 						bigint_ConsumerRegion = 							".$region.", 						tinyint_ConsumerStatus = 0;";		$tresult = mysql_query_errors($csql, $conn , __FILE__ , __LINE__ );		$userid = mysql_insert_id($conn);

how would i be able to repair this? it should return the id of the updated record if a record was not inserted.

[justsomeguy]

it should return the id of the updated record if a record was not inserted.

That's what the documentation says it does, so it sounds like your insert is not finding a duplicate row (or else it would return the ID that it updated).

[Greywacke]

thing is i have repeated this on a private server using mysql version 5.1.41 and it just returns 0 as the id when updated. it would seem mysql has taken the return of the value of the autoincrement field away... any ideas how to work around this? if it is updated, i need to select the id. on the private test server (5.1.41) mysql_insert_id returns 0 if updating. if on the live server (5.0.91) it returns the LAST_INSERT_ID() function - which contains the next insert id to be inserted if nothing was inserted. this really breaks my mood somewhat... :/ any ideas for solutions?ps: it does not return the id that it updated, or 0 as the documentation states. it returns the LAST_INSERT_ID() value from mysql instead, and this sometimes contains id's that do not exist in the table yet (when updating instead of inserting).

[Greywacke]

an option could be: to add a mysql_update_id function, to replace the use of mysql_insert_id with the ON DUPLICATE KEY UPDATE queries... this function can then first try to get the mysql_insert_id, check that it exists in the database - if not, select the updated row's id by email (the unique field in this consumers table).something like this as replacement:

$userid = mysql_update_id($conn, $mail);

will post an update of the suggested function. it is imperative to me, and i am sure not just me - to be able to get the id of the updated record if updating instead of inserting - otherwise retrieve the new insert id.

[Greywacke]

well unfortunately this section of the project has been parked for now. i will get back with the function when i have done it

[Greywacke]

well unfortunately this section of the project has been parked for now. i will get back with the function when i have done it