mysql WHERE id='$id' not working?

Question

Long Story Short: Made a CMS a year ago, worked great. Starting again from scratch. For me - for whatever reason - the simplest things always seem to give me the most trouble.

EDIT USER PAGE:
When I did this a year ago, and I wanted to print out all the data from the table, I used the while loop. When I wanted to only grab certain variables from a table, I just created a new set of variables, as show below. Using the same method from a year ago, I started working on printing various data to the screen (such as "Welcome $username"). 5 minutes of typing... refresh page... nada.
So I stripped away just about every tag but the essentials and it still wouldn't work
=======================================================================
...
$userID = 3;
$sql = "SELECT * FROM users WHERE userID='$userID'";
$result = mysql_query($sql);
$rows = mysql_fetch_row($result);

echo $rows['username'];
...
=======================================================================
Normally, this would just grab the username from a row in Table users where the userID = 3
But instead - it does nothing. I just see a blank page. It's a simple 5 lines of code and it's like "im just gonna do w.e the fk i wanna do mmmk thxforplaying"

PS: Keep in mind there is nothing wrong with the connection to the database, and I'm able to print ALL values in a while loop, but I can't grab single values

Answer 1

1. is there a column 'userID' on the 'users' table?

2. is there a row with userID=3 on that table?

3. mysql_fetch_row gives you back only ONE row. So the variable you are using $rows is confusing. Please change it to $row.

4. mysql_fetch_row returns a numerical array. So use $row[0], $row[1]....

5. you can use mysql_fetch_assoc to get $row['username'].





You are not testing for condition number 2 above! Do this before fetching the row:



if (!$result) {

echo 'No such user: ' . mysql_error();

exit;

}

$row = mysql_fetch_row($result);

Answer 2

Depending on mysql/settings there are differences in handling upercase/lowercase letters in table field names. So, there is a small chance, that if mysql got upgraded and code no longer works, you should change WHERE userID= ... to WHERE userid= . Although usually MYsql is not case sensitive by default, other database engines are. Thus using anything but lowercase for database field names might be considered poor coding style.

Additionally, the same mistake might be in echo $rows['username']; You should do print_r($row) to see if any data is received and how fields are named.

If it fails, you should print_r($result) to see if the query passes without any errors.

Answer 3

1. Your query is not correctly formatted:

$sql = "select * from `users` where `userID` = '". $userID . "'";

expanded: `userID` = ' " . $userID . " ' " ;

- Php does not parse variables enclosed in single quotes ($userID)

- variables must be enclosed in single quotes

- table and field names must be enclosed in single reverse quotes.



2. Your query CAN return more than one entry, hence you should use a while loop to retrieve the results:

$res = mysql_query($sql) or die(mysql_error());

// use "or die" to get an error report if any. Remove when tested.

while ($row = mysql_fetch_array($res))

{

echo ($row['username']);

}



3. make sure that the indexes are correctly spelt, these exist in the table, and case sensitive!



If you wanted to extract only ONE row, review your query:

$sql = "select * from `users` where `userID` = '". $userID . "' limit 1";

and use

$row = mysql_fetch_array($res); // one row returned only



4. Finally, do not forget to release the result!

mysql_free_result($res);

Answer 4

I know MySQL, but is your language PHP? I'm not familiar with PHP, but it kind of looks like $rows is just a recordset that is supposed to contain values fetched from the users table where userID = 3.



I've done a bit of reading about PHP and seem to recall that it treats single quotes differently than double quotes. Are you certain that you've used the correct quote in the line:



echo $rows['username'];



?



Failing that, I would open a direct connection to the MySQL database and run your query against it. You can install the MySQL client tools on your computer. You can download them from the http://www.mysql.com website. On a Windows computer, you can directly open the database with the following command:



mysql -hHOST -uUSER -pPASS -DDATABASE

where HOST is the host name of the MySQL database server, and USER is your username, and PASS is your password. DATABASE is the name of your database on the specified HOST. The parameters (-h, -u, -p, and -D) are case sensitive. In other words, -D is different than -d.



Once connected, run your query and see what the results are:



SELECT * FROM users WHERE userID=3;



Did you get what you expected? Yes, problem is elsewhere. No, you've found your problem.





One cool thing about MySQL is that its free and it can run on most computers. There are Linux versions, windows versions, etc. It will run on Windows servers or on XP, Vista, 7, or even Windows 2000. You can always download and install your own copy of the MySQL server and create a local copy of your database. Modify the my.ini file (MySQL configuration file, found in the MySQL program files folder). Change the lines:

#log-long-format

#log=



to:



log-long-format

log=c:/mysql.log



and then change your ODBC datasource to point at your local MySQL database. Then your MySQL commands, issued from your webpages will be logged in the specified log file. You can verify that your SQL Queries are being properly (and expectedly) sent to the server. Once you have verified this and you've gotten your code working, you can set your ODBC datasource back to the original MySQL server.



Note: you'll have to create a port forward for port 3306 on your router to allow the connection between your web server and your local test MySQL database. You can transfer the data from your online MySQL database to your local database with the MySQLDump command (part of the MySQL client tools).