[RESOLVED] INSERT ... UPDATE ... REPLACE ... ???

steamPunk

I have quite a simple problem but i can't find a simple answer

I have a table with 3 cols : 'client_id', 'doc_id', 'dl_date'

if a client downloads a document a new row is inserted - but if the client re-downloads the same doc the insert has to fail if an identical 'client_id'/'doc_id' pair is already present

i don't want to "ON DUPLICATE KEY UPDATE" the row because I don't want the original 'dl_date' to change and i can't use REPLACE because the cols can't be UNIQUE and that would update 'dl_date' too

how might I do this with just 1 query ?

thanks

NogDog

Make sure the DB table has a 2-column unique index on the client_id and doc_id fields. Then just do a plain insert query. If it fails, check to see if it is a duplicate key error. Assuming MySQL, it could be something like:

$result = mysql_query($your_query_string);
if($result == false)
{
   if(mysql_errno() == 1062)
   {
      // do whatever you want to do (or not do) if a duplicate entry
   }
   else
   {
      // something else happened, so log error and if desired notify user something went wrong
   }
}

steamPunk

great !

i didn't know that 2-column unique keys existed

that's a neat simple solution - thanks !