Show current logged users characters

[Tanelorn]

MYSQL version: 5.5.24

 

I've got a problem.

For my project in a class I want to show the characters of the person who is currently logged in.

I get it to show every character in the database "characters" but not the logged in.
I've tried to put this code in the WHERE but it will just ignore it and all of the other code jsut gets commented away when I do the "" to make it visible (in php) a friend of mine have written a C++ program that does this exact same thing altough I don't get it to work.

 

So my question is what should I put in the WHERE to get the characters.name of the "current user"

I've thought of making the: WHERE accounts.id = " . htmlentities($_SESSION['username'] ." Altough these two " makes so the rest of the code gets commented away and I've tried to put an extra " but nothing happens and I need the two " too make the htmlentities correct.

I know this is both PHP and MYSQL but since it's the SQL part I need help with I put it here.
If any admin see this as the wrong place to have my post please move it in that case or tell me to redo it at the right place

Edited April 24, 2013 by Tanelorn

[Jessica]

A. Don't post screenshots of your code, post the actual code.

B. why are you running htmlentities on that name?

C. Your where and on clauses are redundant

D. Switch to using user id instead of username, it'll be a lot easier.

[Tanelorn]

A. Don't post screenshots of your code, post the actual code.

B. why are you running htmlentities on that name?

C. Your where and on clauses are redundant

D. Switch to using user id instead of username, it'll be a lot easier.

I'm well aware of that the on and where is completely redundant there I had so that it showed every character and the owner of the character. and the htmlentities is the current user logged in user.

I don't want to show every character just the user that is currently logged in.

If I switch to user id it will list every account which I don't want it to I want it to list of the user who is logged in atm.

Theres the code

 

    $sql = "SELECT

                accounts.id,

                accounts.username,

                characters.owner,

                characters.name,

                characters.money

            FROM

                characters

            LEFT JOIN

                accounts

            ON

                accounts.id = characters.owner

            WHERE

                accounts.username = X

            ORDER BY owner ASC";

Edited April 24, 2013 by Tanelorn

[Jessica]

Good job on following one of the things I posted. :eyeroll:

[Psycho]

I think you got some misinformation or have confused two functions. htmlentities() is used to make content safe to display on a web page so the characters are not interpreted as HTML code. When displaying user input on a page, a user could include characters such as '<' that make the browser confused and completely corrupt the output. Or worse, a user could insert malicious JavaScript code that could hijack your visitors.

 

There is no good reason to use htmlentities() on data used in a DB query. You should be using an appropriate function to sanitize the data for use in a DB query, such as mysql_real_escape_string(). However, if you used htmlentities() on the data when you stored it,then you are stuck. You shoudl go back and clean up the existing data and start doing it correctly.

 

As for your most recent query, you should (as Jessica stated) use the user's ID instead of the username. That appears to be the field accounts.id. Also, no need to put "ORDER BY owner ASC" when you only want the results for one owner.

 

So, using the user's ID, this should work - you would need to set $userID beforehand, obviously.

$sql = "SELECT accounts.username, characters.owner,
               characters.name, characters.money
        FROM characters
        LEFT JOIN accounts ON accounts.id = characters.owner
        WHERE accounts.id = '$userID'";

Note: I removed accounts.id from the SELECT clause since you would have to have that information before running the query anyways.

Edited April 24, 2013 by Psycho

[Barand]

$sql = "SELECT accounts.username, characters.owner,
               characters.name, characters.money
        FROM characters
        LEFT JOIN accounts ON accounts.id = characters.owner
        WHERE accounts.id = '$userID'";

 

If you need the LEFT JOIN then the accounts.id = '$userID' needs to be part of the JOIN .. ON clause, otherwise it will behave like an INNER JOIN

[Tanelorn]

I've managed to solve this problem perhaps not the best way of solving it but for the task it's supposed to do it's okay
 

$id = htmlentities($_SESSION['id']) ;
    $sql = "SELECT
                *
            FROM
                characters
            WHERE
                characters.owner = '$id'";

 
It shows everything exactly as I want it to so thank you all for the help ^^

Edited April 25, 2013 by Tanelorn

[Jessica]

What does htmlentities do?

[Psycho]

There is no good reason to use htmlentities() on data used in a DB query. You should be using an appropriate function to sanitize the data for use in a DB query, such as mysql_real_escape_string(). However, if you used htmlentities() on the data when you stored it,then you are stuck. You should go back and clean up the existing data and start doing it correctly.

 

For an ID field you would normally want to verify/force the value to be an integer instead of mysql_real_escape_string().