[Resolved] mysql_num_rows help

JoelyUK · Jul 14, 2003

I've just wrote a script for users signup, but everytime I go to check the database to see if the username exists, I get this error:

Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource in /home/site/public_html/members/register/confirm.php on line 81

Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource in /home/site/public_html/members/register/confirm.php on line 92

The offending parts of the script are:

78 mysql_select_db($mysqldatabase, $mysqlconnection);
79 $query_username = "SELECT id FROM users WHERE username = '$username'";
80 $doquery_username = mysql_query($query_username); 
81 $numrows_username = mysql_num_rows($doquery_username); 
81 if($numrows_username > 0){ 
82 $regerror .= " - That username exists. Please choose another";
83 $username = "";
84 fatalerror();
85 exit;
}

The other bit is the same, but just checking for a registered e-mail.

Strange thing is - if I refresh the page - it works fine. I can't work it out

There are no other MySQL queries before this one either, other than the connection info:

$mysqlhostname = "localhost";
$mysqldatabase = "xxxxxxx";
$mysqlusername = "xxxxxxx";
$mysqlpassword = "xxxxxxx";
$mysqlconnection = mysql_pconnect($mysqlhostname, $mysqlusername, $mysqlpassword) or die(mysql_error());

Help?

OhLordy · Jul 14, 2003

change this line

$doquery_username = mysql_query($query_username);

to

$doquery_username = mysql_query($query_username) or die(mysql_error());

This will print the mysql_query which is causing the query to throw.

HTH
Rob

cassius · Jul 14, 2003

As far as I can see, you could use

mysql_affected_rows()

to do the same work. It will return the same result as

mysql_num_rows()

in your example.

You don't need to pass an argument to

mysql_affected_rows()

, it will return the number of affected rows from your last

mysql_query()

DeltaRho2K · Jul 14, 2003

From php Manual:

mysql_affected_rows() returns the number of rows affected by the last INSERT, UPDATE or DELETE query associated with link_identifier. If the link identifier isn't specified, the last link opened by mysql_connect() is assumed

I think (but I may be wrong), that you have to use the mysql_num_rows() function, because the SELECT action doesn't actually "affect" any rows.

cassius · Jul 14, 2003

Your probably right DeltaRho2K, I just didn't take a good enough look at the question...

this should work(it does for me...):

$sql = "SELECT username FROM Users WHERE username='$username'";	//search for username
      	$result = mysql_query($sql) or die("Couldn't execute query.");
	      $num = mysql_num_rows($result);
      	  if ($num == 1){  // username found
//check for password etc...
 //login...or print errormessage(s)..

you could of course change the if-sentence to suite your needs ( >= <= == !=)

DeltaRho2K · Jul 14, 2003

Basically, the error is saying there is something wrong with this:
$doquery_username

Then going back in the code, this means there is something wrong with this:
$query_username

Which means there is something wrong with this:
"SELECT id FROM users WHERE username = '$username'";

So, basically, it means either
a) your SQL is wrong. Make sure your $username variable is being passed by the previous form properly. (Check register globals. If OFF, then this could be your problem. This has been discussed many times here, so I won't go into it.

OR

b) your database connection is failing.

Jacob

JoelyUK · Jul 14, 2003

I'm now getting the error message:

Query was empty

When I added the 'or die();' parts::

// Check to see if the username exists

mysql_select_db($mysqldatabase, $mysqlconnection);
$query_username = "SELECT id FROM users WHERE username = '$username'";
$doquery_username = mysql_query($query_username) or die(mysql_error());
$numrows_username = mysql_num_rows($doquery_username) or die(mysql_error());
if($numrows_username > 0){ 
$regerror .= " - That username exists. Please choose another";
$username = "";
fatalerror();
exit;
}

This really doesn't sound right :-(

stolzyboy · Jul 14, 2003

put this

echo $query_username;

right after

$query_username = "SELECT id FROM users WHERE username = '$username'";

sounds like $username isn't coming thru, you may have to use

