Select records between time in two columns

[shhh]

  <?php

                    $con = mysql_connect("localhost","root","");
                    if (!$con)
                         {
                             die('Could not connect: ' . mysql_error());
                         }

                            mysql_select_db("ms", $con);
                            $time1 = $row['timein'];
                            $time2 = $row['timeout'];

                            $sql = mysql_query("SELECT employees.*, timetable.*  FROM employees INNER JOIN timetable ON
                                                 employees.id = timetable.id WHERE timetable.in_time BETWEEN employees.timein AND employees.timeout ");

                     echo "<table width='734'>
                        <tr>
                            <th width='49' scope='col'>Employee ID</th>
                            <th width='49' scope='col'>Lastname</th>
                            <th width='49' scope='col'>Firstname</th>
                            <th width='49' scope='col'>Department</th>
                            <th width='49' scope='col'>Time In</th>
                            <th width='49' scope='col'>Time Out</th>
                        </tr>";

                     while ($row = mysql_fetch_array($sql))
                         {  

                        echo "<tr>";
                             echo "<td class='align-center'>&nbsp" . $row['id'] . "&nbsp</td>";
                             echo "<td>&nbsp" . $row['Lastname'] ."&nbsp</td>";
                             echo "<td>&nbsp" . $row['Name'] . "&nbsp</td>";
                             echo "<td>&nbsp" . $row['Department'] . "&nbsp</td>";
                             echo "<td class='align-center'>&nbsp" . $row['in_time'] . "&nbsp</td>";
                             echo "<td class='align-center'>&nbsp" . $row['out_time'] . "&nbsp</td>";
                        echo "</tr>";

                        };
echo "</table>";

mysql_close($con);
?>

[LastMitch]

@shhh

Can you post your table? So I can see what you have. The code you provide is a JOIN but having to see the actually table will make more sense!

[shhh]

timetable

id | date | in_time | out_time
C003 2012-11-14 2012-11-14 9:17:18 2012-11-14 9:17:18

id Name Middlename Lastname Email Homeaddress Birthdate Contactnumber Department username Password Profileimage timein timeout branchcode C003 Kevin Dagdayan Lee [e-mail] Danube 116 Riverfront Pasig City 1990-02-11 09244587236 MIS pinkpapi cGFzc3dvcmQ= 9.png 08:00:00 am 11:00:00 am 0003 Edit Delete C002 Harold San Francisco [e-mail] Calooocan City 1992-06-29 09275618930 Bonding haroldo cGFzc3dvcmQ= 4.png 10:00:00 am 11:00:00 am 0002 Edit Delete C001 Maria Celine Guevara Cinco [e-mail] 13th Street Northgate Village New Manila Quezon Ci... 1992-11-21 09158368338 MIS celine21 Z29kc2xvdmU=

[shhh]

i need it to display late employees ~ Thank you

[urtrivedi]

what is in time, is it same for all or is it diffent for all

[shhh]

@urtrivedi - different for all

[urtrivedi]

This query shows all record who come after their standard in time.

                       $sql = mysql_query("SELECT employees.*, timetable.*  FROM employees INNER JOIN timetable ON
                                             employees.id = timetable.id WHERE timetable.in_time > str_to_date(concat(timetable.date,' ',employees.timein)  ,'%Y-%m-%d %r')");

[shhh]

@urtrivedi - no result

[LastMitch]

@shhh

Are connected to the database?

[shhh]

@lastmitch - yes ~ i want to select employees whos late .but each employees has a different default time on time in .

[LastMitch]

i want to select employees whos late .but each employees has a different default time on time in .

urtrivedi - wrote the query example because you said different for all and it made sense but now it's no result and now you want employees whos late?

Can you just add another row called late then you can JOIN timeout and timein? Right now, it's very complicated because how can you put late on an employee base on your code? You need to create if & else statement and another query to do select the employee who is late.

[urtrivedi]

post few reocordds from employee and few from timetable(where some ar lare and some are not late)

do not past combine query result, just paste 2 separte table records.