如何对 mysql 查询中具有相同时间的行进行分组？

发布于 2024-11-27 23:01:28 字数 2605 浏览 0 评论 0原文

我有以下代码：

//set $message and run database query
$message .= '<body style="margin: 0;">';
$message .= '<div style="padding: 0 20px;">';
$message .= '<span style="display: block; font-size: 22px; font-weight: bold; margin: 25px 0 -15px 0;"> User Activity on ' . $website . ' for ' . $yesterday . '</span>';


//query the database for today's history
$result = mysql_query("SELECT * FROM user_history WHERE date = '$yesterday' ORDER by name, time, title") 
or die(mysql_error());
$old_user = '';   

    while($row = mysql_fetch_array( $result )) {
        $new_user = $row['uid'];
        if ($new_user != $old_user) {
            $message .= '<br /><br /><hr />' . '<span style="font-size: 18px; font-weight: bold;">' . $row['name'] . '</span>' . '<br />' . $row['company'] . '<br />' . $row['email'] . '<br />' . $row['phone'] . '<br /><br />';
            $old_user = $new_user;
        }
        $message .= '<ul><li>' . $row['time'] .
    '<ul><li>' . $row['title'] . ' (<a href="' . $row['url'] . '">' . $row['url'].     '</a> )' . '</li></ul>' . '<br /><br />' .
    '</li></ul>';
    }  


//if no user activity, simply say so
if($new_user == "") {
    $message .= '<br /><br /><hr /><span style="font-size: 18px; font-weight: bold;">No user activity to report</span>';
}

$message .= '</div>';


$message .= '<div style="position: fixed; bottom: 0; height: 40px; width: 100%; margin: 20px 0 0 0; background: #000; color: #FFF; line-height: 40px; padding: 0 20px;">' .
'Report generated ' . $date . ' ' . $time .
'</div>';

$message .= '</body>';

它在邮件功能中运行良好。输出电子邮件组如下所示...

John Doe
无名氏公司
[电子邮件受保护]
800-555-0000

*1:30pm
*视频 1

*1:47pm
*视频 2

*1:47pm
*视频 3

================================

相反，我会喜欢这样输出：

John Doe
无名氏公司
[电子邮件受保护]
800-555-0000

*1:30pm
*视频 1

*下午 1:47
*视频2
*视频 3

===============================

有人可以吗请告诉我如何实现这一点？谢谢！

原文

I have the following code:

//set $message and run database query
$message .= '<body style="margin: 0;">';
$message .= '<div style="padding: 0 20px;">';
$message .= '<span style="display: block; font-size: 22px; font-weight: bold; margin: 25px 0 -15px 0;"> User Activity on ' . $website . ' for ' . $yesterday . '</span>';


//query the database for today's history
$result = mysql_query("SELECT * FROM user_history WHERE date = '$yesterday' ORDER by name, time, title") 
or die(mysql_error());
$old_user = '';   

    while($row = mysql_fetch_array( $result )) {
        $new_user = $row['uid'];
        if ($new_user != $old_user) {
            $message .= '<br /><br /><hr />' . '<span style="font-size: 18px; font-weight: bold;">' . $row['name'] . '</span>' . '<br />' . $row['company'] . '<br />' . $row['email'] . '<br />' . $row['phone'] . '<br /><br />';
            $old_user = $new_user;
        }
        $message .= '<ul><li>' . $row['time'] .
    '<ul><li>' . $row['title'] . ' (<a href="' . $row['url'] . '">' . $row['url'].     '</a> )' . '</li></ul>' . '<br /><br />' .
    '</li></ul>';
    }  


//if no user activity, simply say so
if($new_user == "") {
    $message .= '<br /><br /><hr /><span style="font-size: 18px; font-weight: bold;">No user activity to report</span>';
}

$message .= '</div>';


$message .= '<div style="position: fixed; bottom: 0; height: 40px; width: 100%; margin: 20px 0 0 0; background: #000; color: #FFF; line-height: 40px; padding: 0 20px;">' .
'Report generated ' . $date . ' ' . $time .
'</div>';

