mysql grouping problem

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
invisibled
Forum Contributor
Posts: 112
Joined: Sun Apr 29, 2007 3:35 pm
Location: New Westminster

mysql grouping problem

Post 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;
 
User avatar
Sofw_Arch_Dev
Forum Commoner
Posts: 60
Joined: Tue Mar 16, 2010 4:06 pm
Location: San Francisco, California, US

Re: mysql grouping problem

Post 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.
invisibled
Forum Contributor
Posts: 112
Joined: Sun Apr 29, 2007 3:35 pm
Location: New Westminster

Re: mysql grouping problem

Post 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
 
JakeJ
Forum Regular
Posts: 675
Joined: Thu Dec 10, 2009 6:27 pm

Re: mysql grouping problem

Post by JakeJ »

Sure, as it loops through each user:

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