Converting Sql database into JSON with proper formatting via PHP and sorting out values in the PHP script itself

Question

For building my Android App, I am currently converting my Wordpress SQL database into JSON.

Here's the script that I am currently using which I found on Stack Overflow here:

    <?php

$host = "localhost";
$user = "*********";
$password = "********";
$db = "********";

$sql = "select * FROM wp_postmeta WHERE meta_key = 'event_start_date' OR meta_key = 'event_end_date' OR meta_key = 'event_start_time' OR meta_key = 'event_end_time' OR meta_key = 'event_phone' OR meta_key = 'post_views_count' OR meta_key = 'event_address_longitude' OR meta_key = 'event_address_latitude' OR meta_key = 'event_address_zip' OR meta_key = 'event_address_address' ORDER BY post_id ASC";
$con = mysqli_connect($host,$user,$password,$db);

$result = mysqli_query($con,$sql);
$response = array();


while($row = mysqli_fetch_array($result)){
    array_push($response,array('post_id'=>$row[1],'meta_tag'=>$row[2],'meta_Value'=>$row[3]));

}


echo json_encode(array("server_response"=>$response));
mysqli_close($con);

?>

A small part of the result that I am getting is this:

{
    "post_id": "3726",
    "meta_tag": "event_start_date",
    "meta_Value": "11\/25\/2015"
}, {
    "post_id": "3726",
    "meta_tag": "event_start_time",
    "meta_Value": "08:00 PM"
}, {
    "post_id": "3726",
    "meta_tag": "event_end_date",
    "meta_Value": "11\/25\/2015"
}, {
    "post_id": "3726",
    "meta_tag": "event_end_time",
    "meta_Value": "11:00 PM"
}, {
    "post_id": "3726",
    "meta_tag": "event_address_address",
    "meta_Value": "Tippler On The Roof 276, 100 FEET ROAD, INDIRANAGAR"
}, {
    "post_id": "3726",
    "meta_tag": "event_address_zip",
    "meta_Value": "560038"
}, {
    "post_id": "3726",
    "meta_tag": "event_address_latitude",
    "meta_Value": "12.9782859"
}, {
    "post_id": "3726",
    "meta_tag": "event_address_longitude",
    "meta_Value": "77.63875669999993"
}, {
    "post_id": "3726",
    "meta_tag": "post_views_count",
    "meta_Value": "65"
}, {
    "post_id": "3727",
    "meta_tag": "event_address_address",
    "meta_Value": "F BAR & KITCHEN 18, ALI ASKAR ROAD OFF CUNNINGHAM ROAD"
}, {
    "post_id": "3727",
    "meta_tag": "event_address_zip",
    "meta_Value": "560001"
}, {
    "post_id": "3727",
    "meta_tag": "event_phone",
    "meta_Value": "9731110594"
}, {
    "post_id": "3727",
    "meta_tag": "event_address_latitude",
    "meta_Value": "12.9887093"
}, {
    "post_id": "3727",
    "meta_tag": "event_address_longitude",
    "meta_Value": "77.5937212"
}, {
    "post_id": "3727",
    "meta_tag": "post_views_count",
    "meta_Value": "54"
}, {
    "post_id": "3729",
    "meta_tag": "event_start_date",
    "meta_Value": "11\/25\/2015"
}, {
    "post_id": "3729",
    "meta_tag": "event_start_time",
    "meta_Value": "08:00 PM"
}, {
    "post_id": "3729",
    "meta_tag": "event_end_date",
    "meta_Value": "11\/25\/2015"
}, {
    "post_id": "3729",
    "meta_tag": "event_end_time",
    "meta_Value": "11:30 PM"
}, {
    "post_id": "3729",
    "meta_tag": "event_address_address",
    "meta_Value": "FUNKY VILLA 4TH FLOOR, NGV COMMERCIAL COMPLEX, KHB GAMES VILLAGE"
}, {
    "post_id": "3729",
    "meta_tag": "event_address_zip",
    "meta_Value": "560047"
}, {
    "post_id": "3729",
    "meta_tag": "post_views_count",
    "meta_Value": "77"
}, {
    "post_id": "3731",
    "meta_tag": "event_start_date",
    "meta_Value": "12\/19\/2015"
}, {
    "post_id": "3731",
    "meta_tag": "event_start_time",
    "meta_Value": "09:00 PM"

But this is not how I want my results to appear. Therefore, after fetching the array of results in my php script, I want to sort the values there itself and then pass it on in JSON format to my app.

As you can see, each piece right now contains,3 values: post_id, meta_tag and meta_value. So, there are two things that I want to do with it:

Combine all small arrays with common post_id into one.
Then, the 3 values that are appearing right now are not how I need them. Actually the value that meta_tag is showing should replace meta_tag and the value of meta_value should become its value.

To avoid the confusion, and put things into perspective, here's how I would like the above results to appear:

{
        "post_id": "3726",
        "event_start_date": "11\/25\/2015",
        "event_start_time": "08:00 PM"
        "event_end_date": "11\/25\/2015"
        "event_end_time": "11:00 PM"
        "event_address_address": "Tippler On The Roof 276, 100 FEET ROAD, INDIRANAGAR"
        "event_address_zip": "560038"
        "event_address_latitude": "12.9782859"
        "event_address_longitude": "77.63875669999993"
        "post_views_count": "65"
        }

I have shown the output Json example of one post_id. The same has to be done for all posts. I need a loop solution that can be used to cover all the posts in the database (and there are 100s of them).

Can you post a snippet of your database? Seems like you might need to normalise it if you have multiple info on different row for 1 id . — Matt
– Matt, Commented May 1, 2016 at 10:24

markt · Accepted Answer · 2016-05-01 10:54:07Z

1

You need to combine tables wp_posts and wp_postmeta:

select p.ID as post_id p,
(select meta_value from wp_postmeta where meta_tag='event_start_date' && post_id=p.ID) as event_start_date,
(select meta_value from wp_postmeta where meta_tag='event_start_time' && post_id=p.ID) as event_start_time,

...

from wp_posts

EXPLAINATION:

what you are trying to retrieve is a data set for each post, so your primary query is to wp_posts not wp_postmeta. You then want to add data to the row from various rows within wp_postmeta, so wp_postmeta is your secondary query.

edited May 1, 2016 at 10:54

answered May 1, 2016 at 10:40

markt

9037 silver badges21 bronze badges

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

6 Comments

D S AMAN Over a year ago

Hey Markt, Thanks for sparing some time to help me out with this. But I couldn't get it. Why combine tables from wp_posts when all these values are present in wp_postmeta?

markt Over a year ago

because the wp_posts table ties them all together with the post_id - then you can get them all in a single row

D S AMAN Over a year ago

Oh yes, I think that is a valid approach and the right direction to work towards. But the result that I am getting right now contains null values. Maybe a few changes will have to be made to make this thing work properly which it will.

markt Over a year ago

If the meta_tag doesn't exist for a particular post_id, then you will get null values. I would just write some php code to handle this.

D S AMAN Over a year ago

If the meta_tag doesn't exist for even a single post, then null values will be returned?

|

splash58 · Accepted Answer · 2016-05-01 11:08:01Z

1

while($row = mysqli_fetch_array($result)){
    if( ! isset($response[$row[1]])) 
        $response[$row[1]] = array('post_id'=>$row[1]);
    $response[$row[1]][$row[2]] = $row[3];
}
// to make index numeric
$response = array_value($response);

edited May 1, 2016 at 11:08

answered May 1, 2016 at 10:24

splash58

26.2k3 gold badges25 silver badges35 bronze badges

7 Comments

D S AMAN Over a year ago

Thanks for sparing your time to help me out splash58. But this code doesn't seem to work. I am getting "URL returned bad status code 500." I should add it after "while($row = mysqli_fetch_array($result)){ array_push($response,array('post_id'=>$row[1],'meta_tag'=>$row[2],'meta_Value'=>$row[3])); }" Right?

splash58 Over a year ago

just print_r resulting $responce to see structure of an array made.

D S AMAN Over a year ago

I am afraid its still not working. With print_r too the server is giving the 500 error.

splash58 Over a year ago

you can use print_r only for debuging - to watch the array structure. Not for production code

D S AMAN Over a year ago

Yes, I did it on my local Xampp server to test it out. It didn't work. Are you sure it will work?

|

Collectives™ on Stack Overflow

Converting Sql database into JSON with proper formatting via PHP and sorting out values in the PHP script itself

2 Answers 2

6 Comments

7 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

6 Comments

7 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related