[SOLVED] Help with Query

[savagenoob]

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.

[dropfaith]

$query = "SELECT * FROM timeclock order by Timestamp desc limit 1";

$result = mysql_query($query) or die ("Error in query: $query. " . mysql_error());

 

 

this is kinda rough  but should work out

 

 

[savagenoob]

Thanks, but this will only return the last row no matter which employee. I need to go one step further and return the last record from each employee in database. Any suggestions would be greatly appreciated.

[fenway]

First you need to get "each" employee -- then you can find the last row.

[savagenoob]

I can do that, I have another table that has the same data called members and the row member_id is the same as Employee.

How do I do it... foreach? can i get a snippet of how?

Here is how I tried but failed miserably...

<?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 $echo $myrow['Time']?></th>
    <th width="130" scope="col"> </th>
  </tr>
</table>
<?php
}
}
?>

[ngreenwood6]

I am not quite sure that you are trying to do but in the sense that you are using this foreach it is pretty much pointless. take out the foreach and change the $show to $employee. So it is like this:

 

<?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'];

?>
<table width="700" border="0">
  <tr>
    <th width="179" scope="col"><?php echo $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 $echo $myrow['Time']?></th>
    <th width="130" scope="col"> </th>
  </tr>
</table>
<?php
}
?>

 

You said that you failed miserably can you give a little more information. What did not work that was supposed to work.

[savagenoob]

Figured it out...below is code.

 

<?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
}
}
?>

[fenway]

Well, that's one way...