<?
$query_username = "SELECT id FROM users WHERE username = '" . $_POST['username'] . "'";
?>

JoelyUK · Jul 14, 2003

It's coming through fine:

SELECT id FROM users WHERE username='testuser'
Query was empty

This has been annoying me for several hours now

JoelyUK · Jul 14, 2003

Interestingly, if I refresh the page, it works fine and I get printed before the HTML of the page:

SELECT id FROM users WHERE username='joely'
Resource id #2

from the PHP:

echo $query_username."<br>"; 
$doquery_username = mysql_query($query_username) or die(mysql_error());
echo $doquery_username."<br>";
$numrows_username = mysql_num_rows($doquery_username) or die(mysql_error());

DeltaRho2K · Jul 14, 2003

So are things working or not? I got lost with what the situation is now with your last two posts ;-) (Sorry, I have been watching this thread closely)

JoelyUK · Jul 14, 2003

Sorry, I'll reclarify

On first submit of form, no, it does not work and throws up the error "Query is empty"

If I refresh the page in Internet Explorer, and resubmit the data (via the IE prompt), the error is not thrown up, and everything is processed as it should.

Here is the whole PHP script before the MySQL query.

Excuse my comments. And the spelling. And the bad coding. And the lot, really

<?php 

// Start session
session_start();

include("/home/site/public_html/resources/includes/functions.php");

// Assign variables from form
$username = $_POST['username'];
$email = $_POST['email'];
$emailconfirm = $_POST['emailconfirm'];
$password = $_POST['password'];
$passwordconfirm = $_POST['passwordconfirm'];
$dobday = $_POST['dobday'];
$dobmonth = $_POST['dobmonth'];
$dobyear = $_POST['dobyear'];
$nickname = $_POST['nickname'];
$regerror = ""; // Always blank the error var, because I'm silly and forget that.

// register them variables
session_register("username");
session_register("email");
session_register("emailconfirm");
session_register("password");
session_register("passwordconfirm");
session_register("dobday");
session_register("dobmonth");
session_register("dobyear");
session_register("nickname");
session_register("regerror");

// this is for us to call in the event we need to declare a 'fatal' error which must be reported back straight away
function fatalerror() {
header("Location: [edited because of silly forum autolink]"); 
exit;
}

// Check to see if the fields are empty
if($username == "") { $regerror .= " - The username is empty <br>"; }
if($email == "") { $regerror .= " - The email field is empty <br>"; }
if($emailconfirm == "") { $regerror .= " - The confirm e-mail is empty <br>"; }
if($password == "") { $regerror .= " - The password field is empty <br>"; }
if($passwordconfirm == "") { $regerror .= " - The confirm password is empty <br>"; }
if($dobday == "") { $regerror .= " - The date of birth day field is empty <br>"; }
if($dobmonth == "") { $regerror .= " - The date of birth month field is empty <br>"; }
if($dobyear == "") { $regerror .= " - The date of birth year field is empty <br>"; }

// force an error check - if a problem exists, go back to the form
if($regerror) { fatalerror(); exit; }

// check username and other fields for alpha numeracy thingy ma jiggy

if(eregi("[^a-z_0-9]",$username))
{
$regerror .= " - Your username contains invalid characters. Use only A-z and 0-9! <br>"; 
fatalerror();
exit;
}

if(eregi("[^a-z_0-9]",$password))
{
$regerror .= " - Your password contains invalid characters. Use only A-z and 0-9! <br>"; 
fatalerror();
exit;
}

if(eregi("[^a-z_0-9]",$nickname))
{
$regerror .= " - Your nickname contains invalid characters. Use only A-z and 0-9! <br>"; 
fatalerror();
exit;
}

$nickname = $username;

// Check to see if the username exists

