MySQL Erroneously Returning Empty Result Sets

[XenoPhyre]

 

 

MySQL is incorrectly returning empty result sets in response to SELECT queries from

a login script, which is seen below. MySQL Server Version I use is 5.0.41-community-nt

 

if($row['Username'] != $username)
{
echo "
<table border='0' style='-moz-opacity:1.00;filter:alpha(opacity=100);font:12px Arial'>
<tr><td>
<b><font color='red'>Username not found. Please try again.</font></b></td></tr>
</table><br><br>
";
$_SESSION['signedin'] = 0;
}

if($row['Username'] == $username)
{

if($row['PasswordHash'] != $passwordhash)
{
echo "
<table border='0' style='-moz-opacity:1.00;filter:alpha(opacity=100);font:12px Arial'>
<tr><td>
<b><font color='red'>Password is invalid. Please try again.</font></b></td></tr>
</table>";
$_SESSION['signedin'] = 0;
}

if($row['PasswordHash'] == $passwordhash)
{
$_SESSION['signedin'] = 1;
$_SESSION['username'] = $row['Username'];
$_SESSION['reputation'] = $row['TotalThumbsUp'] / ($row['TotalThumbsUp'] + $row['TotalThumbsDown']);
$_SESSION['friends'] = explode("#",$row['FriendsList']);



}
}
}

 

To test the script, I used the signup script to enter a record. Username: XenoPhyre, Password: ************.

 

When using the login script, MySQL returns no result, therefore the script will return a

"Username not found" error back to the user.

 

->MySQL Server Version: 5.0.41-community-nt

 

->When diagnosing the variables:

  Testing the variable $row with mysql_num_rows() returns 0.

  Testing the variable $_POST['username'] returns the correct data ("XenoPhyre").

 

->The same problem occurs when trying to manually bring up the record via PhpMyAdmin

  using an SQL Query on the database:

 

   SELECT * FROM vm_userdb WHERE Username = 'XenoPhyre'

  MySQL still returns empty result set.

 

->Dropping the database then rebuilding it still does not fix the problem. If it may be of any

  help, this was the SQL query used to build the database:

 

$sql = "CREATE DATABASE PassportSvc";
$sql2 = "CREATE TABLE vm_userdb(
Username varchar(30),
PasswordHash varchar(30),
EMailAddress text,
UniqueID varchar(30),
YOB varchar(,
AccountCreationDate varchar(17),
UserLevel bigint(9),
AvatarContent mediumblob,
OriginalMachine text,
AuthorizedMachines text,
Games text,
Medals text,
TotalThumbsUp bigint(9),
TotalThumbsDown bigint(9),
FirstName text,
LastName text,
Gender int(1),
AKA text,
PersonalDescription text,
Location text,
ContactInformation text,
Notebook text,
DownloadSalvage text,
SecretQuestion varchar(150),
SecretAnswer text,
PaymentOptionData text,
StoreCredit bigint(9),
AdminRemarks text,
FreezeReason text,
BanReason text,
FriendsList text)";

 

I reckon this might be a MySQL bug...

 

[XenoPhyre]

Sorry, I think the first code block in my post contains the wrong info. Here's the

correct info:

 

if($_POST['action'] == "login")
{
$sqlquery = "SELECT * FROM vm_userdb WHERE Username ='" . $_POST['username'] . "'";
$result = mysql_query($sqlquery);
$row = mysql_fetch_assoc($result);
echo mysql_num_rows($result);
echo $_POST['username'];
echo $result;
echo $row;

if($row['Username'] != $username)
{
echo "
<table border='0' style='-moz-opacity:1.00;filter:alpha(opacity=100);font:12px Arial'>
<tr><td>
<b><font color='red'>Username not found. Please try again.</font></b></td></tr>
</table><br><br>
";
$_SESSION['signedin'] = 0;
}

if($row['Username'] == $username)
{

if($row['PasswordHash'] != $passwordhash)
{
echo "
<table border='0' style='-moz-opacity:1.00;filter:alpha(opacity=100);font:12px Arial'>
<tr><td>
<b><font color='red'>Password is invalid. Please try again.</font></b></td></tr>
</table>";
$_SESSION['signedin'] = 0;
}

if($row['PasswordHash'] == $passwordhash)
{
$_SESSION['signedin'] = 1;
$_SESSION['username'] = $row['Username'];
$_SESSION['reputation'] = $row['TotalThumbsUp'] / ($row['TotalThumbsUp'] + $row['TotalThumbsDown']);
$_SESSION['friends'] = explode("#",$row['FriendsList']);



}
}
}

 

 

[fenway]

Forgot about PHP for a minute... if your SELECT query doesn't work from phpmyadmin, you're either connected to the wrong DB, or there are really no records that match your results.

[XenoPhyre]

It would be great if that were the case, but unfortunately it isn't. A matching record is right there

and its as if MySQL is ignoring it completely. Heck, I even have a screenshot to prove it.

 

 

http://img292.imageshack.us/img292/3272/problempc9.png

 

 

I am connected to the correct database, and there should be records that match the request.

From the screenshot it will seem that SELECT queries without a WHERE will work perfectly

 

SELECT * FROM `vm_userdb`

 

While the problem in question will occur if you are looking for something specific using WHERE

 

SELECT * FROM `vm_userdb` WHERE Username = 'XenoPhyre'

 

As seen here:

http://img150.imageshack.us/img150/7492/problem2wi3.png

 

See? I'm not crazy. 

[XenoPhyre]

A small update:

 

When another table is built, this time with only 1 field, which is Username, the problem suddenly

goes away. MySQL will return a result set.

[fenway]

"another table"?