Results 1 to 7 of 7
  1. #1
    Join Date
    Dec 2010
    Posts
    136

    Unanswered: Need help in using if condition to select query

    Hi..


    Now, I have select query with if condition, that if null the table data <td> will have background-color : red

    here is my code:

    Code:
    $sql = "SELECT DISTINCT IF(ISNULL(a.LOG_IN), 'rdc', '') AS LOGIN_CLASS, IF(ISNULL(a.LOG_OUT), 'rdc', '') AS LOGOUT_CLASS, a.EMP_ID, a.LOG_IN, a.LOG_OUT, CONCAT(LNAME, ',' , FNAME, ' ', MI, '.') AS FULLNAME
     FROM hrdb.attendance AS a JOIN hris.employment em ON (a.EMP_ID = em.EMP_NO AND em.STATUS = 'Reg Operatives') JOIN hris.personal AS p ON p.EMP_ID = em.EMP_ID
     WHERE ATTENDANCE_DATE BETWEEN '2012-06-01' AND '2012-06-15' OR ATTENDANCE_DATE = '0000-00-00'  
     OR ISNULL(a.LOG_IN) OR ISNULL(a.LOG_OUT) ORDER BY FULLNAME, a.LOG_IN, a.LOG_OUT";
    
        $DTR = $conn3->GetAll($sql);
       
        $smarty->assign('attendance', $DTR);
    Code:
    .rdc {background-color:#ff0000;}
     {section name=att loop=$attendance} 
    <table>
    <tr>  
    <td colspan="2" class="{$attendance[att].LOGIN_CLASS}">{$attendance[att].LOG_IN}</td>
    <td colspan="2" class="{$attendance[att].LOGOUT_CLASS}">{$attendance[att].LOG_OUT}</td>  
    {sectionelse}
      <tr><td colspan="1">No DATA</td></tr>
    {/section}

    Now..

    I need to revise my select if condition, because now the LOG_IN and LOG_OUT has no possibilities to have NULL values but now It will have 0000-00-00 00:00:00 values.


    So I need to put background color if the LOG_IN is 0000-00-00 00:00:00 also with LOG_OUT.


    Thank you so much

  2. #2
    Join Date
    Mar 2004
    Posts
    480

  3. #3
    Join Date
    Dec 2010
    Posts
    136
    Sorry..

    If i repost my problem to a lots of forum..

    I need to resolve as soon as possible thats why I repost it.


    I hope you'll understand

    Thank you

  4. #4
    Join Date
    Sep 2009
    Location
    San Sebastian, Spain
    Posts
    880
    I have run a test on timestamp as follows:

    Code:
    mysql> select * from testdate;
    +---------------------+---------------------+-------+
    | sdate               | edate               | name  |
    +---------------------+---------------------+-------+
    | 2012-06-18 09:07:57 | 2012-06-18 09:16:40 | Tom   | 
    | 2012-06-18 09:06:40 | 0000-00-00 00:00:00 | Dick  | 
    | 2012-06-18 09:06:40 | 0000-00-00 00:00:00 | Harry | 
    +---------------------+---------------------+-------+
    3 rows in set (0.00 sec)
    
    mysql> select * from testdate where edate = '0000-00-00 00:00:00';
    +---------------------+---------------------+-------+
    | sdate               | edate               | name  |
    +---------------------+---------------------+-------+
    | 2012-06-18 09:06:40 | 0000-00-00 00:00:00 | Dick  | 
    | 2012-06-18 09:06:40 | 0000-00-00 00:00:00 | Harry | 
    +---------------------+---------------------+-------+
    2 rows in set (0.00 sec)
    
    mysql> select * from testdate where edate = str_to_date('0000-00-00 00:00:00', '%Y-%m-%d %H:%i:%s');
    +---------------------+---------------------+-------+
    | sdate               | edate               | name  |
    +---------------------+---------------------+-------+
    | 2012-06-18 09:06:40 | 0000-00-00 00:00:00 | Dick  | 
    | 2012-06-18 09:06:40 | 0000-00-00 00:00:00 | Harry | 
    +---------------------+---------------------+-------+
    2 rows in set (0.00 sec)
    Ronan Cashell
    Certified Oracle DBA/Certified MySQL Expert (DBA & Cluster DBA)
    http://www.it-iss.com
    Follow me on Twitter

  5. #5
    Join Date
    Dec 2010
    Posts
    136
    In my query I get all data eventhough it's 0000-00-00 00:00:00

    I want is to change my if condition to color the value 0000-00-00 00:00:00


    Thank you so much

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by newphpcoder View Post
    I need to resolve as soon as possible thats why I repost it.
    you seem to be really good at posting tiny little problems in multiple forums, hoping to cast a wide enough net that somebody is sure to do your work for you

    you've been doing it for months and months

    my advice is... learn how to fix your own problems

    feeding time is over
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Dec 2010
    Posts
    136
    I tried this code and it works:

    Code:
    <td colspan="2" class="{$attendance[att].LOGIN_CLASS}{if $attendance[att].LOG_IN == "0000-00-00 00:00:00"} rdc{/if}">{$attendance[att].LOG_IN}</td>
    Thank you so much

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •