SitePoint Sponsor

User Tag List

Page 2 of 2 FirstFirst 12
Results 26 to 30 of 30

Thread: 2 questions

  1. #26
    SitePoint Wizard bronze trophy
    Join Date
    Jul 2006
    Location
    Augusta, Georgia, United States
    Posts
    4,194
    Mentioned
    17 Post(s)
    Tagged
    5 Thread(s)
    Two questions

    1. What do your tables look like (primary keys,foreign keys,etc)?
    2. What is it that you would like to display from those tables?

  2. #27
    SitePoint Enthusiast
    Join Date
    Apr 2009
    Location
    China
    Posts
    69
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Here are the structures for that
    employee(emp_id number(6),
    name varchar2(100),
    position varchar2(50))

    emp_in_out(emp_id number(6),
    on_date date,
    in_time number(8),
    out_time number(8))
    Many Thanks & Best Regards,
    HuaMin Chen

  3. #28
    SitePoint Wizard bronze trophy
    Join Date
    Jul 2006
    Location
    Augusta, Georgia, United States
    Posts
    4,194
    Mentioned
    17 Post(s)
    Tagged
    5 Thread(s)
    Not really sure what you would like to accomplish but the below will join all users and their associated records if they have any.

    Code SQL:
    SELECT
         t1.emp_id AS employee_id
         ,t1.name AS employee_name
         ,t1.POSITION AS employee_position
         ,t2.on_date AS clock_date
         ,t2.in_time AS clock_in_time
         ,t2.out_time AS clock_out_time
     FROM
         employee AS t1
     LEFT
     JOIN
        emp_in_out AS t2
       ON
         t1.emp_id = t2.emp_id

  4. #29
    SitePoint Enthusiast
    Join Date
    Apr 2009
    Location
    China
    Posts
    69
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Question

    Then how about the way?

    I need this

    Master table

    Employee
    ID
    Name
    Position

    ...

    Detail Table

    Date In time Out time
    11/05/2009 9:01 AM 6:37 PM
    12/05/2009 9:01 AM 6:37 PM
    13/05/2009 9:01 AM 6:37 PM
    14/05/2009 9:01 AM 6:37 PM
    ...


    as one web page. Please help!

    How about this approach using PHP? Is my question clear now?????

    Many Thanks & Best Regards,
    HuaMin Chen

  5. #30
    SitePoint Wizard bronze trophy
    Join Date
    Jul 2006
    Location
    Augusta, Georgia, United States
    Posts
    4,194
    Mentioned
    17 Post(s)
    Tagged
    5 Thread(s)
    Something like this perhaps:

    PHP Code:
    $id=1// employee primary key
    $sql 
    'SELECT
         t1.emp_id AS employee_id
         ,t1.name AS employee_name
         ,t1.position AS employee_position
         ,t2.on_date AS clock_date
         ,t2.in_time AS clock_in_time
         ,t2.out_time AS clock_out_time
     FROM
         employee AS t1
     LEFT
     JOIN
        emp_in_out AS t2
       ON
         t1.emp_id = t2.emp_id
    WHERE
        t1.emp_id = '
    .$id.';';    
    $result mysql_query($sql);
    if(
    $result && mysql_num_rows($result)!=0) {
        
    $row mysql_fetch_assoc($result);    
        echo 
    '<li><strong>Id:</strong>&nbsp;'.$row['employee_id'].'</li>';
        echo 
    '<li><strong>Name:</strong>&nbsp;'.$row['employee_name'].'</li>';
        echo 
    '<li><strong>Position:</strong>&nbsp;'.$row['employee_position'].'</li>';    
        echo 
    '<table>';    
        echo 
    '<thead>';
        echo 
    '<th>Date</th>';
        echo 
    '<th>Time In</th>';
        echo 
    '<th>Time Out</th>';
        echo 
    '</thead>';    
        echo 
    '<tbody>';    
        if(!
    is_null($row['clock_date'])) {    
            
    mysql_field_seek($result,0);        
            while(
    $row mysql_fetch_assoc($result)) {        
                echo 
    '<tr>';            
                echo 
    '<td>',$row['clock_date'],'</td>';
                echo 
    '<td>',$row['clock_in_time'],'</td>';
                echo 
    '<td>',$row['clock_out_time'],'</td>';            
                echo 
    '</tr>';        
            }    
        } else {    
            echo 
    '<tr><td colspan="3">No Result</td></tr>';    
        }        
        echo 
    '</tbody>';    
        echo 
    '</table>';




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
  •