Return JSON in php from mySql Database

Question

I have the following data in my database:

I want to have the date in the JSON format below:

{
    "labels": ["12.11.2016", "13.11.2016", "14.11.2016", ...],
    "temperature": ["12", "35", "27", ...],
    "humidity": ["56", "70", "87", ...]
}

My current code is:

$conn = new PDO("mysql:host=$servername;dbname=$dbname", $username, $password);
        // set the PDO error mode to exception
        $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
        $conn->setAttribute(PDO::ATTR_CASE, PDO::CASE_UPPER);

        //Select the requested data
        $sql = "SELECT date, temperature, humidity FROM `TestData` ORDER BY date ASC ";

        $statement = $conn->prepare($sql);
        $statement->execute();
        $result = $statement->fetchAll(PDO::FETCH_ASSOC);

        //Values returned for JavaScript
        $labels = array();
        $temperature = array();
        $humidity = array();
        $data = array();

        foreach ($result as $row)
        {
            array_push($labels, $row['date'] );
            array_push($temperature, $row['temperature']);
            array_push($humidity, $row['humidity']);
        }
        $result = null;

        //Load data in one single array
        $data['labels'] = $labels;
        $data['temperature'] = $temperature;
        $data['humidity'] = $humidity;
        //echo $data;
        //echo json_encode($labels , JSON_FORCE_OBJECT);
        echo json_encode($data);

but this gives me only the following sh*t:

Does anyone have an idea here? I just can't get it working even if I already searched the internet for like 5 hours :/

What's the output of $result ? var_dump($result)

monokh
– monokh

2016-10-02 20:07:33 +00:00
Commented Oct 2, 2016 at 20:07 — monokh
– monokh, Commented Oct 2, 2016 at 20:07
Output is here

FranzHuber23
– FranzHuber23

2016-10-02 20:12:53 +00:00
Commented Oct 2, 2016 at 20:12 — FranzHuber23
– FranzHuber23, Commented Oct 2, 2016 at 20:12

Shawn · Accepted Answer · 2016-10-02 22:11:54Z

2

Try:

array_push($labels, $row['DATE'] );
array_push($temperature, $row['TEMPERATURE']);
array_push($humidity, $row['HUMIDITY']);

Looks like all you column names are upper case because of $conn->setAttribute(PDO::ATTR_CASE, PDO::CASE_UPPER);.

edited Oct 2, 2016 at 22:11

answered Oct 2, 2016 at 20:19

Shawn

2,7553 gold badges27 silver badges49 bronze badges

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

4 Comments

FranzHuber23 Over a year ago

Haha, WTF... This worked for me :D Ok, thank you @Shawn. Result is now in the right format :)

FranzHuber23 Over a year ago

What exactly is the issue behind this? Can you explain this to me. I don't think, I used upper case descriptions for my columns... :/

Shawn Over a year ago

Actually it's this line: $conn->setAttribute(PDO::ATTR_CASE, PDO::CASE_UPPER);

FranzHuber23 Over a year ago

Ah, f*ck, yes... Didn't think about this anymore :/

monokh · Accepted Answer · 2016-10-02 20:19:28Z

1

The output of the fetch is a key to value pair. The key being the row number.

Also, your column names seem to be uppercase from the var dump that you posted.

Try this to fetch the values correctly:

foreach ($result as $key => $row)
{
    array_push($labels, $row['DATE'] );
    array_push($temperature, $row['TEMPERATURE']);
    array_push($humidity, $row['HUMIDITY']);
}

edited Oct 2, 2016 at 20:19

answered Oct 2, 2016 at 20:14

monokh

1,9903 gold badges22 silver badges31 bronze badges

5 Comments

FranzHuber23 Over a year ago

Nope, still gives me those null values... link

Shawn Over a year ago

foreach ($result as $key => $row) or foreach($result as $key) doesn't matter in this case.

FranzHuber23 Over a year ago

Yeah, that's what i realized as well, it really was the issue with the upper case letters

FranzHuber23 Over a year ago

Is now also the right answer... I cannot check 2 of them :(

monokh Over a year ago

@FranzHuber23 Shawn was first :)

Collectives™ on Stack Overflow

Return JSON in php from mySql Database

2 Answers 2

4 Comments

5 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

5 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related