[SOLVED] Table Help

[savagenoob]

OK, I know some of this involves MySQL but I posted over there a few days ago and got little help...

 

I have a table named timeclock with Employee, Clock, Timestamp for options. Is it possible to select the last row for each employee?

I am trying to build a table for an admin to view the last punch for each employee showing whether they are currently clocked in or out.

 

If there is not a way to do this with MySQL, how could I build some kind of loop to find the last unique value for Employee and display the row...

 

Here are the table headers... I am displaying a pin image under In or Out depending on whether or not the client is clocked in or out.

 

 

   

Employee

   

Out

   

In

   

Last Punch

   

Notes

 

 

[Snart]

Assuming that Last Punch is a timestamp and is kept in a MySql field called lastpunch:

 

SELECT * FROM `emplyees` ORDER BY lastpunch DESC LIMIT 1

[peranha]

Select the data in desc order, and limit it to 1

 

SELECT * FROM table WHERE employee = '$employee' ORDER BY DESC LIMIT 1

[savagenoob]

But this would just return the last punch for whatever Employee I choose, but I was wanting something that would show the last punch for all employees in the system.

[arwvisions]

wouldn't you just use foreach then?

[savagenoob]

Thats what I was testing now... can't quite get it though...

 

I think Im making my code more complicated than necessary now... do you have an easy way to use foreach in this case?

[savagenoob]

Heres my code that doesnt work

 

<?php
$agency = $_SESSION['SESS_AGENCY'];

$result = mysql_query("SELECT * FROM timeclock WHERE Agency = '$agency' ORDER BY Clock DESC LIMIT 1");
while($myrow = mysql_fetch_assoc($result))
{
$employee = $myrow['Employee'];
foreach ( $employee as $show )
{
?>
<table width="700" border="0">
  <tr>
    <th width="179" scope="col"><?php echo $show; ?></th>
    <th width="80" scope="col"><?php if ($myrow['Clock'] == "Out"){ echo "<img src=\"images/round_push_2.gif\" alt=\"Push Pin 2\" />"; }?>
</th>
    <th width="80" scope="col"><?php if ($myrow['Clock'] == "In"){ echo "<img src=\"images/round_push_1.gif\" alt=\"Push Pin 1\" />"; }?></th>
    <th width="209" scope="col"><?php $punchdate =strtotime($myrow['Time']); echo date("l, m/d/Y, g:i a", $punchdate); ?></th>
    <th width="130" scope="col"> </th>
  </tr>
</table>
<?php
}
}
?>

[Lucky_PHP_MAN]

Maybe you can show us a little bit of the sql script for the table?

 

1. Do an "Export" on your tables and post it here?

 

Regards,

LPM

[savagenoob]

ID Employee Time             Clock    Minutes  Agency

100 2 2009-01-17 22:50:15 In 1 (null)

101 2 2009-01-17 22:51:24 Out 1 (null)

102 2 2009-01-17 23:08:08 In 16 (null)

103 2 2009-01-17 23:11:19 Out 3 (null)

104 2 2009-01-18 00:07:24 In 56 (null)

105 2 2009-01-18 15:41:13 Out 933 (null)

106 2 2009-01-18 18:20:54 In 159 (null)

107 2 2009-01-29 22:31:36 Out 297 1 Solution Insurance

108 2 2009-01-29 22:31:36 In 643 1 Solution Insurance

109 2 2009-01-29 22:31:36 Out 244 1 Solution Insurance

110 2 2009-01-29 22:31:36 In 305 1 Solution Insurance

111 2 2009-01-29 22:31:36 Out 0 1 Solution Insurance

112 3 2009-01-30 00:40:11 In 0 1 Solution Insurance

113 3 2009-01-30 00:40:11 Out 0 1 Solution Insurance

114 2 2009-01-29 22:31:35 In 1338 1 Solution Insurance

115 2 2009-01-29 22:31:35 Out 1660 1 Solution Insurance

116 2 2009-01-29 22:31:35 In 10202 1 Solution Insurance

117 2 2009-01-29 22:31:35 Out 0 1 Solution Insurance

 

I might add that the Employee number is pulled from another table called members - member_id.

[savagenoob]

I figured this out finally, thank god...

 

<?php
$agency = $_SESSION['SESS_AGENCY'];
$query = "SELECT * FROM members WHERE agency = '$agency'";
$sql = mysql_query($query);
while ($emp2  = mysql_fetch_array($sql))
{
$employee = $emp2['member_id'];

$result = mysql_query("SELECT * FROM timeclock WHERE Agency = '$agency' AND Employee = $employee ORDER BY Clock DESC LIMIT 1");
while($myrow = mysql_fetch_assoc($result))
{
?>
<table width="700" border="0">
  <tr>
    <th width="179" scope="col"><?php echo $myrow['Employee']; ?></th>
    <th width="80" scope="col"><?php if ($myrow['Clock'] == "Out"){ echo "<img src=\"images/round_push_2.gif\" alt=\"Push Pin 2\" />"; }?>
</th>
    <th width="80" scope="col"><?php if ($myrow['Clock'] == "In"){ echo "<img src=\"images/round_push_1.gif\" alt=\"Push Pin 1\" />"; }?></th>
    <th width="209" scope="col"><?php $punchdate =strtotime($myrow['Time']); echo date("l, m/d/Y, g:i a", $punchdate); ?></th>
    <th width="130" scope="col"> </th>
  </tr>
</table>
<?php
}
}
?>