mysql_select_db($mysqldatabase, $mysqlconnection);
$query_username = "SELECT id FROM users WHERE username='$username'";
echo $query_username."<br>"; 
$doquery_username = mysql_query($query_username) or die(mysql_error());
echo $doquery_username."<br>";
$numrows_username = mysql_num_rows($doquery_username) or die(mysql_error());
if($numrows_username > 0){ 
$regerror .= " - That username exists. Please choose another";
$username = "";
fatalerror();
exit;
}

[..]

DeltaRho2K · Jul 14, 2003

I believe after you register your session variables, you still have to redefine the session back into variables...

session_register('username', 'email', 'emailconfirm', 'password', 'passwordconfirm', 'dobday', 'dobmonth', 'dobyear', 'nickname', 'regerror'); 

$_SESSION['username'] = $username;
$_SESSION['email'] = $email;
$_SESSION['emailconfirm'] = $emailconfirm;
// yadda, yadda, yadda...

Once you do this, you can use the variables in your query like you are trying to do.

"SELECT id FROM users WHERE username='$username'";

Otherwise, you would have to do something like:

"SELECT id FROM users WHERE username='$_SESSION['username']'";

JoelyUK · Jul 14, 2003

Done that.

Now it still says:

SELECT id FROM users WHERE username='joely2'
Query was empty

Wheres that huge crying smiley

DeltaRho2K · Jul 14, 2003

I know this is going to sound silly, but..... Are you sure there is a user name in your users.username field that is joely2?

When your query comes back empty, it usually means just that. There was no result set...

Also, where are these values defined? (just out of curiosity.) I just wanted to make sure you were connecting to the right DB. (In case you had a users table in two different DBs)
$mysqldatabase, $mysqlconnection

I know this all sounds silly, but sometimes, you have to go back to basics to find problems you think are more complex than they really are...

Next, what happens if you put in the name "joely2" instead of the variable in your SQL?

Lastly, move all of your connection, and SQL to a different page (without all of the error checking) and see if it queries things fine there...

JoelyUK · Jul 14, 2003

Originally posted by DeltaRho2K
I know this is going to sound silly, but..... Are you sure there is a user name in your users.username field that is joely2?

That is what it is checking - to see if it is present. If not, great. If it is, stop and report back.

This error is occuring with the bit of script in my signup that checks to see if someone has that username already

When your query comes back empty, it usually means just that. There was no result set...

Judging from the MySQL Help pages, it means there was no query passed in the mysql_query() bit, I think. But there must be, since I can echo it just before it actually does the query.

Also I can echo $username, $POST['username'] and $SESSION['username'] no problem at all during that stage.

Also, where are these values defined? (just out of curiosity.) I just wanted to make sure you were connecting to the right DB. (In case you had a users table in two different DBs)
$mysqldatabase, $mysqlconnection

Yup, connecting to right DB, as it works fine if I refresh the page (which is a very odd thing!). The MySQL connection info is defined in the include at the stop of the script (functions.php).

I know this all sounds silly, but sometimes, you have to go back to basics to find problems you think are more complex than they really are...

Yeah. I've also tried commenting out all of my stuff. Commenting out the MySQL queries, and it processes the registration fine.

I tried rewriting the MySQL query, using someone's suggestion from above:

		mysql_select_db($mysqldatabase, $mysqlconnection); 
		$sql = "SELECT username FROM users WHERE username='$_POST['username']'";    //search for username 
        $result = mysql_query($sql) or die(mysql_error()); 
        $num = mysql_num_rows($result); 
//		if($num > 0){ 
//		$regerror .= " - That username exists. Please choose another";
//		$username = "";
//		fatalerror();
//		exit;
//		}  

		echo $num;

That does the same. "Query was empty".

However, upon a refresh, I get the HTML results, the data is added, and the number '0' at the top of the page (result of echo $num).

hat happens if you put in the name "joely2" instead of the variable in your SQL?

Well I tried that. And I also tried putting the Query into the mysql_query(); itself. Still the same error

lastly, move all of your connection, and SQL to a different page (without all of the error checking) and see if it queries things fine there...

I'll try that next!

JoelyUK · Jul 14, 2003

Ok, it works fine on its own page.

