XenoPhyre Posted January 25, 2008 Share Posted January 25, 2008 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... Quote Link to comment Share on other sites More sharing options...
XenoPhyre Posted January 25, 2008 Author Share Posted January 25, 2008 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']); } } } Quote Link to comment Share on other sites More sharing options...
fenway Posted January 25, 2008 Share Posted January 25, 2008 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. Quote Link to comment Share on other sites More sharing options...
XenoPhyre Posted January 26, 2008 Author Share Posted January 26, 2008 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. Quote Link to comment Share on other sites More sharing options...
XenoPhyre Posted January 26, 2008 Author Share Posted January 26, 2008 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. Quote Link to comment Share on other sites More sharing options...
fenway Posted January 26, 2008 Share Posted January 26, 2008 "another table"? Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.