Trouble in mysql_num_rows() in php

[Peter2]

Hi; PHP experts:
In the last part of my project I run into a trouble in using mysql_num_rows() function, I want to use this function to check if the userID entered by the new user is already taken in the database. Here is the code:

$user_name = "root";
$pass_word = "";
$database = "memberbook";
$server = "127.0.0.1";

$db_handle = mysql_connect($server, $user_name, $pass_word);
$db_found = mysql_select_db($database, $db_handle);

if ($db_found) {

$num_rows = 0;
//====================================================================
// CHECK THAT THE USERID IS NOT TAKEN from the member table
//====================================================================

$SQL = "SELECT * FROM memberbook WHERE userid = $userid";
$result = mysql_query($SQL);
$num_rows = mysql_num_rows($result);
}

The message is:

Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource in C:\Program Files\PHP\www\signup.php on line 143

I can not see anything wrong in the codes, but it never works.

Your help is highly appreciated.

Peter

 

[KLin]

http://us.php.net/mysql_num_rows

When looking at the example in the link above, it looks like you have to reference the sql connection object within the mysql_query function. Maybe that's the issue?

 

[jjones]

Off the top of my head, I'd say you need to enclose the userid variable in single quotes like this:

$SQL = "SELECT * FROM memberbook WHERE userid = '$userid'";

also, why not just use an if statement right on num_rows like so:

if (mysql_num_rows($result) !== 0) {
echo 'error: user ID is already in use';
} else {
//insert user ID into DB
}

Edit: oops, lol, I was doing things a little backwards.

 

[Hyperblaze]

Just as a suggestion, try avoiding using "SELECT *" when making a query to the database.

It causes unnecassary load on the database and making page load longer (if it is two things you care about)

 

[KLin]

Originally posted by: Hyperblaze
Just as a suggestion, try avoiding using "SELECT *" when making a query to the database.

It causes unnecassary load on the database and making page load longer (if it is two things you care about)

I agree. I would have just done a select count(UserID) As UserCount from memberbook and evaluated if the the UserCount value was greater > 0

 

[troytime]

Originally posted by: KLin

Originally posted by: Hyperblaze
Just as a suggestion, try avoiding using "SELECT *" when making a query to the database.

It causes unnecassary load on the database and making page load longer (if it is two things you care about)

I agree. I would have just done a select count(UserID) As UserCount from memberbook and evaluated if the the UserCount value was greater > 0

why bother with getting data from the resource if the code is only there to check if that ID exists.

$result = mysql_query($SQL, $db_handle);
if(mysql_num_rows($result)) // will be true if query found records, false if not

 

[drebo]

When your WHERE condition is looking at a text column, your string literal always needs to be in quotations. Where PHP can take $variable and recognize that it's a string, integer, whatever, this is not the case when passing a PHP variable to MySQL through a query string. It must be enclosed within single quotes. Also, when dealing with text, I've always found the LIKE comparison to be more precise for a lot of things.

The reason your mysql_num_rows() is failing is because your query is not properly formatted and thus MySQL is throwing an error and not returning anything to your $result variable. If $result is null, mysql_num_rows($result) will error. Append "or die(mysql_error());" to your mysql_query() call to see the error.

However, changing your query to "SELECT userid FROM memberbood WHERE userid LIKE '${userid}';" should get rid of the error.

 

[Peter2]

Thank you all for the great help.

From your reply, I found two mistakes:

1. In the $SQL statement, the variable $userid should be in single quotes as
jjones indicated.
2. As indicated by Klin, you have to reference the sql connection object within the mysql_query function.

Now my codes work.

But I still have a question related to this topic pointed by Hyperblaze, Klin, troytime,.drebo for unnecessary loading the data if only checking the userid exist.

When the "$result = mysql_query($SQL, $db_handle);" is executed, it has to go through all records, no matter you want return data or just count the number of records, if there is multi million records, this will take long time even the record is found in the first checking. Is there any way to stop the checking when the first record is found?

Thanks.

Peter

 

[Crusty]

Depending on the situation you could make it a primary key with a unique contraint. You can also put a LIMIT 1 at then end of your query, that will force it to return only one row.

 

[drebo]

Originally posted by: Peter2
But I still have a question related to this topic pointed by Hyperblaze, Klin, troytime,.drebo for unnecessary loading the data if only checking the userid exist.

When the "$result = mysql_query($SQL, $db_handle);" is executed, it has to go through all records, no matter you want return data or just count the number of records, if there is multi million records, this will take long time even the record is found in the first checking. Is there any way to stop the checking when the first record is found?

Thanks.

Peter

That's not necessarily true. Simply executing the query within mysql_query() does NOT load all of the results. The actual data does not get pulled out until you request it with mysql_fetch_assoc() or related functions. However, in this case, your query should return only one result...so even if you were getting the data, it still isn't going to be very expensive in terms of processing power. Disk access is another thing, but at your scope it's not relevant.