But not with the rest of the script

Sooooooooooooooo. I need to check the rest of the script!

JoelyUK · Jul 14, 2003

Ok, going a further step back.

The problem, which seems to miraculously fix itself when you refresh the page, I though may be linked to sessions somehow.

So I commented out all of my session stuff.

And low and behold, it works. First time.

So how the HECK were the sessions interferring with the MySQL query?

I could echo all the variables and there various forms remember - so what the heck is it? lol

JoelyUK · Jul 14, 2003

I think I've sorted it.

As daft as it sounds, I put the 'session_register' bits in the wrong place.

After fiddling, this combo seems to work:

<?php 

// Start session
 session_start();

include("/home/site/public_html/resources/includes/functions.php");

// register them variables
 session_register("username");
 session_register("email");
 session_register("emailconfirm");
 session_register("password");
 session_register("passwordconfirm");
 session_register("dobday");
 session_register("dobmonth");
 session_register("dobyear");
 session_register("nickname");
 session_register("regerror");

// Assign variables from form
$username = $_POST['username'];
$email = $_POST['email'];
$emailconfirm = $_POST['emailconfirm'];
$password = $_POST['password'];
$passwordconfirm = $_POST['passwordconfirm'];
$dobday = $_POST['dobday'];
$dobmonth = $_POST['dobmonth'];
$dobyear = $_POST['dobyear'];
$nickname = $_POST['nickname'];
$regerror = ""; // Always blank the error var, because I'm silly and forget that.

[..] 

?>

Tested it many times, and a few friends too.

Thanks to everyone, especially DeltaRho2K for your help mate

I'll go and hide in the corner at the fact it was actually session's fault, and nothing to do with the mysql_num_rows. Still weird it wouldnt work though! :rolleyes:

DeltaRho2K · Jul 14, 2003

You are welcome...

By the way, you're not 100% there...
The session actually works like this:

// Assign variables from form to variables
$username = $_POST['username']; 
$email = $_POST['email']; 
$emailconfirm = $_POST['emailconfirm']; 
$password = $_POST['password']; 
$passwordconfirm = $_POST['passwordconfirm']; 
$dobday = $_POST['dobday']; 
$dobmonth = $_POST['dobmonth']; 
$dobyear = $_POST['dobyear']; 
$nickname = $_POST['nickname']; 
$regerror = ""; 

// register them variables in the session
 session_register("username"); 
 session_register("email"); 
 session_register("emailconfirm"); 
 session_register("password"); 
 session_register("passwordconfirm"); 
 session_register("dobday"); 
 session_register("dobmonth"); 
 session_register("dobyear"); 
 session_register("nickname"); 
 session_register("regerror"); 

// Assign the registered sessions back to variables for use later.
$_SESSION['username'] = $username; 
$_SESSION['email'] = $email;
$_SESSION['emailconfirm'] = $emailconfirm;
$_SESSION['password'] = $password;
$_SESSION['passwordconfirm'] = $paswordconfirm;
$_SESSION['dobday'] = $dobday;
$_SESSION['dobmonth'] = $dobmonth;
$_SESSION['dobyear'] = $dobyear;
$_SESSION['nickname'] = $nickname;
$_SESSION['regerror'] = $regerror;

You may have trouble destroying the session variables the way you have it now... Also, I am not seeing how you could be assigning any values to your session_registers since you don't have the variables defined yet...

Try what I have here, and if that works, great (which it should )
If not, then keep going your way, but something just doesn't seem right about what you currently have...

[Resolved] mysql_num_rows help

JJoelyUK

OOhLordy

Ccassius

DDeltaRho2K

Ccassius

DDeltaRho2K

JJoelyUK

Sstolzyboy

JJoelyUK

JJoelyUK

DDeltaRho2K

JJoelyUK

DDeltaRho2K

JJoelyUK

DDeltaRho2K

JJoelyUK

JJoelyUK

JJoelyUK

JJoelyUK

DDeltaRho2K