Mysql, best way to check if record exists and more

Databases
1

I accidentally posted this in php and instead of bumping it there I’m hoping its OK to repost here.

The more I look for answers the more variations I get and am hoping to get guidance here on a few questions.

First is to see if a record exists, I have been using mysql_numrows() and this may not be the best approach. I have an alternative posted below.


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

or very similar

SELECT COUNT(*) FROM users WHERE   username = 'loren';

Are either one of these better? Also why do they use * instead of something like “id”? I would think id would be faster, thats why I ask.

This brings up the issue of insert or update if a record exists and found this.


INSERT INTO users SET username = 'loren', text='hello'
ON DUPLICATE KEY UPDATE username = 'loren'

This would work great for a single entry however I have a table that looks like this.

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

For the above I would need to see if a record exists and then insert or update OR should I just do a delete where name = ‘loren’ and then do an insert?

Then there is this,


REPLACE INTO users SET username = 'loren', text='hello'

I have never seen REPLACE INTO before and was wondering if there was a reason I’ve never seen it.

I suppose they all have their place but I have no idea when to use what and in what circumstances.

Right now I thing I am doing things totally inefficienty and hope some here can help me improve my skills.

I appreciate your guidance and sorry about this being a bunch of questions but to me they all relate.

Thanks
Loren

2

INSERT … ON DUPLICATE KEY is usually better than REPLACE.

however, i can’t give you better advice because your queries don’t match your sample data.

3

he sample table is for the radius server and is the unusual one.

The users table is just straightforward id name pass text etc…

The first question, is mysql_numrows() vs one of the other two examples be better to see if something exists, as in


if (mysql_numrows($q) == 0){
// do this
}
else{
// do that
}

would one of the other COUNT options be better?

Thanks

4

I rather run separate queries and leave the domain to make that decision.


$sql = 'SELECT COUNT(*) AS total FROM users WHERE username = \\'loren\\';';
$query = mysql_query($sql);
$row = mysql_fetch_assoc($query);
$total = $row['total'];

if($total==1) {

} else {

}
5

Thanks oddz,

That reply is exactly what I was looking for.
SELECT COUNT from here on out.

Cheers
Loren