You're making a typical assumption here that's false. You're assuming that when you call mysql_query(), you're loading all of the data into your script and then PHP is doing the searching. That's not quite how it works. When you call mysql_query(), MySQL is actually doing all of the processing to find the results you're looking for, and then it passes a simple identifier back to PHP which tells MySQL how to find the data. No data actually goes between the two applications until you request it. That's what makes SQL so fast compared to an XML-based or C-Tree type database.

 

[troytime]

Originally posted by: drebo

Originally posted by: Peter2
But I still have a question related to this topic pointed by Hyperblaze, Klin, troytime,.drebo for unnecessary loading the data if only checking the userid exist.

When the "$result = mysql_query($SQL, $db_handle);" is executed, it has to go through all records, no matter you want return data or just count the number of records, if there is multi million records, this will take long time even the record is found in the first checking. Is there any way to stop the checking when the first record is found?

Thanks.

Peter

That's not necessarily true. Simply executing the query within mysql_query() does NOT load all of the results. The actual data does not get pulled out until you request it with mysql_fetch_assoc() or related functions. However, in this case, your query should return only one result...so even if you were getting the data, it still isn't going to be very expensive in terms of processing power. Disk access is another thing, but at your scope it's not relevant.

You're making a typical assumption here that's false. You're assuming that when you call mysql_query(), you're loading all of the data into your script and then PHP is doing the searching. That's not quite how it works. When you call mysql_query(), MySQL is actually doing all of the processing to find the results you're looking for, and then it passes a simple identifier back to PHP which tells MySQL how to find the data. No data actually goes between the two applications until you request it. That's what makes SQL so fast compared to an XML-based or C-Tree type database.

he's referring to the mysql query pulling multiple rows instead of a single row.
with a LIMIT 1 on the query, it will stop as soon as it finds a row that meets the WHERE condition. without the limit, the query will return all the rows that meet the WHERE condition.
a single row pull will be a lot faster than one getting all the rows

 

[drebo]

Not when the only access to the result is mysql_num_rows(). He's not actually accessing the data. Which is completely besides the point in this case, as there will only ever be one record.

 

[Crusty]

Originally posted by: drebo
Not when the only access to the result is mysql_num_rows(). He's not actually accessing the data. Which is completely besides the point in this case, as there will only ever be one record.

That's a bad assumption. What if the code allows people to sign up multiple times? Never assume anything when dealing with users or input!

 

[troytime]

Originally posted by: drebo
Not when the only access to the result is mysql_num_rows(). He's not actually accessing the data. Which is completely besides the point in this case, as there will only ever be one record.

that has nothing to do with the mysql load from the actual query
if there query is pulling multiple rows it will use more resources than if it were pulling a single row

the comparison is not between mysql_num_rows and a mysql_fetch_assoc and the data set

 

[jjones]

Hmm, I was just doing some interesting testing. Using the MySQL query browser on an InnoDB table with about 80,000 rows (not much to test with I know, but at least better than a few thousand), unless the query was pulling a bunch of rows, it was generally more efficient (less time) not limiting the query to one row. Could just be a quirk of this particular table I was testing, but it shows it's always good to analyze your queries.

It may be best not to specify a limit especially since the userid is supposed to be unique anyway and it would just be pulling a single row if found. I'd sure like a million row table to test with though.

 

[troytime]

Originally posted by: jjones
Hmm, I was just doing some interesting testing. Using the MySQL query browser on an InnoDB table with about 80,000 rows (not much to test with I know, but at least better than a few thousand), unless the query was pulling a bunch of rows, it was generally more efficient (less time) not limiting the query to one row. Could just be a quirk of this particular table I was testing, but it shows it's always good to analyze your queries.

It may be best not to specify a limit especially since the userid is supposed to be unique anyway and it would just be pulling a single row if found. I'd sure like a million row table to test with though.

was the column in your where indexed?

i can test with some multi million row tables tomorrow, on both indexed columns and unindexed

 

[jjones]

Yeah, it was indexed and then I ignored the index too and the table scan was faster than using the index. I haven't been looking too much at performance in my projects because I work with pretty small DBs so it's never been an issue. Still, it's interesting to look at this stuff and I remember reading somewhere that using indexes, in some cases, may not always be fastest. But at this point in what I'm doing, it really doesn't matter so I'll stick with convention for now.

 

[drebo]

Interestingly enough, we were all wrong.

In a 30K row table, it was ALWAYS faster to let MySQL do the counting and then read the result than it was to use mysql_num_rows() or read the result with mysql_fetch_assoc(). In fact, mysql_num_rows() was always the slowest.

This was the case whether I knew my query would only return one row (query by primary key) or whether it returned several thousand rows.

So, I guess using "SELECT COUNT(pk) as NumRows FROM table WHERE condition = test" is the best way to do it afterall. I'll be damned.

 

[hans007]

yeah generally in my experience, letting the database do the work , instead of the scripting / programming language is almost always faster.

keep in mind people spend untold hours optimizing these things for stuff like that.