Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 7 of 7
  1. #1
    Regular Coder
    Join Date
    Aug 2010
    Posts
    712
    Thanks
    165
    Thanked 0 Times in 0 Posts

    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
    Senior Coder
    Join Date
    Jan 2011
    Location
    Missouri
    Posts
    3,772
    Thanks
    23
    Thanked 550 Times in 549 Posts
    Try this for your query:

    Code:
    $query = "
    select * from hris 
    inner join payroll on payroll.emp_no = hris.emp_id 
    inner join wage on wage.emp_id = hris.emp_id";
    $results = mysql_query($query);

  • #3
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,020
    Thanks
    75
    Thanked 4,323 Times in 4,289 Posts
    When you use an "implicit join" (that is, a join without the keyword JOIN), you are *always* doing an INNER JOIN. No other choice.

    If you want to always get records from one table even if there are no matching records in some other table, you must use an *explicit* OUTER JOIN. That is, either a LEFT JOIN or a RIGHT JOIN.

    *possibly* what you are after:
    Code:
    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' 
    GROUP BY c.EMP_NO
    Except that GROUP MAKES no sense. You don't have any aggregate function in there (e.g, MIN(), MAX(), SUM(), etc.) so the GROUP BY won't do anything. Almost surely what you need is ORDER BY, instead.

    EDIT: If you know that every employment record indeed has a matching wage record, then you might want to change the first LEFT JOIN to an INNER JOIN. But the second join surely needs to be a LEFT JOIN.
    Last edited by Old Pedant; 12-06-2011 at 08:50 PM.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • Users who have thanked Old Pedant for this post:

    newphpcoder (12-07-2011)

  • #4
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,020
    Thanks
    75
    Thanked 4,323 Times in 4,289 Posts
    And Sunfighter: Your query is the exact equivalent of his original. The syntax is different, but the semantics are 100% the same. Well, it would be, except that he doesn't seem to have a table named hris and also he seems to be getting data from two different databases which your query ignored.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • Users who have thanked Old Pedant for this post:

    newphpcoder (12-07-2011)

  • #5
    Senior Coder
    Join Date
    Jan 2011
    Location
    Missouri
    Posts
    3,772
    Thanks
    23
    Thanked 550 Times in 549 Posts
    @Old Pedant, please explain this to me. I don't know where hris came from it should have been employment. But I did make a db with those tables and the columns he gave and did run my query and did get ll the info.

    I'll look at his and my query to see why they are the same. But you said" he seems to be getting data from two different databases which your query ignored. " I am in the dark here I thought I was getting from the tables he wanted.

    And could you point me to some site to learn joins from (not mysql site, had a hard time with that one). Please.

  • #6
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,020
    Thanks
    75
    Thanked 4,323 Times in 4,289 Posts
    You'd have to see his full PHP code to know that actual names of his two databases, but look here (his original code made readable):
    Code:
    $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";
    He has two PHP variables, $ADODB_DB and $PAYROLL, and those contain the names of his two databases. Let's say he has prior PHP code that does
    Code:
    $ADODB_DB = "empinfo";
    $PAYROLL = "pay";

    So that means his query is really doing
    Code:
    $sql = "SELECT em.EMP_NO, em.STATUS, w.RATE, c.Casual_Hours "
         . " FROM empinfo.wage w, empinfo.employment em, pay.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";
    Yes, in MySQL it is perfectly legal to use data from as many databases as you wish, so long as they are all on the same server. You simply use the database name as a prefix to the table name. (There's also a way to use a database on another server, but let's not get into that.)

    And yes, both your query and his will work fine, *SO LONG* as there is indeed data in the Casual_Hours table for EACH AND EVERY employee.

    But now try running your query after adding a new employee to the employment table but not to the wage table or Casual_Hours table.

    That employee won't show up in your results.

    Go ahead and add a record in the wage table for that new employee. Again run the query. The new employee still won't show up.

    Finally add a record for that employee to the Casual_Hours table and run your query. *FINALLY* the employee shows up.

    Run the same experiment with my query and the employee always shows up, with NULL data in the fields for the other tables if there is no match in those tables.

    As phpcoder said:
    i want to displayed rate even he has no hours
    And *THAT* is indeed how outer joins work.

    Joins work the same way in all databases, so you could start here:
    http://www.sql-tutorial.net/
    Last edited by Old Pedant; 12-07-2011 at 06:34 AM.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • Users who have thanked Old Pedant for this post:

    sunfighter (12-07-2011)

  • #7
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,020
    Thanks
    75
    Thanked 4,323 Times in 4,289 Posts
    PHPCoder: I missed this part of your first post:
    i want to displayed rate even he has no hours, and hours will be 00:00
    You've probably discovered by now that you just get NULL back for the hours if there are none. You could of course take care of that in your PHP code, but...

    Easy fix.
    Code:
    SELECT em.EMP_NO, em.STATUS, w.RATE, IFNULL(c.Casual_Hours,'0:00:00') AS `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' 
    ORDER BY em.EMP_NO, em.STATUS, Casual_Hours
    As I said, your GROUP BY is bogus, so I changed it to what I *guess* would be the right ORDER BY.
    Last edited by Old Pedant; 12-07-2011 at 06:33 AM.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • Users who have thanked Old Pedant for this post:

    newphpcoder (12-07-2011)


  •  

    Posting Permissions

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