Problem in NULL value

PHP
1

Hi…
i have table
EMP_NO
LOGIN - DATETIME, NULL
LOGOUT - DATETIME, NULL

I have this query:


<?php

include 'config.php';

$currentEmpID = $_SESSION['empID']; 
 
 if(!isset($_POST['submit_'])){ 
     
    $DATE1 = $_POST['firstinput'];
    $DATE2   = $_POST['secondinput'];
    
  
 $sql = "SELECT EMP_NO, LOGIN, LOGOUT  FROM employee_attendance WHERE DATE(LOGIN) BETWEEN '$DATE1' AND '$DATE2'";
    $attendance = $conn3->GetAll($sql);
   
    $smarty->assign('attendance', $attendance);

 }
 $smarty->display('header_att.tpl');
 $smarty->display('empAttendance.tpl');
 $smarty->display('footer.tpl');
  
?>

My problem is I have data
EMP_NO 00000223
LOGIN NULL
LGOUT NULL

this data was not displayed because it has a null value… i want it to displayed also and highlight it because it has a value null…

Thank you

2

I’ve revised your query…

$sql = "SELECT EMP_NO, LOGIN, LOGOUT  FROM employee_attendance WHERE LOGIN BETWEEN '$DATE1' AND '$DATE2 23:59:59' OR LOGIN IS NULL";

Two changes:

  1. Don’t apply a function (DATE) to the column because then it’s impossible for MySQL to apply any optimizations to the query. What I wrote is equivalent but allows use of an index if you define one, and the query cache if you enable it.

  2. To include the rows where the column is null, add OR LOGIN IS NULL.

3

Thank you…I used DATE because i need to get only the DATE part in LOgin…

LOGIN DATA - 2011-12-01 05:35:00
LOGOUT DATA - 2011-12-01 13:35:00
LOGIN DATA - 2011-12-02 5:35:00
LOGOUT DATA - 2011-12-02 3:35:00
LOGIN DATA - 2011-12-0305:35:00
LOGOUT DATA - 2011-12-0313:35:00

And also I need to highlight the data which has a NULL value.

I have this revised code:


if(!isset($_POST['Regsubmit_'])){ 
     
    $DATE1 = $_POST['Regfirstinput'];
    $DATE2   = $_POST['Regsecondinput'];


 $sql = "SELECT a.EMP_NO, CONCAT(LNAME, ', ' , FNAME, ' ', MI) AS FULLNAME, a.LOGIN, a.LOGOUT
 FROM $ATTENDANCE.employee_attendance AS a LEFT JOIN $ADODB_DB.employment em ON a.EMP_NO = em.EMP_NO 
 LEFT JOIN $ADODB_DB.personal p ON em.EMP_ID = p.EMP_ID WHERE DATE(LOGIN) BETWEEN '$DATE1' AND '$DATE2' 
 AND em.STATUS IN ('Reg Operatives', 'Reg Staff ') OR LOGIN IS NULL";
    $DTR = $conn3->GetAll($sql);
    
$LOGIN = $DTR->fields['LOGIN'];

if($LOGIN == NULL){
 // I dont know how can I add stylesheet here if the data has a null value
}
   
    $smarty->assign('attendance', $DTR);

 }



<div id="attendance" style="width: 580px; height: 420px; overflow: auto; padding: 5px; top:95px">
<fieldset>
<legend>Employee Attendance</legend>
<table border="1">
<tr>
<td colspan="2" style="text-align:center">Employee No</td>
<td colspan="2" style="text-align:center">Employee Name</td>
<td colspan="2" style="text-align:center">Time In</td>
<td colspan="2" style="text-align:center">Time Out</td>
</tr>

{section name=att loop=$attendance}
  <tr>
    <td colspan="2">{$attendance[att].EMP_NO}</td>
    <td colspan="2">{$attendance[att].FULLNAME}</td>  
    <td colspan="2">{$attendance[att].LOGIN|date_format:"%d-%m-%Y %I:%M %p"}</td>
    <td colspan="2">{$attendance[att].LOGOUT|date_format:"%d-%m-%Y %I:%M %p"}</td> 
  </tr>
{sectionelse}
  <tr><td colspan="1">No DATA</td></tr>
{/section}
</table>
</fieldset>
</div>

Thank you

4

I understood why you used DATE(). Any datetime in LOGIN whose date component would’ve fallen between $date1 and $date2 will fall between midnight of $date1 and 23:59:59 on $date2. It doesn’t matter that $date1 and $date2 are just dates, they’re datetimes when the strings are coerced for comparison. Thus the query is equivalent while eliminating the function call. Whoever’s paying you to write this ought to thank you for that.

Here’s the same change to your actual query (why didn’t you share that in the first place?)


 $sql = "SELECT a.EMP_NO, CONCAT(LNAME, ', ' , FNAME, ' ', MI) AS FULLNAME, a.LOGIN, a.LOGOUT
 FROM $ATTENDANCE.employee_attendance AS a LEFT JOIN $ADODB_DB.employment em ON a.EMP_NO = em.EMP_NO 
 LEFT JOIN $ADODB_DB.personal p ON em.EMP_ID = p.EMP_ID WHERE (LOGIN BETWEEN '$DATE1' AND '$DATE2 23:59:59' OR LOGIN IS NULL)
 AND em.STATUS IN ('Reg Operatives', 'Reg Staff ')";

I don’t know how to highlight your null dates in your template. I know PHP and SQL, I don’t know Smarty’s templating language. I’m sure you can look up how to write a conditional in Smarty’s documentation

5

i think it is a CSS…

Thank you