SitePoint Sponsor

User Tag List

Results 1 to 14 of 14
  1. #1
    SitePoint Guru
    Join Date
    Oct 2011
    Posts
    658
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Problem encountered in select statement from two databases

    Hi

    I have select statement to get the Rate and Hours of employee, and I had noticed that if no hours save in database for that employee but he has rate, the rate was not also displayed. i want to displayed rate even he has no hours, and hours will be 00:00

    the fields is came from those databases and tables.

    HRIS - database name

    Tables and fields list

    employment AS em
    EMP_ID
    EMP_NO
    STATUS

    wage AS w
    EMP_ID
    RATE

    payroll - Database Name

    Table Name and Fields
    casual_hours As c
    EMP_NO
    Casual_Hours


    Here is my code:
    Code:
    if($STATUS == 'Casual'){
     
      $sql = "SELECT em.EMP_NO, em.STATUS, w.RATE, c.Casual_Hours FROM $ADODB_DB.wage w, $ADODB_DB.employment em, $PAYROLL.casual_hours c WHERE em.EMP_NO = c.EMP_NO AND w.EMP_ID = '$currentEmpID' AND em.EMP_ID = '$currentEmpID' GROUP BY c.EMP_NO"; 
      $RsEarnings = $conn2->Execute($sql); 
      
      $Rate      = $RsEarnings->fields['RATE'];
      $Hours      = $RsEarnings->fields['Casual_Hours'];
    
      $Hours = substr($Hours, 0, 5);
      $Hours = str_replace(':', '.', $Hours);
      
     $Amount = $_POST["Amount"];
     
     $Amount = round(($Hours/8)* $Rate, 2);  
     }
     else{
         $Hours = ('00:00');
         $Amount = (0);
     }
      
      $smarty->assign('Rate', $Rate);
      $smarty->assign('Hours', $Hours);
      $smarty->assign('Amount', $Amount);

    Thank you so much...

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,263
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by newphpcoder View Post
    i want to displayed rate even he has no hours, and hours will be 00:00
    use a LEFT OUTER JOIN
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Guru
    Join Date
    Oct 2011
    Posts
    658
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    can you give an example im not familiar in left outer join..

    Thank you

  4. #4
    SitePoint Guru
    Join Date
    Oct 2011
    Posts
    658
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    is it like this?

    Code:
    SELECT * FROM table1 LEFT JOIN table2 ON table1.id=table2.id
      LEFT JOIN table3 ON table2.id=table3.id;
    Thank you

  5. #5
    SitePoint Guru
    Join Date
    Oct 2011
    Posts
    658
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    i revised my code...

    here is my new code:
    Code:
    <?php
    if($STATUS == 'Casual'){
        
      $sql = "SELECT em.EMP_NO, w.RATE, c.Casual_Hours FROM $ADODB_DB.wage w LEFT JOIN $ADODB_DB.employment em ON w.EMP_ID=em.EMP_ID
      LEFT JOIN $PAYROLL.casual_hours c ON em.EMP_NO=c.EMP_NO";
      $RsEarnings = $conn2->Execute($sql); 
      
      $Rate      = $RsEarnings->fields['RATE'];
      $Hours      = $RsEarnings->fields['Casual_Hours'];
    
      $Hours = substr($Hours, 0, 5);
      $Hours = str_replace(':', '.', $Hours);
      
     $Amount = $_POST["Amount"];
     
     $Amount = round(($Hours/8)* $Rate, 2);  
     }
     else{
         $Hours = ('00:00');
         $Amount = (0);
     }
    ?>
    it works..i want to know how can i add 00.00 if no data from hours....now when no hours the textbox become blank,or empty i want it to have 00.00..

    Thank you

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,263
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by newphpcoder View Post
    i want to know how can i add 00.00 if no data from hours....now when no hours the textbox become blank,or empty i want it to have 00.00..
    use COALESCE

    you've never taken any SQL training, have you

    i think it is time that you do

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    SitePoint Guru
    Join Date
    Oct 2011
    Posts
    658
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Ok i will search about use COALESCE


    Thank you

  8. #8
    SitePoint Guru
    Join Date
    Oct 2011
    Posts
    658
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    When I tried this query in mysql
    Code:
    SELECT em.EMP_NO, w.RATE, r.Hours FROM hris.wage w LEFT JOIN hris.employment em ON w.EMP_ID=em.EMP_ID LEFT JOIN payroll.regular_sum_hours r ON em.EMP_NO = r.EMP_NO;
    but when i put to php with this code:
    Code:
    $sql = "SELECT EMP_ID,EMP_NO, STATUS FROM employment WHERE EMP_ID = '$currentEmpID'";
     $rsStatus = $conn->Execute($sql);
     
     $STATUS = $rsStatus->fields['STATUS'];
     $EMP_ID = $rsStatus->fields['EMP_ID'];
     
     if ($STATUS == 'Regular'){
     
     //$sql = "SELECT em.EMP_NO, em.STATUS, w.RATE, r.Hours FROM $ADODB_DB.wage w, $ADODB_DB.employment em, $PAYROLL.regular_sum_hours r WHERE em.EMP_NO = r.EMP_NO AND w.EMP_ID = '$currentEmpID' AND em.EMP_ID = '$currentEmpID' GROUP BY r.EMP_NO"; 
       $sql = "SELECT em.EMP_NO, em.STATUS, w.RATE, r.Hours FROM $ADODB_DB.wage w LEFT JOIN $ADODB_DB.employment em ON w.EMP_ID = em.EMP_ID 
       LEFT JOIN $PAYROLL.regular_sum_hours r ON em.EMP_NO = r.EMP_NO";    
      $RsEarnings = $conn2->Execute($sql); 
      
      $Rate      = $RsEarnings->fields['RATE'];
      $Hours      = $RsEarnings->fields['Hours'];
    
      $Hours = substr($Hours, 0, 5);
      $Hours = str_replace(':', '.', $Hours);
      
     $Amount = $_POST["Amount"];
     
     $Amount = round(($Hours/8)* $Rate, 2); 
     }
     elseif($STATUS == 'Casual'){
     
      //$sql = "SELECT em.EMP_NO, em.STATUS, w.RATE, c.Casual_Hours FROM $ADODB_DB.wage w, $ADODB_DB.employment em, $PAYROLL.casual_hours c WHERE em.EMP_NO = c.EMP_NO AND w.EMP_ID = '$currentEmpID' AND em.EMP_ID = '$currentEmpID' GROUP BY c.EMP_NO"; 
      $sql = "SELECT em.EMP_NO, em.STATUS, w.RATE, c.Casual_Hours FROM $ADODB_DB.wage w LEFT JOIN $ADODB_DB.employment em ON w.EMP_ID=em.EMP_ID
      LEFT JOIN $PAYROLL.casual_hours c ON em.EMP_NO=c.EMP_NO";
      $RsEarnings = $conn2->Execute($sql); 
      
      $Rate      = $RsEarnings->fields['RATE'];
      $Hours      = $RsEarnings->fields['Casual_Hours'];
    
      $Hours = substr($Hours, 0, 5);
      $Hours = str_replace(':', '.', $Hours);
      
     $Amount = $_POST["Amount"];
     
     $Amount = round(($Hours/8)* $Rate, 2);  
     }
     else{
         $Hours = ('00:00');
         $Amount = (0);
     }
      
      $smarty->assign('Rate', $Rate);
      $smarty->assign('Hours', $Hours);
      $smarty->assign('Amount', $Amount);
    when the employee is Regular. the Rate and Hours are all the same, they only get the first row in the database.but whenI run it to mysql it works i only change the $ADODB_DB to hris and $PAYROLL to payroll

    Thank you so much..

  9. #9
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,263
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    sounds like a php programming issue
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  10. #10
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,500
    Mentioned
    163 Post(s)
    Tagged
    4 Thread(s)
    Quote Originally Posted by newphpcoder View Post
    PHP Code:
      $RsEarnings $conn2->Execute($sql); 
      
      
    $Rate      $RsEarnings->fields['RATE'];
      
    $Hours      $RsEarnings->fields['Hours']; 
    when the employee is Regular. the Rate and Hours are all the same, they only get the first row in the database.but whenI run it to mysql it works i only change the $ADODB_DB to hris and $PAYROLL to payroll

    Thank you so much..
    I see no loop in the php code? You only assign the values once.

    And by the way, I would change the queries like this (see the red line, I edited the "casual" as example):
    Code:
    SELECT 
        w.EMP_NO
      , em.STATUS
      , w.RATE
      , c.Casual_Hours 
    FROM $ADODB_DB.wage w 
    LEFT JOIN $ADODB_DB.employment em 
    ON w.EMP_ID=em.EMP_ID
    LEFT JOIN $PAYROLL.casual_hours c 
    ON em.EMP_NO=c.EMP_NO
    If you take emp_no from a left joined table, it will be NULL if no row exists in that table. Taking it from the primary table in the query, it will always have a value.

  11. #11
    SitePoint Guru
    Join Date
    Oct 2011
    Posts
    658
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    But no EMP_NO in wage table...only EMP_ID

    Thank you

  12. #12
    SitePoint Guru
    Join Date
    Oct 2011
    Posts
    658
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I tried this code...suggested by other programmer:

    Code:
    $sql = "SELECT em.EMP_NO, em.STATUS, w.RATE, c.Casual_Hours 
    FROM $ADODB_DB.employment AS em 
    LEFT JOIN $ADODB_DB.wage AS w ON em.EMP_ID = w.EMP_ID
    LEFT JOIN $PAYROLL.casual_hours AS c ON em.EMP_NO = c.EMP_NO
    WHERE  em.EMP_ID = '$currentEmpID'";
    Thank you

  13. #13
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,500
    Mentioned
    163 Post(s)
    Tagged
    4 Thread(s)
    Quote Originally Posted by newphpcoder View Post
    But no EMP_NO in wage table...only EMP_ID

    Thank you
    O yeah, my mistake.
    Makes me wonder why you have EMP_NO and EMP_ID though. Isn't EMP_NO a unique number that identifies the employee?

    Quote Originally Posted by newphpcoder View Post
    I tried this code...suggested by other programmer:

    Code:
    $sql = "SELECT em.EMP_NO, em.STATUS, w.RATE, c.Casual_Hours 
    FROM $ADODB_DB.employment AS em 
    LEFT JOIN $ADODB_DB.wage AS w ON em.EMP_ID = w.EMP_ID
    LEFT JOIN $PAYROLL.casual_hours AS c ON em.EMP_NO = c.EMP_NO
    WHERE  em.EMP_ID = '$currentEmpID'";
    Thank you
    And does it work?

  14. #14
    SitePoint Guru
    Join Date
    Oct 2011
    Posts
    658
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Yes, but I revised it:

    Code:
     $sql = "SELECT em.EMP_NO, em.STATUS, w.RATE, r.Hours 
    FROM $ADODB_DB.employment AS em 
    INNER JOIN $ADODB_DB.wage AS w ON em.EMP_ID = w.EMP_ID
    LEFT JOIN $PAYROLL.regular_sum_hours AS r ON em.EMP_NO = r.EMP_NO
    WHERE  em.EMP_ID = '$currentEmpID'";


Bookmarks

Posting Permissions

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