Jump to content

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


Greywacke
 Share

Recommended Posts

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

Edited by Pierre 'Greywacke' du Toit
Link to comment
Share on other sites

  • 2 weeks later...

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

Edited by Pierre 'Greywacke' du Toit
Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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

Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share

×
×
  • Create New...