How to combine multiple SQL queries into one to ou

问题:

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.

回答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.