User Exist & Mysql Error Test

[kiss]

I contradicted with someone what is the right code to insert mysql_error and check if user exist or not.Here's 3 examples:
Number 1:

$sql = "SELECT user, email, timereg FROM test WHERE user='".$user."'";
$items = @mysql_query($sql) or die(mysql_error());
if(@mysql_num_rows($items)>0)
{
while($item = @mysql_fetch_array($items))
{
echo "Hi ".$item['user']."";
}
}else{
echo "User not exist.";
} 

Number 2:

$sql = "SELECT user, email, timereg FROM test WHERE user='".$user."'";
$items = @mysql_query($sql) or die(mysql_error());
while($item = @mysql_fetch_array($items))
{
echo "Hi ".$item['user']."";
} 

Number 3:

$sql = "SELECT user, email, timereg FROM test WHERE user='".$user."'";
$items = @mysql_query($sql) or die(mysql_error());
$item = @mysql_fetch_array($items);
echo "Hi ".$item['user'].""; 

What you think is correct or if you have completions,please..

[syrus]

none of the above

$q = mysql_query("SELECT id FROM table WHERE email ='".$email."' LIMIT 1"); if(mysql_num_rows($q))
{
 echo ' user already exists'
}
else
{ 
//continue
}
 /* @query returned will be 0 or 1 i.e bool*/ 

[djlee]

There is no RIGHT WAY, you need to take into acct what the page does after the user is found. If its a simple username check say in the signup form to make sure a user isnt trying to signup with a taken username id use something like

if((int)mysql_result(mysql_query("SELECT COUNT(id) FROM users WHERE username='test'"),0) > 0)
 die("username taken");

or an inline echo of the above would look like this (i use somethin similar in my ajax signup form)

echo (int)mysql_result(mysql_query("SELECT COUNT(id) FROM users WHERE username='test'"),0) > 0 ? "username taken" : "username available";

Howeer if it was say on a user profile page, while the above would work for checking user exists, its not ideal as you then have to make a second query to get the users info to display their profile, so on such a page i would use this instead

$res = mysql_query("SELECT * FROM users WHERE username='test' LIMIT 1");
if(mysql_num_rows($res) == 0)
 die("user unknown");
else 
 $user = mysql_fetch_assoc($res);

similarly on a page where a loop is required say like a search index page you could substitue the $user = xxxx in the above else clause for the while loop display

There is no right or wrong way to always do it, you have to formulate your queries based on what you will do if the resultset returns true/false, in an event where you simply state the resultset is true/false the first code i posted is a good one to use, but if you want to display the resultset info if true such as a details page then the second query (3rd codebox) is better as you only need to use one query

[kei_ki7]

    $sql = mysql_fetch_array(mysql_query("SELECT COUNT(*) FROM test WHERE user='".$user."'"));
    if ($sql[0]>0)
      {
        echo "User already exist";
      }else{
        echo "Hi $user";
      }