So what IS the best way to check if a row exists?

Question

If your only goal is to check if a row exists in php (true or false), what is the best way to do it?

Option 1?

$result = mysql_query("SELECT * FROM users WHERE id = '1'");
$num_rows = mysql_num_rows($result);

if ($num_rows == 1) 
    // one user, like it should be.

else 
  // do something else

Option 2?

$query = mysql_query("select count(1) from users where id = 1")

if (mysql_result($query, 0) == 1)
    // one user, like it should be.

else 
  // do something else

Option 3?

$query = mysql_query("something like SELECT EXISTS( SELECT */1/COUNT(*)/etc. ...)")

if (mysql_result($query, 0) == 1)
    // one user, like it should be.

else 
  // do something else

Beter option 4?

you name it.

Subquestions

• COUNT(*), COUNT(1) or COUNT(id)?

Answer 1

Option 3 is the fastest way to check if a row exists if you are using MySQL:

$query = mysql_query("SELECT EXISTS(SELECT 1 FROM users WHERE id = 1)")

if (mysql_result($query, 0) == 1)
    // one user, like it should be.

else 
  // do something else

Answer 2

The EXISTS is faster then SELECT COUNT(*) because the subquery will stop searching when it finds one row. It won't have to find them all and count them. It will return either 0 or 1:

SELECT EXISTS 
       ( SELECT * FROM ... )

Answer 3

I think the question refers more the code itself then the time involved, so using his query:

$result = mysql_query("SELECT * FROM users WHERE id = '1'");
//if result not returned(false) from Mysql return False Else True
//This is just example and you can do anything you need in side the if()
if(!$result) {
//or return some error or redirect to another piece of code
return FALSE;
} else {
//or do some other php/mysql magic
//if there is a result you have the row to work with of needed
return TRUE;
}

mysql_query
...excerpt from PHP manual Return Values

For SELECT, SHOW, DESCRIBE, EXPLAIN and other statements returning resultset, mysql_query() returns a resource on success, or FALSE on error.

For other type of SQL statements, INSERT, UPDATE, DELETE, DROP, etc, mysql_query() returns TRUE on success or FALSE on error.