p2p.wrox.com Forums

Need to download code?

View our list of code downloads.

  Return to Index  

beginning_php thread: user authentication -- query good, but mysql_num_rows false?

Message #1 by "keith hughitt" <hughittk@m...> on Mon, 10 Feb 2003 04:38:34
Ok, after much debugging with this im still at a loss., what happens is 
after inputting a user / password, mysql_query executes a select 
statement to check and see if there is a math for the password / user in 
the database, and returns the name of it if there is, and "0" if not. 
Everything works fine up until mysql_num_rows. i echod out the sql 
statement and ran it in mysql without php, and it found the 1 record. but 
in the php script after it sends the result to mysql_num_rows, it returns 
a 0 instead of a 1..? and therefor the user/pw combination is always 
wrong because it cannot pull out the name from the db. Well here is my 
script which is a modified version of the example in chaper 13, also in 
there are some of the debug checks i used, Any advice would be greatly 



include "./include/common_db.inc";

function auth_user($username, $userpassword) {
	global $default_dbname, $user_tablename;
	$user_tablename = 'users';
	$link_id = db_connect($default_dbname);
	echo "<br /><b>testing...</b> username is 
<em>$username</em><Br /><br />
			userpassword is 
<em>$userpassword</em><br /><br />";

	echo "<br/><div style=\"color:purple\">\$user_tablename = 
$user_tablename <br /></div>";
	$query = "SELECT username FROM $user_tablename
				WHERE username = '$username'
				AND userpassword = password
	echo "<br /><br /><b>QUERY:</b><br />$query<br /><br />";
	/** another debug test.. 
	if(!mysql_query($query)) die(sql_error());
	$result = mysql_query($query);
	echo "<br /><div style=\"color:pink\">result = 
$result</div><br />";
	/** used to see if mysql_num_rows is returning proper values, for 
debuging only **/
	$num_rows = mysql_num_rows($result);
	echo "<br/><div style=\"color:green\">$num_rows 
Rows\n</div><br />";

	if(!mysql_num_rows($result)) return 0;
	else {
		$query_data = mysql_fetch_row($result);
		echo "<div style=\"color:red;\">\$query_data[0] = 
		return $query_data[0];

function login_form() {
	global $PHP_SELF;
	echo "<?xml-stylesheet href=\"#internalStyle\" type=\"text/css\"?
Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-loose.dtd">
<form method="post" action="<? echo $PHP_SELF ?>">
	<div class="center"><center>
	<h3>Enter username and password to login.</h3>
	<fieldset> <legend><b>Personal Information</b> </legend>
	<div class="formLeft"><label for="id">ID</label></div>
	<input id="id" type="text" name="username" size="8">
	<div class="formLeft"><label for="password">Password</label></div>
	<input id="password" type="password" name="userpassword" size="8">
	<div class="right"><input type="submit" value="LOGIN" 

if(!isset($_POST['username'])) {
else {
	echo "executing second part of sessions<br/><br/>";
	$_SESSION['username'] = $_POST['username'];
	$_SESSION['userpassword'] = $_POST['userpassword'];
	//session_register("username", "userpassword");
	$username = auth_user($_POST['username'], $_POST['userpassword']);
	echo "<div style=\"color:blue\">$username</div>";
	if(!$username) {
		echo "Authorization failed. " .
			"You must enter a valid userid and password 
combo. " .
			"Click on the following link to try again. 
<br />\n";
		echo "<a href=\"$PHP_SELF?\">Login</a><br />";
	else echo "Welcome, $username!";



also in case it helps here is the output i get from the above script 
(from the various echo statements i put in):

executing second part of sessions

testing... username is xtort

userpassword is ragnar

$user_tablename = users 

SELECT username FROM users WHERE username = 'xtort' AND userpassword = 

result = Resource id #2

0 Rows 

Authorization failed. You must enter a valid userid and password combo. 
Click on the following link to try again. 


Thanks lots!

Message #2 by "keith hughitt" <hughittk@m...> on Mon, 10 Feb 2003 19:30:08
ok, i did some more testing and i *believe* now the problem to be in the 
password() mysql function. When i execute the sql statement 
SELECT username FROM users WHERE username = 'test' AND userpassword = 

on mysqladmin, it executes fine but does not return anything. If i take 
away the "AND userpassword = password('test');" it finds the entry fine. 
i tried re-entering the user/pw multiple times to make sure i was 
spelling it right and all but no luck. Any ideas? 

Message #3 by "keith hughitt" <hughittk@m...> on Tue, 11 Feb 2003 01:40:59
ok problem solved --

what i did wrong was i made the password field length 9 chars ( VARCHAR
(9) ) thinking that no one would use a password more than 9 chars, but i 
didnt think that it wasnt actualy the PASSWORD being stored, but the 
ENCRYPTED PASSWORD being stored. Also i found while looking for a 
solution that its better to use md5() to encrypt than password() (from 
the common functions in www.mysql.com, search for "password()" ) and also 
the field need be only 32 chars in length, for md5 at least.


  Return to Index