How to create JSON from a executed statement?

Question

I just wrote basic PHP code that looks like this:

$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 table waypoints of the sql table look like this:

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

Therefore, I would like to convert the array to JSON format. It sounds pretty simple, but my PHP code was created like this:

 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"}] 

This is not what I would like to have. All variables are duplicated right now (once: name, second time: index). Is there a way to get variables by name because I don't want every object to be twice in my array and JSON object.

If you have further questions, let me know.

Answer 1

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

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

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