SitePoint Sponsor |
|
User Tag List
Results 26 to 30 of 30
Thread: 2 questions
-
May 14, 2009, 23:04 #26
- Join Date
- Jul 2006
- Location
- Augusta, Georgia, United States
- Posts
- 4,194
- Mentioned
- 17 Post(s)
- Tagged
- 5 Thread(s)
Two questions
- What do your tables look like (primary keys,foreign keys,etc)?
- What is it that you would like to display from those tables?
-
May 15, 2009, 01:32 #27
- 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
-
May 15, 2009, 02:14 #28
- 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
-
May 15, 2009, 02:38 #29
- Join Date
- Apr 2009
- Location
- China
- Posts
- 69
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
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
-
May 15, 2009, 03:57 #30
- 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> '.$row['employee_id'].'</li>';
echo '<li><strong>Name:</strong> '.$row['employee_name'].'</li>';
echo '<li><strong>Position:</strong> '.$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