问题描述
我目前已设置了下表:
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
JSON输出看起来像这样:
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
}
}
}
由于我仍在学习PHP，因此我通过对数据库进行3次查询来编写一些草率的代码，每次选择一天分别为1、2和3的所有数据.
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.
下面是一个代码示例，该代码用于获取day = 0的数据，并在day = 1和day = 2时重复该操作:
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.
我开始做这样的事情:
$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.
推荐答案
SQL:
$sql = "SELECT * FROM table ORDER BY Day";
进一步了解代码:
$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...
您可能不同意我的观点，但我不认为用item0，item等索引项目...或day0，day1 ...是一个好主意.我个人更希望循环遍历结果是:
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.
这篇关于如何将多个SQL查询合并为一个以PHP代码形式输出为JSON?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！