How to select data from 'logged in user' from database to a graph

Question

I need help with some code. I want to select data from mySQL to a graph on my web page. The data must be from the current logged in user, and when I select data to a card it works fine, but when I select it to a graph the graph disappears.

I'm using this code for the cards, and it works fine:

 $sql = "SELECT energyexpenditure FROM energy4project WHERE user_id =  '{$_SESSION["user_id"]}' ORDER BY time_stamp DESC LIMIT 1;";

This is the code for my current graph that don't show data based on logged in user:

<?php
header('Content-Type: application/json');

        $host = "localhost";
        $user = "`blabla";
        $pwd = "blabla";
        $db = "blabla";
        $conn = new mysqli($host, $user, $pwd, $db);

        // Check connection
        if ($conn->connect_error) {
            die("Connection failed: " . $conn->connect_error);
        }


         $sql = "SELECT energyexpenditure, time_stamp FROM energy4project ORDER BY time_stamp DESC LIMIT 7;";

        $result = $conn->query($sql);


$data = array();
foreach ($result as $row) {
    $data[] = $row;
}

mysqli_close($conn);

echo json_encode($data);
?>

When I implement the code from the cards in the graph code it doesn't work.
Why does the SELECT WHERE user = '{$_SESSION["user_id"]} not work in the graphs?

Fast fix: `{$_SESSION['user_id']}` (use single quotes for index name when using string interpolation) . But you better start using prepared statements with placeholders. Read [how-can-i-prevent-sql-injection-in-php](https://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php) — Paul Spiegel, May 21 '19 at 20:02
I tried it with single quotes, $sql = "SELECT energyexpenditure, time_stamp FROM energy4project {$_SESSION['user_id']} ORDER BY time_stamp DESC LIMIT 7;"; and I put it in the graph file, but my file just disappears? — enille, May 21 '19 at 20:08
I'm sorry I mean I used this code... ````$sql = "SELECT energyexpenditure, time_stamp FROM energy4project WHERE user_id = {$_SESSION['user_id']} ORDER BY time_stamp DESC LIMIT 7;"; ````. And my graph still disappears — enille, May 21 '19 at 20:24

score 0 · Answer 1 · answered May 21 '19 at 20:19

If I understood good your Mysql query return an empty result. Try to modify your code as follows:

if(!$result = $conn->query($sql)) {
    echo "query error."; 
    die();
}

$data = array();

while($row = $result->fetch_array(MYSQLI_ASSOC))
{
    $data[] = $row;
}


/* free result set */
$result->free();

/* close connection */
$conn->close();

//uncomment the below line if you want to check de result of your mysql query because it seems be good.
//var_dump($data); die(); echo "<pre>"; 

echo json_encode($data);

So if you don't have any mysql error, verify your database name, table name are correctly and if it has data in your table.

Reference: https://www.php.net/manual/en/mysqli-result.fetch-array.php

Regards

The graph works just fine, but when I add WHERE user_id = .... the graph disappears.. — enille, May 21 '19 at 20:28
$userId = $_SESSION["user_id"]; Add this to your query $sql = "SELECT energyexpenditure, time_stamp FROM energy4project WHERE user_id = $userId ORDER BY time_stamp DESC LIMIT 7; — Daniel Luna, May 21 '19 at 20:32

How to select data from 'logged in user' from database to a graph

1 Answers1