1

I have a login scrip that previously appeared to work, but now is not working for some users, including some that previously worked.

The SQL always works in phpMyAdmin, but on the webpage for some users it always returns zero rows. Here is the code where there is a problem:

$query_str = 'SELECT id, username, level_access, active FROM users WHERE username = "';
$query_str .= mysql_real_escape_string($uname).'"';
$query_str .= ' AND password = "'.mysql_real_escape_string($pwd).'"';
echo $query_str. "<br/>";

$result = mysql_query($query_str);
$num_result = mysql_num_rows($result);   
echo $num_result. "<br/>";

I spent considerable time testing any variations, and combinations that I could think of, I added back quotes, changed variable names, inserted other variables, simplified the expressions. Each change I tested the outputted query statement in phpMyAdmin. I added other users, deleted users and re-created them, I changed access levels and usernames.

Always I got the same result, for some users the login always works for others it always returns no rows, even though they are returned in phpMyAdmin

Finally I removed the where statement so that I created the following SQL statement:

$query_str = 'SELECT id, username, level_access, active FROM users';

In my webpage this always returns 8 rows, no matter how many users there are. If there are 8, 9 or 10 users it return 8 rows. I deleted users so that there are less than 8 users, but there are still 8 rows returned to the webpage phpMyAdmin the correct number is returned each time.

It seems clearly that the issue is not the code. So this is beyond my expertise. What could be causing this problem and how do I solve it?

user1047397
  • 11
  • 1
  • 1
    Are you sure that your PHP code and phpmyadmin are looking at the same database? – Marc B Nov 16 '11 at 03:29
  • Are you sure your data is correct? Sometimes, something as subtle as one incorrect value can throw your whole result out. Also, try using different DB tools, such as PhpMyAdmin _and_ MySQL Work Bench. – Agi Hammerthief May 17 '13 at 00:18

2 Answers2

0

What are your MySQL cache settings ? Returing 8 rows when there are less than that in the database sounds like it could be caching old results of a query to me. You can disable the cache by adding SQL_NO_CACHE after the SELECT statement, so does changing your query to $query_str = 'SELECT SQL_NO_CACHE id, username, level_access, active FROM users'; help ?

Michael Low
  • 24,276
  • 16
  • 82
  • 119
  • I don't know anything about cache setting, but just tried your suggestion and still get 8 rows. – user1047397 Nov 16 '11 at 03:01
  • Hmm....there are other suggestions about getting round the cache on http://stackoverflow.com/questions/181894/mysql-force-not-to-use-cache-for-testing-speed-of-query , you might find something that works there. – Michael Low Nov 16 '11 at 03:03
  • It just occurred to me that I haven't tried printing out the rows, that should tell me something. I am doing that now. – user1047397 Nov 16 '11 at 03:07
  • OK, well it is returning the old users. So it seems that the Cache must be the problem. Thanks very much. I will follow those links and try those solutions. – user1047397 Nov 16 '11 at 03:09
  • No problem, hope you get it working. There's a link on there about flushing or resetting the cache, with any luck that should work for you. BTW, you can see your current cache settings if you enter SHOW VARIABLES WHERE variable_name LIKE '%query_cache%' into phpmyadmin, which might help if you want to search for what some setting is doing. – Michael Low Nov 16 '11 at 03:16
  • After trying everything followed by proper investigation when it didn't work, it turns out that the cache is turned off. – user1047397 Nov 17 '11 at 01:22
0

Have you connected to the database ( you think) is being queried explicitly?

$link = mysql_connect('localhost', 'mysql_user', 'mysql_password');
mysql_select_db ('myDatabase' , $link);

Then you need to use the $link handle in your queries.

$result = mysql_query($query_str, $link);
Pranav Hosangadi
  • 23,755
  • 7
  • 44
  • 70
  • Yes I am connecting to the database. But I have just discovered that its connecting to the WRONG database for this query. The weird thing is that its only this query that is connecting to the wrong database, and its the same line of code that is doing the connection (ie not an identical line of code). – user1047397 Nov 17 '11 at 01:25
  • Actually I wasn't using the $link in my queries. I have added it to the one that is causing a problem and I get an "not a valid MySQL-Link resource" error. NOTE that this is despite every other query in the whole website working correctly. – user1047397 Nov 17 '11 at 01:51
  • @user1047397 very weird... Have you replaced localhost with the location of the database (and, if not on the default port, then the port number also.) mysql_user with a valid user and mysql_password with the password for the user? – Pranav Hosangadi Nov 17 '11 at 06:39