How to combine multiple SQL queries into one to output as JSON in PHP code?

Question

I currently have the following table set up:

 StartTime   EndTime    Performer  Event   Day   Location 
 -----------------------------------------------------
  1:00pm      2:00pm     Test       Test    0     1
 11:00pm     12:00am     Test       Test    0     0
  2:00pm      2:30pm     Test       Test    1     0
 11:00pm     12:00am     Test       Test    2     1

The JSON output looks something like this:

{
    "day0": {
        "item1": {
            "StartTime": "1:00pm",
            "EndTime": "2:00pm",
            "Performer": "Test",
            "Event": "Test",
            "Location": 1
        },
        "item2": {
            "StartTime": "11:00pm",
            "EndTime": "12:00am",
            "Performer": "Test",
            "Event": "Test",
            "Location": 0
        }
    },
    "day1": {
        "item1": {
            "StartTime": "2:00pm",
            "EndTime": "2:30pm",
            "Performer": "Test",
            "Event": "Test",
            "Location": 0
        }
    },
    "day2": {
        "item1": {
            "StartTime": "11:00pm",
            "EndTime": "12:00am",
            "Performer": "Test",
            "Event": "Test",
            "Location": 1
        }
    }
}

Since I'm still learning PHP, I wrote some sloppy code by making 3 queries to the database, each time selecting all data where the day was 1, 2, and 3.

Here's an example of code for fetching data for day=0, which is repeated for day=1 and day=2:

echo '{ "day0" : {';

$sql = "select * from table WHERE day = 0";
$result = mysqli_query($connection, $sql) or die("Error in Selecting " . mysqli_error($connection));

$jsonData = array();
$rowCount = $result->num_rows;
$index = 1;

while($row =mysqli_fetch_assoc($result))
{
    echo '"item'.$index.'":';
    echo json_encode(array("StartTime" => $row['StartTime'], "EndTime" => $row['EndTime'], "Performer" => $row['Performer'], "Event" => $row['Event'], "Location" => intval($row['Location'])));

    if ($rowCount != $index)
    {
        echo ',';
    }
    ++$index;
}

echo ' }';

// Repeated code for day=1

// Repeated code for day=2

echo ' }';

I feel as though this can be achieved with just one query, but being that I'm new, I'm not sure how to implement it.

I started to do something like this:

$sql = "select * from table";

$result = mysqli_query($connection, $sql) or die("Error in Selecting " . mysqli_error($connection));

$jsonData = array();
$numOfRows = $result->num_rows;
$count = 1;
while($row = mysqli_fetch_assoc($result))
{
    $outerIndex = 'day'.$row['day'];

    if ($row['day'] == '1')
    {
        // Do something, not sure
    }

    if ( !isset( $jsonData[$outerIndex] ) )
    {            
        $innerIndex = 'item'.$count.'';


        $jsonData[$outerIndex][$innerIndex] = $row;
    }
    ++$count;
}

echo json_encode($jsonData);

However, I just got stuck, and not really sure how to approach it further.

@TimBiegeleisen: the exact same output as described, just cleaner code using one query — Pangu
– Pangu, Commented Jan 5, 2017 at 5:13
is that json output (on that format) really that necessary? I mean I think it would be better to have it on this format: [{day:0,items:[{StartTime:'11:00am',EndTime:'2pm'....},{StartTime:....}]},{day:1,items:[{}...]}] — barudo
– barudo, Commented Jan 6, 2017 at 2:04
@barudo I would like to have it the same output as my app code relies on that output to display data in my app — Pangu
– Pangu, Commented Jan 6, 2017 at 2:46

barudo · Accepted Answer · 2017-01-06 10:24:03Z

1

SQL:

$sql = "SELECT * FROM table ORDER BY Day";

Further down on code:

$result_object = [];
$item = 1;
while ($row = $result->fetch_assoc()) {
    if(isset($result_object['day'.$row['Day']]))
    {
        $result_object['day'.$row['Day']]['item'.$item] = $row;
        $item++;
    }
    else
    {
        $result_object['day'.$row['Day']]['item1'] = $row;
        $item = 2;
    }
}

You can then output it with:

echo json_encode($result_object, JSON_PRETTY_PRINT); //JSON_PRETTTY_PRINT is not necessary...

You may disagree with me, but I don't think indexing items with item0, item1 and so on.... or day0, day1 ... is a GOOD idea. I personally prefer that the looping through the result would be:

while ($row = $result->fetch_assoc()) {
    if(isset($result_object[$row['Day']]))
    {
        $result_object[$row['Day']]->items[] = $row;
    }
    else
    {
        $result_object[$row['Day']] = (object)['day'=>$row['Day'], 'items'=>[$row]];
    }

}

In this case, the result would be an array of objects. ie:

[
    {
        "day": "0",
        "items": [
            {
                "StartTime": "07:23:56",
                "EndTime": "17:24:04",
                "Performer": "Performer1",
                "Event": "Event1",
                "Day": "0",
                "Location": "1"
            },
            {
                "StartTime": "09:24:30",
                "EndTime": "01:04:37",
                "Performer": "Performer2",
                "Event": "Event2",
                "Day": "0",
                "Location": "1"
            }
        ]
    },
    {
        "day": "1",
        "items": [
            {
                 "StartTime": "10:25:22",
                 "EndTime": "11:25:29",
                 "Performer": "Performer2",
                 "Event": "Event3",
                 "Day": "1",
                 "Location": "2"
            }
         ]
     },
     {
         "day": "2",
         "items": [
             {
                "StartTime": "12:26:08",
                "EndTime": "13:26:12",
                "Performer": "Performer3",
                "Event": "Event4",
                "Day": "2",
                "Location": "1"
             }
        ]
    }
 ]

The reason: you can easily iterate through each values(an array) in whatever language you're going to use.

answered Jan 6, 2017 at 10:24

barudo

6651 gold badge4 silver badges13 bronze badges

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

2 Comments

Pangu Over a year ago

I would prefer the first answer, and the "format" of the output is what I like, however, it's missing a lot of items. I have multiple items for each day, but its only outputting 1 item for day0, 1 item for day1, and 1 item for day2

barudo Over a year ago

this is important: $sql = "SELECT * FROM table ORDER BY Day";

Collectives™ on Stack Overflow

How to combine multiple SQL queries into one to output as JSON in PHP code?

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related