PHP mysql_num_rows() Returns error

Question

I have a PHP login script. This is the part where the person can create a new user. My issue is I want to check if the user exists, and if the username does not exist the the table, than create the new user. However, if the user does exist, I want it to return an error in a session variable. Here is the code I have right now. This doesn't include my DB connections, but I know they do work. Its num_rows() that is being written as an error in the error_log file. Here is the code:

$username = mysql_real_escape_string($username);
$query = "SELECT * FROM users WHERE username = '$username';";
$result = mysql_query($query,$conn);
if(mysql_num_rows($result)>0) //user exists
{
    header('Location: index.php');
    $_SESSION['reg_error']='User already exists';
    die();
}
else
{
$query = "INSERT INTO users ( username, password, salt )
        VALUES ( '$username' , '$hash' , '$salt' );";
mysql_query($query);
mysql_close();
header('Location: index.php');

The error it is giving me is

mysql_num_rows(): supplied argument is not a valid MySQL result resource in [dirctory name]

To check whether a row exist or not, you can use SELECT COUNT(*) — stivlo, Oct 12 '11 at 02:53
You need to do error checking on `mysql_query()`. `if (!$result) echo mysql_error()` — Michael Berkowski, Oct 12 '11 at 02:54
`mysql_error()` will tell you exactly why the query failed (which is why `mysql_num_rows()` is complaining. — Michael Berkowski, Oct 12 '11 at 02:54
a few suggestions, you do not need a ";" inside the query and are you sure you are able to connect to the db at all ? — Dhiraj, Oct 12 '11 at 02:54
possible duplicate of [mysql_num_rows(): supplied argument is not a valid MySQL result resource](http://stackoverflow.com/questions/3698740/mysql-num-rows-supplied-argument-is-not-a-valid-mysql-result-resource) — Michael Berkowski, Oct 12 '11 at 02:56
So would you guys recommend using select count(*) instead of num_rows()? — comu, Oct 12 '11 at 03:00
@JonahAllibone: No... I'd recommend `SELECT 1...LIMIT 1` if you just want to check if something exists. — mpen, Oct 12 '11 at 03:30

stivlo · Answer 1 · 2011-10-12T04:57:39.060

mysql_num_rows()

Retrieves the number of rows from a result set. This command is only valid for statements like SELECT or SHOW that return an actual result set. To retrieve the number of rows affected by a INSERT, UPDATE, REPLACE or DELETE query, use mysql_affected_rows().

Instead of doing SELECT * and then mysql_num_rows(), you can do a SELECT COUNT(*) and then retrieve the number of rows, by fetching the field (that should be 0 or 1). SELECT COUNT will always return a result (provided that the query syntax is correct of course).

Also, change the query:

$query = "SELECT * FROM users WHERE username = '$username';";

into

$query = "SELECT * FROM users WHERE username = '" 
    . mysql_real_escape_string($username)  . "';";

`SELECT 1 ... LIMIT 1` would be quicker, no? No need to count the number of users, just need to know that one exists. — mpen, Oct 12 '11 at 03:31

score 0 · Answer 2 · answered Oct 12 '11 at 02:58

0

Just out of curiosity, have you ever heard of upserts? I.E., "insert on duplicate key". They'd be useful to you in this situation, at least if your username column is a unique key.

http://dev.mysql.com/doc/refman/5.0/en/insert-on-duplicate.html

answered Oct 12 '11 at 02:58

Joseph

207
3
10

Never heard of it. Ill take a look – comu Oct 12 '11 at 03:00
I don't think he wants to perform an update "on duplicate" though... I think `INSERT IGNORE` would be better suited for the task, no? – mpen Oct 12 '11 at 03:33

score 0 · Answer 3 · answered Oct 12 '11 at 03:38

0

 $username = mysql_real_escape_string($username);

i think you have to replace the above to

$username = mysql_real_escape_string($_POST[$username]);

answered Oct 12 '11 at 03:38

phpuser

31
4

no because username is already the name a variable with $_POST content – comu Oct 12 '11 at 21:27

PHP mysql_num_rows() Returns error

3 Answers3