Get Auto Inc ID on Duplicate

[stevil]

I'm working on a fairly large project at the moment and need to limit queries to a minimum. I'm looking for a way to insert a new col, but if the col has a duplicate in it then return the auto inc/key field for that table.

 

	//Gets Date ID or creates one if need be
mysql_query("INSERT INTO dated(date_date) VALUES('".date("Y-m-d")."') ON DUPLICATE KEY UPDATE date_id = date_id;");
$DateID = mysql_insert_id()-(mysql_affected_rows()>1?mysql_affected_rows:0);

 

This worked until the table was altered and the insert_id was out.

 

	$sql="SELECT date_id FROM dated WHERE date_date='".date("Y-m-d")."'";
if($DateCHK	=mysql_fetch_array(mysql_query($sql))){
	$DateID	=$DateCHK['date_id'];
}else{
	$sql	="INSERT INTO dated(date_date) VALUES('".date("Y-m-d")."')";
	$DateCHK=mysql_query($sql);
	$DateID	=mysql_insert_id();
}

 

This also worked, but I started getting errors because we were getting multiple dates showing up in the database. So basically two or more people were checking if the date exisited at midnight at the same time... I know it sounds unlikely, but it's happen on several occassions while in testing.

 

I also find the above method is slow, and runs more queries than needed.

 

I've also looked into doing a REPLACE, this is REALLY slow though as it has to delete the record each time someone uses it. As the query is going to be run well over a million times a day, speed is of high importance.

 

Can anyone see a possible way to do this which will be fast an accurate?

[fenway]

Why not use the first part, but in two parts?

[stevil]

Why not use the first part, but in two parts?

 

Not sure if I get exactly what you mean sorry? Doing the ON DUPLICATE KEY UPDATE method in 2 parts?

 

I've come up with a solution that would be better as far as errors go. I can try and insert the row first, if the keys are duplicate it'll reject it meaning 0 rows are affected meaning I knwo the row is already created. I assume this is pretty slow though aswell.

 

Is there a way maybe to do a SELECT statement with an IF condition on it that does an INSERT ? I highly doubt that it's possible =( but someone might know something along those lines.

[fenway]

Well, regardless of whether or not you insert, you can always find the matching value.