How to encode php array in json and parse into jquery

Question

I am fetching record with drop down using ajax call but the problem is that it is fetching only one record while there are three records in the database

This is my PHP code:

<?php
include 'config/dbconfig.php';

$genid      = $_POST['id'];
$operatorId = $_POST['operatorId'];

$query = mysqli_query($con, "SELECT * FROM generatorrun WHERE generatorId='$genid' AND operatorId='$operatorId'");
while($result = mysqli_fetch_array($query)) {

    $turnOn           = $result['startTime'];
    $turnOff          = $result['endTime'];
    $datetime1        = new DateTime($turnOn);
    $datetime2        = new DateTime($turnOff);
    $interval         = $datetime1->diff($datetime2);
    $datedifference   = $interval->format('%Y-%m-%d %H:%i:%s');
    $startReading     = $result['startReading'];
    $endReading       = $result['endReading'];
    $dailyConsumption = $endReading - $startReading;

    $postData = array(
        "turnOn"           => $turnOn,
        "turnOff"          => $turnOff,
        "runningTime"      => $datedifference,
        "startReading"     => $startReading,
        "endReading"       => $endReading,
        "dailyConsumption" => $dailyConsumption,
    );
}

echo json_encode($postData);
?>

I have to fetch the values from MySQL and stored in an associative array and then encoding it with json_encode() function.

and this is the code for fetching the record in jquery:

<script>
$(document).ready(function () {
    $(".bg-yellow").hide();
    $(".bg-red").hide();
    $("#getGen").change(function () {

        var id = $('#getGen').val();
        var operatorId = $(".opid").val();
        $.ajax({
            type: "POST",
            url: 'getGenerator.php',
            data: {id: id, operatorId: operatorId},
            success: function (response) {
                var data = jQuery.parseJSON(response);
                $(".turnOn").html(data.turnOn);
                $(".turnOff").html(data.turnOff);
                $(".running").html(data.runningTime);
                $(".startReading").html(data.startReading);
                $(".endReading").html(data.endReading);
                $(".dailyConsumption").html(data.dailyConsumption);
                $(".bg-yellow").show();
                $(".bg-red").show();
            }
        });
    });
});
</script>

the problem is that it is fetching only one record and I have used while loop to iterate through all records which are in MySQL table but it is only fetching only one record

What does it means - the same? What do you see in a console? If you have array of items - you need to iterate over them. — u_mulder, Mar 28 '18 at 09:28
Your script is wide open to [SQL Injection Attack](http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php) Even [if you are escaping inputs, its not safe!](http://stackoverflow.com/questions/5741187/sql-injection-that-gets-around-mysql-real-escape-string) Use [prepared parameterized statements](http://php.net/manual/en/mysqli.quickstart.prepared-statements.php) — RiggsFolly, Mar 28 '18 at 10:00

score 0 · Answer 1 · answered Mar 28 '18 at 09:27

In your while loop you are assigning the variable. So every loop it overides the values. You must use array_push

$postData = array();
while($result=mysqli_fetch_array($query)){
$turnOn=$result['startTime'];
$turnOff=$result['endTime'];
$datetime1 = new DateTime($turnOn);
$datetime2 = new DateTime($turnOff);
$interval = $datetime1->diff($datetime2);
$datedifference=$interval->format('%Y-%m-%d %H:%i:%s');
$startReading=$result['startReading'];
$endReading=$result['endReading'];
$dailyConsumption=$endReading-$startReading;
  array_push($postData,array(
    "turnOn" => $turnOn,
    "turnOff" => $turnOff,
    "runningTime"=>$datedifference,
    "startReading"=>$startReading,
    "endReading"=>$endReading,
    "dailyConsumption"=>$dailyConsumption
    ));
  }

how to parse this kind of array in json – hotshot code Mar 28 '18 at 09:33 — hotshot code, Mar 28 '18 at 09:33
how to parse it into jquery – hotshot code Mar 28 '18 at 09:49 — hotshot code, Mar 28 '18 at 09:49

score 0 · Answer 2 · edited Mar 28 '18 at 09:43

0

You just need to define an array.

<?php
include 'config/dbconfig.php';

$postData = array();
$genid=$_POST['id'];
$operatorId=$_POST['operatorId'];

$query=mysqli_query($con,"SELECT * FROM generatorrun WHERE generatorId='$genid' AND operatorId='$operatorId'");

while($result = mysqli_fetch_array($query)) {

    $turnOn=$result['startTime'];
    $turnOff=$result['endTime'];
    $datetime1 = new DateTime($turnOn);
    $datetime2 = new DateTime($turnOff);
    $interval = $datetime1->diff($datetime2);
    $datedifference=$interval->format('%Y-%m-%d %H:%i:%s');
    $startReading=$result['startReading'];
    $endReading=$result['endReading'];
    $dailyConsumption=$endReading-$startReading;

    $postData[] = array(
        "turnOn" => $turnOn,
        "turnOff" => $turnOff,
        "runningTime"=>$datedifference,
        "startReading"=>$startReading,
        "endReading"=>$endReading,
        "dailyConsumption"=>$dailyConsumption
    );
}

echo json_encode($postData);
?>

edited Mar 28 '18 at 09:43

pravindot17

1,199
1
15
32

answered Mar 28 '18 at 09:28

Devendra Choudhary

32
7

json parsing is empty from this method – hotshot code Mar 28 '18 at 09:30
i am getting empty json – hotshot code Mar 28 '18 at 09:30
use document.write(response); for test the response – Devendra Choudhary Mar 28 '18 at 09:33
Can i use alert? – hotshot code Mar 28 '18 at 09:34
you also need to use for loop inside the query – Devendra Choudhary Mar 28 '18 at 09:34
can you please make a solution – hotshot code Mar 28 '18 at 09:35
can you give me your json response ? – Devendra Choudhary Mar 28 '18 at 09:40
you need like this for loop and check the result first. for(var i=0;i – Devendra Choudhary Mar 28 '18 at 09:42
the problem is inside the jquery parsing i think – hotshot code Mar 28 '18 at 09:48
yes you just need the check how to handle json response by using loop – Devendra Choudhary Mar 28 '18 at 09:50

score 0 · Answer 3 · answered Mar 28 '18 at 09:45

try this:

include 'config/dbconfig.php';
$genid      = $_POST['id'];
$operatorId = $_POST['operatorId'];
$query = mysqli_query($con, "SELECT * FROM generatorrun WHERE generatorId='$genid' AND operatorId='$operatorId'");
$postData = array();
while($result = mysqli_fetch_array($query))
{

$turnOn           = $result['startTime'];
$turnOff          = $result['endTime'];
$datetime1        = new DateTime($turnOn);
$datetime2        = new DateTime($turnOff);
$interval         = $datetime1->diff($datetime2);
$datedifference   = $interval->format('%Y-%m-%d %H:%i:%s');
$startReading     = $result['startReading'];
$endReading       = $result['endReading'];
$dailyConsumption = $endReading - $startReading;

$postData[] = array( // this is you missed here
        "turnOn"           => $turnOn,
        "turnOff"          => $turnOff,
        "runningTime"      => $datedifference,
        "startReading"     => $startReading,
        "endReading"       => $endReading,
        "dailyConsumption" => $dailyConsumption,
);
}

It will give you multiple results sets.

How to encode php array in json and parse into jquery

3 Answers3