Mysql query in Session variables

[capgrasdelusion]

I have two session variables whose values are retrieved from my database when the user logs in. I then want to display them on the page the user is brought to. The first one displays the username, and that works fine. The second displays a variable name money and this does not work, because I cannot retrieve it with my query for some reason. The code I used to retrieve it was:

$_SESSION['username'] = mysql_query("SELECT * FROM users WHERE username=$username'");
$_SESSION['money'] = mysql_query("SELECT money FROM users WHERE username=$username'");

Can someone help me find out what I'm doing wrong?

[laserlight]

At the moment you are storing the result resources into the session, but you probably want to store the actual username and money value, e.g.,

$_SESSION['username'] = $username;
$sql = sprintf("SELECT money FROM users WHERE username='%s'", $username);
$result = mysql_query($sql);
$_SESSION['money'] = mysql_result($result, 0);

[capgrasdelusion]

This line gives me an error:

$_SESSION['money'] = mysql_result($result, 0);

This is the error:

Warning: mysql_result(): Unable to jump to row 0 on MySQL result index 3 in /home/www/pushergame.awardspace.com/test/login.php on line 6

I'm also still not sure why the username query worked, but not the money one.
Thanks for any further help.

[laserlight]

That implies that the username does not exist. The result set is empty.

[capgrasdelusion]

Thank you, That worked, but now I realized I will need to add a lot more stats to be retrieved and manipulated, could I retrieve it all in the same query, or would I have to redefine the query over and over? Not to insult your inteligence, but remember that I need all of these querys stored in to session variables, and i'm not quite sure why you used %s in the username query, it works, but I don't understand it, can you please explain this to me? Thank you.

[laserlight]

capgrasdelusion wrote:

That worked, but now I realized I will need to add a lot more stats to be retrieved and manipulated, could I retrieve it all in the same query, or would I have to redefine the query over and over?

Yes, you generally can retrieve all that you need with a single query.

capgrasdelusion wrote:

remember that I need all of these querys stored in to session variables

No, you need the results retrieved to be stored in the session, not the query result resource itself, nor the SQL statement string.

capgrasdelusion wrote:

i'm not quite sure why you used %s in the username query, it works, but I don't understand it

Read the PHP manual concerning [man]sprintf/man and [man]mysql_real_escape_string/man. In this case I did not use mysql_real_escape_string() because you might have used it earlier (or I just forgot, as the case may be), but typically I would not escape the variable until needed (since it could be used in another context where the escaping is wrong), so I would write:

$sql = sprintf("SELECT money FROM users WHERE username='%s'",
    mysql_real_escape_string($username));

The primary advantage of this is that it can be clearer to read than concatenating the variables directly (but if you use the PDO extension or MySQLi extension then you can use prepared statements with named placeholders, and these are even easier to read and provide an even safer escaping functionality than mysql_real_escape_string()).

[capgrasdelusion]

So I've been reading the documentation you reccommended, and now I am even more confused, and ready to give up on my project, I was trying to make an RPG, and I'm not sure if I should use normal php, POD, or I'm not sure if you know anything about this, but adodb to do my data handling. If you could give me any insight into what would be easiest or most effecient it would be greatly appreciated. Thank you.

[laserlight]

capgrasdelusion wrote:

I was trying to make an RPG, and I'm not sure if I should use normal php, POD, or I'm not sure if you know anything about this, but adodb to do my data handling.

What do you mean by "normal PHP"? ADOdb is probably a fine choice if you want to support multiple database management systems, but I prefer using the PDO extension with a simple abstraction layer if I want to support multiple database management systems. By default, the PDO extension is available with the SQLite 3 driver.