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

2

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 ;
}

answered Jul 27, 2021 at 18:09

Barmar

789k57 gold badges554 silver badges669 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

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

0

I managed to do it like this

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

        ]

answered Jul 27, 2021 at 18:44

Brian Mutiso

4108 silver badges12 bronze badges

Collectives™ on Stack Overflow

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

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related