Page 1 of 1

Finding which account is logged in via MySQL

Posted: Tue Dec 02, 2008 6:01 pm
by xQuasar
Hi guys, I'm new here, and to PHP :P so don't treat me too badly.

I've been trying to create a game which you register and login, and stores the data (username, pass etc.) within mySQL. But I can't seem to figure out a way to find which row from mysql that the person is logged in, and show the information for it.

For exmaple, when you login, you go to your base, and it shows some stats;

like attack, def, spy and sentry

and it'll show them as

Attack: [number, get from mysql]
etc.

So what do I do? maybe

Code: Select all

 
<?php
$username = "";
$attackquery = "SELECT FROM `accounts` where `username`="$username";
$attack = mysql_connect($attackquery);
?>
Attack Rating: <?php echo "$attack" ?>
 
As you can see, the only thing I'm stuck with is the $username variable. I don't know how to get it. Help?

Re: Finding which account is logged in via MySQL

Posted: Tue Dec 02, 2008 7:16 pm
by requinix
Here's how you're supposed to use the MySQL extension:

Code: Select all

<?php
// Connecting, selecting database
$link = mysql_connect('mysql_host', 'mysql_user', 'mysql_password')
    or die('Could not connect: ' . mysql_error());
echo 'Connected successfully';
mysql_select_db('my_database') or die('Could not select database');
 
// Performing SQL query
$query = 'SELECT * FROM my_table';
$result = mysql_query($query) or die('Query failed: ' . mysql_error());
 
// Printing results in HTML
echo "<table>\n";
while ($line = mysql_fetch_array($result, MYSQL_ASSOC)) {
    echo "\t<tr>\n";
    foreach ($line as $col_value) {
        echo "\t\t<td>$col_value</td>\n";
    }
    echo "\t</tr>\n";
}
echo "</table>\n";
 
// Free resultset
mysql_free_result($result);
 
// Closing connection
mysql_close($link);
?>
The dark blue function names are also links.

Taken straight from this page.

Re: Finding which account is logged in via MySQL

Posted: Tue Dec 02, 2008 11:45 pm
by xQuasar
nonono, I know that stuff. Just forgot to include it in my snippet there; but can anyone answer my question please? How would you make php know which account you're logged into?

Re: Finding which account is logged in via MySQL

Posted: Wed Dec 03, 2008 12:11 am
by requinix
You have to keep track of the user once they log in. You'll use sessions and probably cookies too.

If you want a tutorial, there's a forum for that kind of help.

Re: Finding which account is logged in via MySQL

Posted: Wed Dec 03, 2008 1:39 pm
by xQuasar
Anyone have a more specific answer? =/

Re: Finding which account is logged in via MySQL

Posted: Wed Dec 03, 2008 2:15 pm
by panic!
You mean "will someone do it for me"..The answer is: no.

Coding/programming is about problem solving, tasairis gave you a good push in the right direction, you can figure out the rest.

Re: Finding which account is logged in via MySQL

Posted: Wed Dec 03, 2008 9:40 pm
by xQuasar
Can cookies replace sessions completely?

EDIT: And another question; for cookies, can I do:

Code: Select all

$username = $_POST['username'];
setcookie("username","$username",time()+1800);
 
And on another file which will be checking...

Code: Select all

$query = "Select from `accounts` where `username`= $_COOKIE['username']";
mysql($query) or die ('Selecting account row failed');

Re: Finding which account is logged in via MySQL

Posted: Wed Dec 03, 2008 10:16 pm
by requinix
xQuasar wrote:Can cookies replace sessions completely?

EDIT: And another question; for cookies, can I do:

Code: Select all

$username = $_POST['username'];
setcookie("username","$username",time()+1800);
 
And on another file which will be checking...

Code: Select all

$query = "Select from `accounts` where `username`= $_COOKIE['username']";
mysql($query) or die ('Selecting account row failed');
Cookies are for the client, sessions are for the server.
Cookies couldn't completely replace sessions. The former is not secure while the second is (probably, depends on the configuration). You'd normally store more information in sessions so you don't have to look it up every time you need it: you can't always do that with cookies.

About the whole "cookies aren't secure", your code is close but not quite complete.

Code: Select all

"Select * from `accounts` where `username` = '" . mysql_real_escape_string($_COOKIE['username']) . "'"
Your SQL habits concern me, at least as far as you've shown in your posts. I hope you really do "know that stuff" as you say you do.

Re: Finding which account is logged in via MySQL

Posted: Thu Dec 04, 2008 12:13 am
by xQuasar
tasairis wrote:Cookies couldn't completely replace sessions. The former is not secure while the second is (probably, depends on the configuration). You'd normally store more information in sessions so you don't have to look it up every time you need it: you can't always do that with cookies.
Hmm. Thanks for that info :D but how do you make sessions store more than just the session name? Haven't found it on any tut yet.

Re: Finding which account is logged in via MySQL

Posted: Thu Dec 04, 2008 1:03 am
by requinix
After you call session_start(), the $_SESSION superglobal array is where you put your information. It acts just like $_POST and $_GET except you can save to it as well.

Re: Finding which account is logged in via MySQL

Posted: Thu Dec 04, 2008 3:52 am
by xQuasar
Thanks heaps tasairis :)

Okay. Another question. (I don't know as much mysql as I thought I did. Go easy on me, i'm only 14 :roll:

How come this:

Code: Select all

<?php
 
session_start();
 
include 'mysql/login.php';
include 'mysql/open.php';
 
$username = $_SESSION['username'];
$race_query = "SELECT `race` FROM `accounts` WHERE `username`='$username'";
$race = mysql_query($race_query);
 
?>
Race: <?php echo "$race" ?>
 
Is giving me the output: 'Race: Resource id #7 '

when all that's stored in my 'race' column is 1,2,3,4, and 5? (1 digit integers)

Re: Finding which account is logged in via MySQL

Posted: Thu Dec 04, 2008 4:12 am
by requinix
...which is exactly why I posted some example code ;) Way up the page. See how they use mysql_query and mysql_fetch_array.

Basically, mysql_query only executes the query - it doesn't give you any data back. To get the data (if any) you use a function like mysql_fetch_array.

For your code:

Code: Select all

$username = $_SESSION['username'];
$race_query = "SELECT `race` FROM `accounts` WHERE `username`='$username'";
$race = mysql_query($race_query);
$record = mysql_fetch_array($race);
 
?>
Race: <?php echo $record["race"]; ?>