split one column to multi columns

[abdu808]

 My datebase structure like
User_Id | UserName | Date | Time
 

I would show result like
User_Id | UserName | Date | In | Leave | Back IN | Out 

In = time  6-8 am

Leave = time 12 pm

Back in = time  5 pm

Out= time 8 pm

[requinix]

Don't rely on the time ranges (presumably 6-8, 8-12, 12-5, 5- to know what they did. What if someone clocked out for lunch after noon? What if they needed to leave early, like 3pm, for a doctor's appointment? What if they came in late, like 9am, or had to work late, like until 10pm?

Edited May 8, 2015 by requinix

[jcbones]

 

$sql = "SELECT User_Id,Username, Date, Time FROM $table WHERE `Date` = CURDATE() ORDER BY User_Id"; //query
$table = '<table>
                <tr>
                    <th>User_Id</th>
                    <th>Username</th>
                    <th>Date</th>
                    <th>In</th>
                    <th>Leave</th>
                    <th>Back IN</th>
                    <th>Out</th>
               </tr>'; //table headers.
$lastId = NULL; //set variable, default value null.
foreach($pdo->query($sql,PDO::FETCH_ASSOC) as $row) { loop through the query.
 if($lastId != $row['User_Id']) { //if the last id doesn't match the new id.
  if($lastId != NULL) { $table .= '</tr>'; } //but the last id isn't null, close the row.
  $table .= '<tr><td>' . $row['User_Id'] . '</td><td>' . $row['Username'] . '</td><td>' . $row['Date'] . '</td>'; //start a new row, with standard info.
 }
 $table .= '<td>' . $row['Time'] . '</td>'; //then show the time, Hope there isn't more than 5 rows.
 $lastId = $row['User_Id']; Change lastId to current ID.
} //end loop.
$table .= '</tr></table>'; //end table.
 
 echo $table; //print table to screen.

[abdu808]

Thanks   

requinix---- jcbones

 

output code not what i need it  get the last date in of employ  and loop all time into

 

 

 

can i group id and loop date with time like >>>  i hope it is clear now 

 

[Barand]

 I would show result like

User_Id | UserName | Date | In | Leave | Back IN | Out 

Now, you apparently want

User_Id | UserName | Date | In | Leave

 

Which?

[Barand]

Alternative approach

 

The data

mysql> SELECT * FROM clock;
+----+-------+-----------+------------+----------+
| id | empid | name      | dated      | datet    |
+----+-------+-----------+------------+----------+
|  1 |    37 | Employee1 | 2015-05-04 | 08:20:00 |
|  2 |    37 | Employee1 | 2015-05-05 | 13:00:00 |
|  3 |    39 | Employee2 | 2015-05-04 | 12:00:00 |
|  4 |    37 | Employee1 | 2015-05-05 | 16:30:00 |
|  5 |    39 | Employee2 | 2015-05-05 | 18:00:00 |
|  6 |    37 | Employee1 | 2015-05-05 | 08:00:00 |
|  7 |    37 | Employee1 | 2015-05-04 | 17:00:00 |
|  8 |    39 | Employee2 | 2015-05-04 | 08:00:00 |
|  9 |    37 | Employee1 | 2015-05-04 | 13:30:00 |
| 10 |    39 | Employee2 | 2015-05-04 | 13:00:00 |
| 11 |    37 | Employee1 | 2015-05-04 | 12:30:00 |
| 12 |    39 | Employee2 | 2015-05-04 | 17:00:00 |
| 13 |    39 | Employee2 | 2015-05-05 | 07:30:00 |
| 14 |    39 | Employee2 | 2015-05-05 | 11:30:00 |
| 15 |    39 | Employee2 | 2015-05-05 | 12:45:00 |
| 16 |    37 | Employee1 | 2015-05-05 | 12:15:00 |
| 17 |    40 | Employee3 | 2015-05-04 | 08:00:00 |
| 18 |    40 | Employee3 | 2015-05-04 | 13:30:00 |
| 19 |    40 | Employee3 | 2015-05-05 | 09:00:00 |
| 20 |    40 | Employee3 | 2015-05-05 | 14:00:00 |
+----+-------+-----------+------------+----------+

The query

SELECT name as Name
    , dated as Date
    , MAX(t1) as `In`
    , MAX(t2) as `Out`
    , MAX(t3) as `BackIn`
    , MAX(t4) as `Leave`
FROM
    (
    SELECT
        @N := IF(name=@name AND dated=@dated,@N+1,0)
       ,IF(@N=0, datet, '') as t1
       ,IF(@N=1, datet, '') as t2 
       ,IF(@N=2, datet, '') as t3 
       ,IF(@N=3, datet, '') as t4
       ,@name:=name as name
       ,@dated:=dated as dated
    FROM clock
         JOIN (SELECT @N:=NULL,@name:=NULL,@dated:=NULL) as init
    ORDER BY name, dated, datet   
    ) as times
GROUP BY name, date;

Output

+-----------+------------+----------+----------+----------+----------+
| Name      | Date       | In       | Out      | BackIn   | Leave    |
+-----------+------------+----------+----------+----------+----------+
| Employee1 | 2015-05-04 | 08:20:00 | 12:30:00 | 13:30:00 | 17:00:00 |
| Employee1 | 2015-05-05 | 08:00:00 | 12:15:00 | 13:00:00 | 16:30:00 |
| Employee2 | 2015-05-04 | 08:00:00 | 12:00:00 | 13:00:00 | 17:00:00 |
| Employee2 | 2015-05-05 | 07:30:00 | 11:30:00 | 12:45:00 | 18:00:00 |
| Employee3 | 2015-05-04 | 08:00:00 | 13:30:00 |          |          |
| Employee3 | 2015-05-05 | 09:00:00 | 14:00:00 |          |          |
+-----------+------------+----------+----------+----------+----------+

[abdu808]

So greet it's working 

 

thanks Barand