mysql_num_rows without a query or is there an alternative?

[perky416]

Hi Guys

 

Im trying to cut down on my mysql_queries, I don't think its possible but I was just wondering if there is a way I can use mysql_num_rows without running a query, or if there is an alternative way to check if an entry already exists in a database?

 

Im currently using something like the following:

 

if ($username){
	if (mysql_num_rows(mysql_query("SELECT username FROM users WHERE username='$username'")) > 0){
		$error[] = "The username you have entered is already in use<br />";
	}
}

 

Thanks

[mgoodman]

You are going to have to run a query no matter what.

 

If you really wanted to make it more efficient then you could consider using a count query instead of using mysql_num_rows:

$query ="SELECT COUNT(*) as count FROM users WHERE username='$username'";
$result = mysql_fetch_assoc(mysql_query($query));

if ($result['count'] > 0) {
    // username is taken
}

 

Since that query only returns a number, it's less information to transmit than if you return the entire row and then perform a count after the fact.

[Pikachu2000]

If all you need is to return the number of matching records, a SELECT COUNT() query against an indexed field is much more efficient. And no, you can't do it without executing a query.

[perky416]

Hi guys thanks for the replys,

 

Thanks for the info! I didnt know about the count. Nice to learn something new.

 

[perky416]

Hi guys,

I have a quick follow on question.

 

Im now using the following code:

 

$username_count = mysql_fetch_assoc(mysql_query("SELECT COUNT(*) as count FROM users WHERE username='$username'"));
$username_count['count'] > 0 ? $error[] = "Username already exists!<br />" : null;

 

When the database is empty and i try to run the search im getting the following error:

Warning: mysql_fetch_assoc(): supplied argument is not a valid MySQL result resource

 

How would i get around this?

 

Thanks

[PFMaBiSmAd]

The way of getting around an error like that is to write proper code that tests if the query executed without an error before attempting to access the result from the query. You should never nest function calls where an inner function call can fail with an error because you cannot use any error checking, error reporting/logging, and error recovery logic to get your application to behave in an expected manor when an error occurs.

 

 

[Maq]

perky, read through this link for some ways to properly handle exceptions and errors:

http://www.phpfreaks.com/blog/or-die-must-die