PHP Database Retrieval from MySQL WHERE user_name is saved in session

Question

I am attempting to retrieve plan information for all plans attached to a specific user. They are logged in and their username is saved in the session, and I know I need to use this, in combination with the MySQL WHERE statement. Here's the code I have:

<?php
$servername = "localhost";
$username = "root";
$password = "XXX";
$dbname = "name";

// Create connection
$conn = mysqli_connect($servername, $username, $password, $dbname);
// Check connection
if (!$conn) {
    die("Connection failed: " . mysqli_connect_error());
}

$sql = "SELECT plan_id, plan_name, plan_type, plan_active FROM plans WHERE user_name ='$_SESSION['user_name']'";
$result = mysqli_query($conn, $sql);

if (mysqli_num_rows($result) > 0) {
    // output data of each row
    while($row = mysqli_fetch_assoc($result)) {
        echo "id: " . $row["plan_id"]. "<br>";
        echo "id: " . $row["plan_name"]. "<br>";
        echo "id: " . $row["plan_type"]. "<br>";
        echo "id: " . $row["plan_active"]. "<br>";
    }
} else {
    echo "0 results";
}

mysqli_close($conn);
?>

Notice I use $_SESSION['user_name'] in the WHERE statement. What did I do wrong? Nothing gets displayed at all.

A better approach to MySQL queries is to use prepared statements (documentation) and bind parameters to it. It is also much safer beacuse it denies any potential SQL injections. — Tomi S
– Tomi S, Commented Jan 22, 2016 at 19:10
1. Use prepared statements instead; 2. Otherwise, if you are using an array element use ${varname[key]}; But an even better approach would be to use sprintf: $sql = sprintf("SELECT plan_id, plan_name, plan_type, plan_active FROM plans WHERE user_name ='%s'", $_SESSION['user_name']);... but again use prepared statements instead. — prodigitalson
– prodigitalson, Commented Jan 22, 2016 at 19:14
@prodigitalson 2. why? There is only one solution use prepared staments — VeeeneX
– VeeeneX, Commented Jan 22, 2016 at 19:17
@VeeeneX: becuase he needs the answer for general purpouses. He obviously doesnt know the syntax to use and array element in string. In this case its a line of SQL, that should indeed be used as a param to a statement. However, he still needs to know generally why its not working because he could just as simply be trying to ouput "My name is '$_SESSION['name']' and Im new to PHP." to screen. Had i actuall posted an answer i would have first mentioned this, then gone over string concatenation, then addressed the issue with the db sepcific stuff by giving an example using prepared statements.. — prodigitalson
– prodigitalson, Commented Jan 22, 2016 at 19:28

Akshay · Accepted Answer · 2016-01-22 19:14:00Z

1

You aren't properly passing the $_SESSION['user_name'] variable in the query.

Try this :-

Replace :-

$sql = "SELECT plan_id, plan_name, plan_type, plan_active FROM plans WHERE user_name ='$_SESSION['user_name']'";

with :-

$sql = "SELECT plan_id, plan_name, plan_type, plan_active FROM plans WHERE user_name ='" . $_SESSION['user_name'] . "'";

I'd suggest you to use prepared statements. You won't make these type of errors then.

answered Jan 22, 2016 at 19:14

Akshay

2,2393 gold badges17 silver badges35 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

Akshay Over a year ago

@VeeeneX Any reasons ?

VeeeneX Over a year ago

Because this bad practise because of mysql injection.

Mm-Art-In Over a year ago

@VeeeneX the MySQL injection is an issue but that issue does not relate to Akshay's answer of properly escaping variables inside strings.

VeeeneX Over a year ago

@Martin Yes, it's related we don't know how SESSION["user_name"] is set

Mm-Art-In Over a year ago

sorry @VeeeneX but it these two points are not equal. Yes, there is a factor of possible SQL injection in the current layout, but the answer Akshay gives does not need to address that, and does not mention or address that as it is outside the core scope of this Question .Your comment of Never. Ever. Do this sounds very much like you're saying never ever close a string and concatenate a variable into the string in the form that akshay has done in his answer. If the variable is first cleaned then the answer Akshay gives is still absolutely valid.

|

mega6382 · Accepted Answer · 2016-01-22 20:50:08Z

1

$_SESSION['user_name'] has the same type of quotes surrounding it and within it. I would set $username = $_SESSION['user_name'] outside of the sql query and then put $username in the sql query.

edited Jan 22, 2016 at 20:50

mega6382

9,39217 gold badges52 silver badges73 bronze badges

answered Jan 22, 2016 at 19:14

tklodd

1,08812 silver badges24 bronze badges

Comments

VeeeneX · Accepted Answer · 2016-01-23 11:20:58Z

0

There is problem with quotes as @kloddant mentioned. This is how a correct query should look like:

SELECT plan_id, plan_name, plan_type, plan_active FROM plans WHERE user_name = ?

And partial code looks like this:

/* create a prepared statement */
if ($stmt = $mysqli->prepare("SELECT plan_id, plan_name, plan_type, plan_active FROM plans WHERE user_name = ?")) {

    /* bind parameters for markers */
    $stmt->bind_param("s", $SESSION['user_name']);

    /* execute query */
    $stmt->execute();

    /* bind result variables */
    $stmt->bind_result($result);


    /* Play with result */


    /* close statement */
    $stmt->close();
}

A few tips; If you have large database of users, index your user_name field or use id instead of user_name. This can make your query faster.

edited Jan 23, 2016 at 11:20

answered Jan 22, 2016 at 19:27

VeeeneX

1,57618 silver badges27 bronze badges

2 Comments

MMultiLinguist Over a year ago

Is your full code supposed to replace all of what I have written in the question or just a part of it?

VeeeneX Over a year ago

@MMultiLinguist No. It's only partial code :), sorry

Collectives™ on Stack Overflow

PHP Database Retrieval from MySQL WHERE user_name is saved in session

3 Answers 3

7 Comments

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

7 Comments

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related