How do I group rows with same time from mysql query?_问答_开发者

How do I group rows with same time from mysql query?

开发者 https://www.devze.com 2023-03-26 00:48 出处：网络

I have the following code: //set $message and run database query $message .= \'<body style=\"margin: 0;\">\';

相关专题：php while-loop

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

johndoe@johndoeco.com

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

johndoe@johndoeco.com

800-555-000开发者_开发知识库0

*1:30pm

*Video 1

*1:47pm

*Video 2

*Video 3

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

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

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.

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>';

}