英文:
PHP: How to convert multicolumn sql query result into array
问题
I've converted the provided code and table into Chinese. Here's the translated code and table:
我有一个SQL数据库表格，看起来像这样：
| 登录 | 开始时间 |
| --- | --- |
| 名字1 | 17:30:00 |
| 名字2 | 18:15:00 |
| 名字3 | 16:30:00 |
| 名字2 | 17:30:00 |
我想将其转换为一个数组，其中小时作为ID与所有对应于该小时的登录相关联：
```php
数组 (
[ 18:15:00 ] => 数组 ( [登录] => 名字2 )
[ 17:30:00 ] => 数组 ( [登录] => (名字1, 名字3) )
[ 16:30:00 ] => 数组 ( [登录] => 名字3 )
)
我尝试了类似这样的东西：
if ($result && $result->num_rows > 0) {
while($row = $result->fetch_assoc()) {
$aReturn[$row['time_start']] = array(
'login' => $row['login']
);
}
}
但它只返回了3行，每行只有一个登录：
数组 (
[16:30:00] => 数组 ( [登录] => 名字3 )
[17:30:00] => 数组 ( [登录] => 名字1 )
[18:15:00] => 数组 ( [登录] => 名字2 )
)
<details>
<summary>英文:</summary>
I've got sql database table which looks like this:
| login | time_start |
| --- | --- |
| name1 | 17:30:00 |
| name2 | 18:15:00 |
| name3 | 16:30:00 |
| name2 | 17:30:00 |
And i want to convert it into array with hour as id associated with all logins coresponding to that hour:
Array (
[ 18:15:00 ] => Array (
[ 17:30:00 ] => Array (
[ 16:30:00 ] => Array (
)
---
I tried something like this:
if ($result && $result->num_rows > 0) {
while($row = $result->fetch_assoc()) {
$aReturn[$row['time_start']] = array(
'login' => $row['login']
);
}
}
---
but it returned only 3 rows with only one login:
Array (
[16:30:00] => Array (
[17:30:00] => Array (
[18:15:00] => Array (
)
</details>
# 答案1
**得分**: 1
这将做到
```php
if ($result && $result->num_rows > 0) {
while($row = $result->fetch_assoc()) {
$aReturn[substr($row['time_start'], 0, 2)]['login'][] = $row['login'];
}
}
你可以为了清晰度而这样做...
if ($result && $result->num_rows > 0) {
while($row = $result->fetch_assoc()) {
if (!isset($aReturn[$hour = substr($row['time_start'], 0, 2)])) {
$aReturn[$hour] = ['login' => []];
}
$aReturn[$hour]['login'][] = $row['login'];
}
}
但在 PHP 中，这并不是功能上必要的。
英文:
This will do it
if ($result && $result->num_rows > 0) {
while($row = $result->fetch_assoc()) {
$aReturn[substr($row['time_start'], 0, 2)]['login'][] = $row['login'];
}
}
you could do this for clarity ...
if ($result && $result->num_rows > 0) {
while($row = $result->fetch_assoc()) {
if (!isset($aReturn[$hour = substr($row['time_start'], 0, 2)])) {
$aReturn[$hour] = ['login'=>[]];
}
$aReturn[$hour]['login'][] = $row['login'];
}
}
but it's not functionally necessary in php
答案2
得分: 0
// 获取将时间转换为小时的SQL
$sql = 'SELECT HOUR(time_start) as hour, login';
$results = $con->query($sql);
$allRows = $result->fetch_all();
foreach ($allRows as $row) {
$aReturn[$row['hour']]['login'][] = $row['login'];
}
结果应该是：
Array
(
[17] => Array
(
此处为隐藏的内容
注册登录后，方可查看
登录
=> Array ( [0] => name1, [1] => name4 ) )
[18] => Array
(
此处为隐藏的内容
注册登录后，方可查看
登录
=> Array( [0] => name2 ) )
[16] => Array
(
此处为隐藏的内容
注册登录后，方可查看
登录
=> Array ( [0] => name3 ) )
)
或者，如果您没有打算仅将时间减少到小时：
while ($row = $result->fetch_assoc()) {
$aReturn[$row['time_start']]['login'][] = $row['login'];
}
请注意，已删除 IF，因为如果没有要执行的操作，while 循环将不执行任何操作，这与使用 IF 相同。
英文:
//get the SQL to convert the time into the hour
$sql = 'SELECT HOUR(time_start) as hour, login';
$results = $con->query($sql);
$allRows = $result->fetch_all();
foreach ($allRows as $row) {
$aReturn[ $row['hour'] ]['login'][] => $row['login'];
}
RESULT should be like
Array
(
[17] => Array
(
此处为隐藏的内容
注册登录后，方可查看
登录
=> Array ( [0] => name1, [1] => name4 ) )
[18] => Array
(
此处为隐藏的内容
注册登录后，方可查看
登录
=> Array( [0] => name2 ) )
[16] => Array
(
此处为隐藏的内容
注册登录后，方可查看
登录
=> Array ( [0] => name3 ) )
)
OR, if you didnt mean you wanted to reduce the Time to only the Hour then simply
while($row = $result->fetch_assoc()) {
$aReturn[ $row['time_start'] ]['login'][] = $row['login'];
}
Note removed the IF as if there is nothing to do the wjile loop will do nothing which is the same result as with the IF there.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论