Best way to check if record exists.

[lorenw]

I have been using mysql_numrows($sql) and checking the count to do an insert or update.

A while back r937 implied it was the most inefficient way to do things.

Two things come up on a regular basis, insert or update and check if a record exists. So to get away from mysql_numrows($sql) to insert or update, should I be using,

SELECT 
  IF(COUNT(*) > 0, 'OK', 'Failed') as Status 
FROM 
  users
WHERE 
  username = 'loren' ; 

or

INSERT INTO users SET username = 'loren', text='hello' ;

ON DUPLICATE KEY UPDATE Status = 'loren' 

The second option sound most reasonable. I am also working with free radius where loren can have many attributes and each attribute is in its own row.

name | attribute
loren | max_session_time
loren | max_bandwidth_down
loren | max_bandwidth_up
etc...

In this case is the first option recommended?

The second question is to just see if a record exists, would the first option be the best or would SELECT DISTINCT be better?

I'm still Googling and found (just copied and pasted)

REPLACE INTO `transcripts`
SET `ensembl_transcript_id` = 'ENSORGT00000000001',
`transcript_chrom_start` = 12345,
`transcript_chrom_end` = 12678; 

Along these lines, a third question, when deleteing a record should you check to see if it exists or just run the delete query.

Many thanks, there is a lot of info out there and many ways of doing things so I hope someone can set me down the right path.

Edit:


Oops, thought I was in the mysql forum, posted in the wrong place.