How to get data in array format in from php mysql database

Question

I have the following function in PHP, I am trying to get array in a specific format mentioned below but to no success, I am selecting a specific column in the mysql table than contains a json value like

{ "lat": "-0.008668899503063161", "lng": "0.0025903204871857714" }

This is the function

 function test() { $pdo = new PDO ("mysql:host=localhost;dbname=dbname","user", "secret"); $statement=$pdo->prepare("SELECT test FROM merchants WHERE user_id = 1"); $statement->execute(); $data = array(); while( $row = $statement->fetch(PDO::FETCH_ASSOC) ) { $data[] = json_decode( $row['test'] ); } return $data ; }

I am expecting the following result

['lat' => -0.008668899503063161,'lng' => 0.0025903204871857714,]

But this is what I keep getting

[{"lat":"-0.008668899503063161","lng":"0.0025903204871857714"}]

You're looping over all the results of the query. You get a nested object for each row. — Barmar
– Barmar, Commented Jul 27, 2021 at 18:01
What do you expect the result to be if the query returns multiple rows? — Barmar
– Barmar, Commented Jul 27, 2021 at 18:02
I have only a single item in the database table that i want to get — Brian Mutiso
– Brian Mutiso, Commented Jul 27, 2021 at 18:05
Then why are you looping and pushing onto an array? Fetch the row and decode it. — Barmar
– Barmar, Commented Jul 27, 2021 at 18:07

Barmar · Accepted Answer · 2021-07-27 18:09:11Z

Don't push onto an array if you just want a single value.

function test() { $pdo = new PDO ("mysql:host=localhost;dbname=dbname","user", "secret"); $statement=$pdo->prepare("SELECT test FROM merchants WHERE user_id = 1"); $statement->execute(); row = $statement->fetch(PDO::FETCH_ASSOC) ) if ($row) { $data = json_decode( $row['test'], true ); } else { $data = null; } return $data ; }

Brian Mutiso · Accepted Answer · 2021-07-27 18:44:38Z

I managed to do it like this

 'merchants.location' => [ 'lat' => $data['lat'], 'lng' => $data['lng'] ]

Collectives™ on Stack Overflow

How to get data in array format in from php mysql database

2 Answers 2

Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Related