How to create JSON from executed prepared statement?

Question

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.

Answer 1

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