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

    Unanswered: Issue in NULL values

    I got an issue in null values and I need to query between to two tables to get the data with non null values.

    here is the scenario

    First Scenario: // the min_dtr has the 0000-00-00 00:00:00
    EMP_NO---EMP_ID----FULLNAME-----------DATE_DTR----min_dtr--------------max_dtr------------
    09900215-000089----Dela Cruz, Juan A.-2011-12-20--0000-00-00 00:00:00--2011-12-20 13:38:00
    09900215-000089----Dela Cruz, Juan A.-2011-12-20--2011-12-20 05:35:00--2011-12-20 13:38:00

    and it works using this code:

    Code:
    
    SELECT em.EMP_NO
         , p.EMP_ID
         , CONCAT(LNAME , ', ' , FNAME , ' ' , MI , '.') AS FULLNAME
    
         , CASE DATE(a.LOGOUT) WHEN '0000-00-00'          THEN DATE(n.TIME_OUT) ELSE DATE(a.LOGOUT) END AS DATE_DTR
         , CASE a.LOGIN        WHEN '0000-00-00 00:00:00' THEN n.TIME_IN        ELSE a.LOGIN        END AS min_dtr
         , CASE a.LOGOUT       WHEN '0000-00-00 00:00:00' THEN n.TIME_OUT       ELSE a.LOGOUT       END AS max_dtr
    /* OR
         , COALESCE( NULLIF(DATE(a.LOGOUT) , '0000-00-00'         ) , DATE(n.TIME_OUT) ) AS DATE_DTR
         , COALESCE( NULLIF(a.LOGIN        , '0000-00-00 00:00:00') , n.TIME_IN        ) AS min_dtr
         , COALESCE( NULLIF(a.LOGOUT       , '0000-00-00 00:00:00') , n.TIME_OUT       ) AS max_dtr
    */
     FROM  hris.employment  em
     INNER JOIN
           hris.personal    p
      ON   p.EMP_ID = em.EMP_ID
     LEFT  OUTER JOIN
           payroll.reg_att  a
      ON   a.EMP_NO         = em.EMP_NO
       AND DATE(a.LOGOUT  ) = '2011-12-20'
     LEFT  OUTER JOIN
           payroll.nrs      n
      ON   n.EMP_NO         = em.EMP_NO
       AND DATE(n.TIME_OUT) = '2011-12-20'
     WHERE
           em.EMP_ID = '000089'
    ;
    the result of this code is :
    EMP_NO---EMP_ID----FULLNAME-----------DATE_DTR----min_dtr--------------max_dtr------------

    09900215-000089----Dela Cruz, Juan A.-2011-12-20--2011-12-20 05:35:00--2011-12-20 13:38:00

    and it is correct

    and this is the second scenario: // the max_dtr has the 0000-00-00 00:00:00

    EMP_NO---EMP_ID----FULLNAME-----------DATE_DTR----min_dtr--------------max_dtr------------
    00900392-000252----Dela Cruz, John A.-2011-12-20--2011-12-20 05:35:00--0000-00-00 00:00:00
    00900392-000252----Dela Cruz, John A.-2011-12-20--2011-12-20 05:35:00--2011-12-20 15:38:00
    I used this code :
    Code:
    SELECT em.EMP_NO
         , p.EMP_ID
         , CONCAT(LNAME , ', ' , FNAME , ' ' , MI , '.') AS FULLNAME
    
         , CASE DATE(a.LOGOUT) WHEN '0000-00-00'          THEN DATE(n.TIME_OUT) ELSE DATE(a.LOGOUT) END AS DATE_DTR
         , CASE a.LOGIN        WHEN '0000-00-00 00:00:00' THEN n.TIME_IN        ELSE a.LOGIN        END AS min_dtr
         , CASE a.LOGOUT       WHEN '0000-00-00 00:00:00' THEN n.TIME_OUT       ELSE a.LOGOUT       END AS max_dtr
    /* OR
         , COALESCE( NULLIF(DATE(a.LOGOUT) , '0000-00-00'         ) , DATE(n.TIME_OUT) ) AS DATE_DTR
         , COALESCE( NULLIF(a.LOGIN        , '0000-00-00 00:00:00') , n.TIME_IN        ) AS min_dtr
         , COALESCE( NULLIF(a.LOGOUT       , '0000-00-00 00:00:00') , n.TIME_OUT       ) AS max_dtr
    */
     FROM  hris.employment  em
     INNER JOIN
           hris.personal    p
      ON   p.EMP_ID = em.EMP_ID
     LEFT  OUTER JOIN
           payroll.reg_att  a
      ON   a.EMP_NO         = em.EMP_NO
       AND DATE(a.LOGOUT  ) = '2011-12-20'
     LEFT  OUTER JOIN
           payroll.nrs      n
      ON   n.EMP_NO         = em.EMP_NO
       AND DATE(n.TIME_OUT) = '2011-12-20'
     WHERE
           em.EMP_ID = '000252'
    ;
    and the output is:

    EMP_NO---EMP_ID----FULLNAME-----------DATE_DTR----min_dtr--------------max_dtr------------
    00900392-000252----Dela Cruz, John A.-(NULL)------(NULL)---------------(NULL)

    I want ouput is from nrs data because it is completed :

    EMP_NO---EMP_ID----FULLNAME-----------DATE_DTR----min_dtr--------------max_dtr------------
    00900392-000252----Dela Cruz, John A.-2011-12-20--2011-12-20 05:35:00--2011-12-20 15:38:00

    Thank you so much

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Somethings you cannot do inside a single SQL statement
    sometimes you use SQL to marshall the data and something else (I'm guessing in your case PHP) to sticthc it all together to create a formatted presentation.

    in this case extract the data from relevant tables and bring it together in PHP
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Dec 2010
    Posts
    136
    Quote Originally Posted by healdem View Post
    Somethings you cannot do inside a single SQL statement
    sometimes you use SQL to marshall the data and something else (I'm guessing in your case PHP) to sticthc it all together to create a formatted presentation.

    in this case extract the data from relevant tables and bring it together in PHP
    HOw?Can you suggest sample.

    Thanks

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    $strSQL = "Select employee, details from Employees"
    //execute the SQL
    //from that recordset you know have all the employees name and other stuff

    $strSQL1 = "select time,events from clockonevents where employeeid = wahtever and eventdate = whatchamacallit'
    //execute that SQL
    //the process the results from that SQL to extract the clock on / clock off events as requried

    then bring it all together in PHP in whatever format you are suign whether thats aas a table, a variable for printing and so on.
    I'd rather be riding on the Tiger 800 or the Norton

  5. #5
    Join Date
    Dec 2010
    Posts
    136
    I tried this:

    Code:
    <?php
      error_reporting(0);
    $con = mysql_connect('localhost', 'root','');
    
    if (!$con) {
        echo 'failed';
        die();
    }
    
    mysql_select_db("mes", $con);
    ?>
    <style type="text/css">
    table {
        margin: 9px;
    }
    
    th {
        font-family: Arial, Helvetica, sans-serif;
        font-size: .7em;
        background: #694;
        color: #FFF;
        padding: 2px 6px;
        border-collapse: separate;
        border: 1px solid #000;    
    }
    
    td {
        font-family: Arial, Helvetica, sans-serif;
        font-size: .7em;
        border: 1px solid #DDD;
        text-align: right;
    }  
    </style>
    <script type="text/javascript">
    function showDetails(pcode) {
     
     var clickElement = pcode.value;
     var click_id = pcode.id; 
                   
    // var value_ =  document.getElementById(click_id).checked
     //    =  document.getElementById(clickElement).checked;   
     //var Table = (document.getElementsByName('list')[0].value);     
     
     var Table = document.getElementById('kanban_list'); 
    
     var rows = Table.rows;   
     
     var strSelect = document.getElementById(click_id).value;
     //alert(strSelect)
     
     for (var i = 0; i < rows.length; i++) {      
          var row = rows[i];      
        //row.style.display = (row.id.substr(0,3) == strSelect) ?      'none'         :          '';   
        //row.style.display = (row.id.substr(0,3) == strSelect) ? 
       // row.style.display = 'none';
       // row.style.display = '';
        
        if (row.id.substr(0,3) == strSelect) {
            row.style.display = ((document.getElementById(click_id).checked) == false) ?  'none' : ''
        }
        //(document.getElementById(click_id).checked == false) ?  'none'               : '' : '';   
     
     }  
       
    }
    
    </script>
    <?php
    $sql = "SELECT kc.PCODE, kc.count_wip_chemical_weighing, kc.count_wip_compounding, kc.count_wip_extrusion, 
    kc.count_wip_forming, kc.count_wip_deflashing, kc.kanban, kc.virtual, p.max_lot, p.min_lot FROM kanban_checker kc 
    JOIN plan p ON kc.PCODE = p.PCODE  ORDER BY p.PCODE";    
    $result = mysql_query($sql, $con);
    
    ?><label>Display Details:</label><input  onclick='showDetails(this);' id='chkDetail'   type='checkbox' checked='checked' value='wip'/>
    <?php
    echo "<table id='kanban_list'>";
    echo "<tr>
            <th> PCODE </th>
            <th> LOT CODE </th>
            <th> CHEMICAL WEIGHING </th>
            <th> COMPOUNDING </th>
            <th> EXTRUSION </th>
            <th> FORMING </th>
            <th> DEFLASHING </th>
            <th> KANBAN </th>
            <th> VIRTUAL </th>
            <th> MAX LOT </th>
            <th> MIN LOT </th>
         </tr>";
    while($row = mysql_fetch_assoc($result)){
        echo "<tr>
            <td>$row[PCODE]</td>
            <td> </td>
           <!-- <td>$row[LOT_CODE]</td>   -->
            <td>$row[count_wip_chemical_weighing]</td>
            <td>$row[count_wip_compounding]</td>
            <td>$row[count_wip_extrusion]</td>
            <td>$row[count_wip_forming]</td>
            <td>$row[count_wip_deflashing]</td>
            <td>$row[kanban]</td>
            <td>$row[virtual]</td>
            <td>$row[max_lot]</td>
            <td>$row[min_lot]</td>
            </tr>";
    $sql = "SELECT  kd.LOT_CODE, kd.wip_chemicalweighing, kd.wip_compounding, kd.wip_extrusion, kd.wip_forming, kd.wip_deflashing 
    FROM kanban_data kd JOIN plan p ON kd.PCODE = p.PCODE  ORDER BY p.PCODE";
    $result_kanban_data = mysql_query($sql, $con);   
    
    while($row_data = mysql_fetch_assoc($result_kanban_data)){      
        echo "<tr id='wip'>
        <td></td>
        <td>$row_data[LOT_CODE]</td>
        <td>$row_data[wip_chemicalweighing]</td>
        <td>$row_data[wip_compounding]</td>
        <td>$row_data[wip_extrusion]</td>
        <td>$row_data[wip_forming]</td>
        <td>$row_data[wip_deflashing]</td>
        </tr>";
    }
    }
    echo "</table>";
         
    ?>
    the problem is all lot code display in P35 same with P35M PCODE.. i want is only lotcode which has PCODE P35 will display below P35 also all lotcode with P35M will display below P35M.

    Thank you

  6. #6
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    not having a clue what you are talking about its hard to diagnose the problem(s)
    PCode P35 and P35M may mean something to you when describing this problem but they mean nothing to me

    you need to investigate why the wrong lotcodes are coming through

    usually that suggest to me the where clause is wrong or possibly a join clause or the SQL isn't doing what you think it is, ferinstance you are not setting variable when you think you are

    you need to develop better debugging skills for use in PHP. whether that measn you move to an integrated debugger such as you can get when using eclipse or netbeasn on an appropriately enable dserver or just use a mix of variables telling you what happening in the code

    eg
    PHP Code:
    <?php
    $sql 
    "SELECT kc.PCODE, kc.count_wip_chemical_weighing, kc.count_wip_compounding, kc.count_wip_extrusion, 
    kc.count_wip_forming, kc.count_wip_deflashing, kc.kanban, kc.virtual, p.max_lot, p.min_lot FROM kanban_checker kc 
    JOIN plan p ON kc.PCODE = p.PCODE  ORDER BY p.PCODE"
    ;    
    $debug "SQL is:".$sql."<BR>";
    $result mysql_query($sql$con);
    $debug .= "Number of rows returned is:".mysql_num_rows($result)."<BR>";
    $debug .= "MySQL State:".mysql_errno."(".mysql_error.")"."<BR>";
    ....
    I'd rather be riding on the Tiger 800 or the Norton

  7. #7
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    ..and as this is now a PHP question not a MySQL question I'll move the4 thread there. please be carefull in deciding which forum is the appropriate forum to post in.
    if its a problem with the SQL then post in the MySQL forum
    if its a problem with the code then post in the PHP forum
    I'd rather be riding on the Tiger 800 or the Norton

  8. #8
    Join Date
    Dec 2010
    Posts
    136
    Quote Originally Posted by healdem View Post
    not having a clue what you are talking about its hard to diagnose the problem(s)
    PCode P35 and P35M may mean something to you when describing this problem but they mean nothing to me

    you need to investigate why the wrong lotcodes are coming through

    usually that suggest to me the where clause is wrong or possibly a join clause or the SQL isn't doing what you think it is, ferinstance you are not setting variable when you think you are

    you need to develop better debugging skills for use in PHP. whether that measn you move to an integrated debugger such as you can get when using eclipse or netbeasn on an appropriately enable dserver or just use a mix of variables telling you what happening in the code

    eg
    PHP Code:
    <?php
    $sql 
    "SELECT kc.PCODE, kc.count_wip_chemical_weighing, kc.count_wip_compounding, kc.count_wip_extrusion, 
    kc.count_wip_forming, kc.count_wip_deflashing, kc.kanban, kc.virtual, p.max_lot, p.min_lot FROM kanban_checker kc 
    JOIN plan p ON kc.PCODE = p.PCODE  ORDER BY p.PCODE"
    ;    
    $debug "SQL is:".$sql."<BR>";
    $result mysql_query($sql$con);
    $debug .= "Number of rows returned is:".mysql_num_rows($result)."<BR>";
    $debug .= "MySQL State:".mysql_errno."(".mysql_error.")"."<BR>";
    ....
    P35 and P35M is type of PCODE.

    Thank you..

    I only want this output:

    P35
    P35LOT_CODE
    P35M
    P35M LOT_CODE

    Thank you

  9. #9
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    so do a single select
    the where clause should be something like

    Code:
    where PCODE in("P35","P35M")
    or
    Code:
    PCODE like ("p35%"
    the iterate through the recordset generating section headers / footers as required

    psuedo code
    lastpcode = "!!!"; //variable to spot when the PCODE chanegs and take appropriate action
    $out =""; //holder fror output
    for each blah in blahdiblah
    is the current pcode the same as the last pcode
    if not ad lastpocde <> "!!!" then setup footer
    if not then set up header
    output data
    lastpcode = current pcode
    next item
    setup footer
    transfer out to output
    I'd rather be riding on the Tiger 800 or the Norton

Posting Permissions

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