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
– stivlo, Commented Oct 12, 2011 at 2:53
You need to do error checking on mysql_query(). if (!$result) echo mysql_error() — Michael Berkowski
– Michael Berkowski, Commented Oct 12, 2011 at 2:54
mysql_error() will tell you exactly why the query failed (which is why mysql_num_rows() is complaining. — Michael Berkowski
– Michael Berkowski, Commented Oct 12, 2011 at 2: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
– Dhiraj, Commented Oct 12, 2011 at 2:54
possible duplicate of mysql_num_rows(): supplied argument is not a valid MySQL result resource — Michael Berkowski
– Michael Berkowski, Commented Oct 12, 2011 at 2:56

stivlo · Accepted Answer · 2011-10-12 04:57:39Z

1

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)  . "';";

edited Oct 12, 2011 at 4:57

answered Oct 12, 2011 at 2:54

stivlo

85.9k35 gold badges148 silver badges200 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

mpen Over a year ago

SELECT 1 ... LIMIT 1 would be quicker, no? No need to count the number of users, just need to know that one exists.

Joseph · Accepted Answer · 2011-10-12 02:58:51Z

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, 2011 at 2:58

Joseph

2073 silver badges10 bronze badges

2 Comments

comu Over a year ago

Never heard of it. Ill take a look

mpen Over a year ago

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?

phpuser · Accepted Answer · 2011-10-12 03:38:46Z

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, 2011 at 3:38

phpuser

314 bronze badges

1 Comment

comu Over a year ago

no because username is already the name a variable with $_POST content

Collectives™ on Stack Overflow

PHP mysql_num_rows() Returns error

3 Answers 3

1 Comment

2 Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related