Jump to content

Grouping Data Twice with Query to MYSQL with PHP


Lawrence

Recommended Posts

Hello,
 
I am querying a Mysql Database and displaying the results in a table. I need the results to Group twice and show totals.
 
What I need is the results to group by the employee name, then under that name group by the date field. Then at the end of each date total up four of the columns that contain numbers.
 
So the output would look like this:
 
    Employee Name
    Date
    
    Description     Job      Activity    Comments    Hours    OT     Travel     OT
    XXXX            XXXXX    XXXXXX      XXXXXX      3         2      1          1
    XXXX            XXXXX    XXXXXX      XXXXXX      2         0      0          1
    XXXX            XXXXX    XXXXXX      XXXXXX      1         0      1          1
    
    Totals                                           6         2      2          3
 
    Date
    
    Description     Job      Activity    Comments    Hours    OT     Travel     OT
    XXXX            XXXXX    XXXXXX      XXXXXX      3         2      1          1
    XXXX            XXXXX    XXXXXX      XXXXXX      2         0      0          1
    XXXX            XXXXX    XXXXXX      XXXXXX      1         0      1          1
    
    Totals                                           6         2      2          3
 
    Date
    
    Description     Job      Activity    Comments    Hours    OT     Travel     OT
    XXXX            XXXXX    XXXXXX      XXXXXX      3         2      1          1
    XXXX            XXXXX    XXXXXX      XXXXXX      2         0      0          1
    XXXX            XXXXX    XXXXXX      XXXXXX      1         0      1          1
    
    Totals                                           6         2      2          3
    
    
    Employee Name
    Date
    
    Description     Job      Activity    Comments    Hours    OT     Travel     OT
    XXXX            XXXXX    XXXXXX      XXXXXX      3         2      1          1
    XXXX            XXXXX    XXXXXX      XXXXXX      2         0      0          1
    XXXX            XXXXX    XXXXXX      XXXXXX      1         0      1          1
    
    Totals                                           6         2      2          3
 
    Date
    
    Description     Job      Activity    Comments    Hours    OT     Travel     OT
    XXXX            XXXXX    XXXXXX      XXXXXX      3         2      1          1
    XXXX            XXXXX    XXXXXX      XXXXXX      2         0      0          1
    XXXX            XXXXX    XXXXXX      XXXXXX      1         0      1          1
    
    Totals                                           6         2      2          3
 
etc....
 
My code so far, I can not figure how to do the second grouping by date and put the totals in.
mysql_select_db("time", $con);
    
    $result = mysql_query("SELECT * FROM data WHERE labor_date BETWEEN '$start' AND '$end' Group By user_name, labor_date Order by last_name ASC");
    
    if (mysql_num_rows($result)==0) {
        echo "[warning_box]There are not any hours recorded for the time period that you chose. Please try again with a different set of dates.[/warning_box]
    ";
        
        }
    else {
    
    $current_user_name = false;
    while($row = mysql_fetch_array($result)) {
        // listing a new employee? Output the heading, start the table
        if ($row['user_name'] != $current_user_name) {
                if ($current_user_name !== false)
                    echo '</table>'; echo '[divider_padding]';// if we're changing employee, close the table
            echo '
                <h5>'.$row['last_name'].', '.$row['first_name'].'</h5>[minimal_table]
                <table>
                    <tr>
                    <th style="width:200px">Description</th>
                    <th style="width:75px" class="tableleft">Job</th>
                    <th style="width:75px" class="tableleft">Activity</th>
                    <th style="width:290px" class="tableleft">Comments</th>
                    <th style="width:64px" class="tableright">Hours</th>
                    <th style="width:64px" class="tableright">OT</th>
                    <th style="width:64px" class="tableright">Travel</th>
                    <th style="width:63px" class="tableright">TOT</th>
                    
                    </tr>'
            ;
                $current_user_name = $row['user_name'];
        }
        // output the row of data
        echo '<tr>
            <td>'.$row['description'].'</td>
            <td class="tableleft">'.strtoupper($row['job']).'</td>
            <td class="tableleft">'.$row['activity'].'</td>
            <td class="tableleft">'.$row['comments'].'</td>
            <td class="tableright">'.$row['rthours'].'</td>
            <td class="tableright">'.$row['othours'].'</td>
            <td class="tableright">'.$row['trthours'].'</td>
            <td class="tableright">'.$row['tothours'].'</td>
           
            </tr>
        ';
    }
    echo '</table>[/minimal_table]'; // close the final table


I am really stuck,

 

Any help would be greatly appreciated!!!!

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.