Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

How to create JSON from executed prepared statement?

Tags:

json

sql

php

I just wrote some basic PHP code that looks like follows:

$pdo = new PDO("mysql:host=localhost;dbname=locationtracker", "xxxx", "xxxx");
$statement = $pdo->prepare("SELECT * FROM waypoints");
$result = $statement->execute();

if ($result){
    echo "Success";
    $resultArray = array();
    $tmpArray = array();

    while($row = $statement->fetch()){
        print_r($row);
        echo "<br>";
        $tmpArray = $row;
        array_push($resultArray, $tmpArray);
    }
    print_r(json_encode($resultArray));
}else{
    die("Error.<br>");
}

The sql table 'waypoints' looks like so:

waypoints
        | x: double
        | y: double
        | name: varchar(255)
        | city: varchar(255)
        | id: Int

So I would like to transform the array into JSON format. Sound quite simple, but my PHP code produced sth like that:

Success
Array ( [x] => 7.0000 [0] => 7.0000 [y] => 32.0000 [1] => 32.0000 [name] => Georgia [2] => Georgia [city] => Georgia [3] => Georgia [id] => 1 [4] => 1 ) 
Array ( [x] => 5.0000 [0] => 5.0000 [y] => 34.000 [1] => 34.000 [name] => Home [2] => Home [city] => St.Martin [3] => St.Martin [id] => 1 [4] => 1 ) 

[{"x":"7.0000","0":"7.0000","y":"32.0000","1":"32.0000","name":"Georgia","2":"Georgia","city":"Georgia","3":"Georgia","id":"1","4":"1"},{"x":"5.0000","0":"5.0000","y":"34.000","1":"34.000","name":"Home","2":"Home","city":"St.Martin","3":"St.Martin","id":"1","4":"1"}]

That's not what I would like to have. All the variables are duplicated right now (one time: name, second time: index). Is there a way to only get the variables by name because I don't want every object to be two times in my array and JSON object.

If you have any further questions, let me know.

like image 601
j3141592653589793238 Avatar asked Oct 29 '22 00:10

j3141592653589793238


1 Answers

By default PDO fetch returns an array indexed both numerically and associatively. You only want the associative index.

while($row = $statement->fetch(PDO::FETCH_ASSOC)){
  //...
}

See https://secure.php.net/manual/en/pdostatement.fetch.php#refsect1-pdostatement.fetch-parameters

like image 191
Jeff Puckett Avatar answered Nov 09 '22 09:11

Jeff Puckett