$message .= '</body>';

It works beautifully in the mail function. The output email groups like this...

John Doe
John Doe's Company
[email protected]
800-555-0000

*1:30pm
*Video 1

*1:47pm
*Video 2

*1:47pm
*Video 3

==============================

Instead, I'd like to output like this:

John Doe
John Doe's Company
[email protected]
800-555-0000

*1:30pm
*Video 1

*1:47pm
*Video 2
*Video 3

=============================

Can someone please tell me how to make this happen? Thanks!

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

断爱 2024-12-04 23:01:28

只需使用与针对不同用户所做的相同技术 (if ($new_user != $old_user) {) 但针对时间值来跟踪当前时间值即可。

虽然

if ($new_user != $old_user) {
     //your existing code
     $old_time = null;
     $end_tag = false;
}

if ($old_time == null || $row['time'] != $old_time) {
    $old_time = $row['time'];
    $message .= '<ul><li>' . $row['time'];
    $end_tag = true;
}

$message .= '<ul><li>' . 
             $row['title'] . ' (<a href="' .
             $row['url'] . '">' . $row['url'].     '</a> )' .
            '</li></ul>';

if ($end_tag) {
    $message .= "</li></ul>";
    $end_tag = false;
}

这种方式编码很快就会变得丑陋，但应该将其分解为函数。

Just keep track of the current time value using the same technique that you are doing for distinct users (if ($new_user != $old_user) {) but for the time value.

Something like

if ($new_user != $old_user) {
     //your existing code
     $old_time = null;
     $end_tag = false;
}

if ($old_time == null || $row['time'] != $old_time) {
    $old_time = $row['time'];
    $message .= '<ul><li>' . $row['time'];
    $end_tag = true;
}

$message .= '<ul><li>' . 
             $row['title'] . ' (<a href="' .
             $row['url'] . '">' . $row['url'].     '</a> )' .
            '</li></ul>';

if ($end_tag) {
    $message .= "</li></ul>";
    $end_tag = false;
}

Though coding this way starts to get ugly fast and should be split up into functions.

回复收藏 0 原文

捶死心动 2024-12-04 23:01:28

这有帮助吗？

$old_time = '';
$video_cache = '';
while($row = mysql_fetch_array( $result ))
{
    $new_user = $row['uid'];
    if ($new_user != $old_user)
    {
        $message .= '<br /><br /><hr />' . '<span style="font-size: 18px; font-weight: bold;">' . $row['name'] . '</span>' . '<br />' . $row['company'] . '<br />' . $row['email'] . '<br />' . $row['phone'] . '<br /><br />';
        $old_user = $new_user;
    }
    $new_time = $row['time'];
    if ($old_time != $new_time)
    {
        $video_cache = '<ul><li>' . $old_time . '</li>' . $video_cache . '</ul>';
        $message .= $video_cache;
        $video_cache = '';
    }

    $video_cache .= '<li>' . $row['title'] . ' (<a href="' . $row['url'] . '">' . $row['url'].     '</a> )' . '</li>';

}

Does this help?

$old_time = '';
$video_cache = '';
while($row = mysql_fetch_array( $result ))
{
    $new_user = $row['uid'];
    if ($new_user != $old_user)
    {
        $message .= '<br /><br /><hr />' . '<span style="font-size: 18px; font-weight: bold;">' . $row['name'] . '</span>' . '<br />' . $row['company'] . '<br />' . $row['email'] . '<br />' . $row['phone'] . '<br /><br />';
        $old_user = $new_user;
    }
    $new_time = $row['time'];
    if ($old_time != $new_time)
    {
        $video_cache = '<ul><li>' . $old_time . '</li>' . $video_cache . '</ul>';
        $message .= $video_cache;
        $video_cache = '';
    }

    $video_cache .= '<li>' . $row['title'] . ' (<a href="' . $row['url'] . '">' . $row['url'].     '</a> )' . '</li>';

}

回复收藏 0 原文

~没有更多了~