Page 1 of 1

mysql grouping problem

Posted: Wed Mar 17, 2010 5:15 pm
by invisibled
hey all,

I need to display a list of times a user has logged in. And i need it to display like this

User Name
time 1
time 2
time 3

Username
time 1
time 2
time 3

but right now my code (below) display's the data like this

User name
time 1

User name
time 2

can anybody help me out? here is code (if you need more info just ask):

Code: Select all

 
$sql = mysql_query(" SELECT 
                * 
                FROM timeLog 
                WHERE start BETWEEN '$start_year-$start_month-$start_day' AND '$end_year-$end_month-$end_day'
            ");
            while($row = mysql_fetch_assoc($sql)):
                
                //
                extract($row);
                
                //
                $sql2 = mysql_query(" SELECT realName FROM module_employees WHERE id = '$employ_id' ");
                $row2 = mysql_fetch_assoc($sql2);
                
                //
                if($end != '0000-00-00 00:00:00'){
                    print '<div class="entry">
                                <h4>'.$row2['realName'].'</h4>';
                                
                                //
                                $sql3 = mysql_query(" SELECT * FROM timeLog WHERE id = '$id'");
                                while($row3 = mysql_fetch_assoc($sql3)):
                                    print $row3['start'];
                                endwhile;
                                
                //              
                    print '</div>';
                }
                
            endwhile;
 

Re: mysql grouping problem

Posted: Wed Mar 17, 2010 5:57 pm
by Sofw_Arch_Dev
Working with your existing structure you could add an ordering to your SQL select...

Code: Select all

 
SELECT .... ORDER BY user_id
 
This would keep rows for a particular user grouped together in the result set. Then in the loop that prints out the markup

Code: Select all

 
$currentEmployeeId = 0;
while($row = mysql_fetch_assoc($sql)) {
    // get the employee id from the row as $thisEmployeeId
    if( $currentEmployeeId != $thisEmployeeId ) {
        $currentEmployeeId = $thisEmployeeId;
        // get and output the name
    } // if
   .. // output the time
} // while
 
Something like that perhaps.

Re: mysql grouping problem

Posted: Wed Mar 17, 2010 6:20 pm
by invisibled
that is fantastic! thank you so much. And just curious, do you have a good method of add'ing those times together? so instead of displaying each individual login time it displays the combined time of all the login's for each user.

my current code

Code: Select all

 
//
            $sql = mysql_query(" SELECT 
                id, user_id, employ_id, start, end, TIMEDIFF(start, end) AS time
                FROM timeLog 
                WHERE start BETWEEN '$start_year-$start_month-$start_day' AND '$end_year-$end_month-$end_day'
            ") or die(mysql_error());
            
            //
            $currentEmployeeId = 0;
            
            //
            print '<div class="entry">';
            while($row = mysql_fetch_assoc($sql)):
                extract($row);
                
                //
                $thisEmployeeId = $employ_id;
                
                //
                if( $currentEmployeeId != $thisEmployeeId ):
                    $currentEmployeeId = $thisEmployeeId;
                    $sql2 = mysql_query(" SELECT realName FROM module_employees WHERE id = '$thisEmployeeId' ");
                    $row2 = mysql_fetch_assoc($sql2);
                    
                    print '<h4>'.$row2['realName'].'</h4>';
                endif;
                
                //
                if($time != ''){
                    $time = str_replace('-', '', $time);
                    print '<span>'.$time.'</span>';
                }
                
            endwhile;
            print '</div>';
 
The Output

Code: Select all

 
Shan Robertson
00:00:13
00:00:07
00:00:02
 
Jaryn Hollowink
00:00:04
 

Re: mysql grouping problem

Posted: Wed Mar 17, 2010 8:40 pm
by JakeJ
Sure, as it loops through each user:

$sum_time = $sum_time + $time; //and output in the desired format.