From: Bruce Gilbert on
Hi there,

I am still attempting to get a display of the result of my two
timestamp fields subtracted from one another in minutes and hours if
needed. This is for a form and the timestamps are recorded when a user
logs in [login_timestamp] and when the form is
submitted[submit_timestamp]. The display still doesn't appear to be
accurate and for some reason the results are displayed three times for
each record underneath the results for one person. So it looks
something like.

Tom Thumb
Answer 1
Answer 2
Answer 3 etc.
[time 1]
[time 2]
[time 3]

Sally Smith
Answer 1
Answer 2
Answer 3 etc.
[time 1]
[time 2]
[time 3]

Bob Jones
Answer 1
Answer 2
Answer 3
[time 1]
[time 2]
[time 3]

time1,2,3 being for Tom Thumb, Sally Smith and Bob Jones respectively.

My entire code for the form looks like this.

<html>
<head>
<title>Exam Results</title


</head>
<body>

<?php include("includes/functions.php"); ?>
<h1 class="results">exam results</h1>
<table id="results">
<tr><th>Candidate Name</th></tr>

<?php
ini_set("display_errors","1");
ERROR_REPORTING(E_ALL);
$conn = mysql_connect("localhost","uname","pw");

if (!$conn) {
echo "Unable to connect to DB: " . mysql_error();
exit;
}

if (!mysql_select_db("MyDB")) {
echo "Unable to select mydbname: " . mysql_error();
exit;
}

$sql = "SELECT Responses.name,Answer1,Answer2,Answer3,Answer4,Answer5,Answer6,Answer7,Answer8,Answer9,Answer10,Answer11,Answer12
FROM Responses ";


$result = mysql_query($sql);



if (!$result) {
echo "Could not successfully run query ($sql) from DB: " . mysql_error();
exit;
}

if (mysql_num_rows($result) == 0) {
echo "No rows found, nothing to print so am exiting";
exit;
}


while ($row = mysql_fetch_assoc($result)) {
echo "<tr><td class='name'>{$row['name']}</td></tr>";
echo "<tr><td class='section'><strong>Section 1</strong></td></tr>";

for ($i =1;$i<9;++$i) {
echo "<tr><td>{$row['Answer'.$i]}</td></tr>";
}
echo "<tr><td class='section'><strong>Section 2</strong></td></tr>";

echo "<tr><td>{$row['Answer10']}</td></tr>";
echo "<tr><td class='section'><strong>Section 3</strong></td></tr>";

echo "<tr><td>{$row['Answer11']}</td></tr>";
echo "<tr><td class='section'><strong>Section 4</strong></td></tr>";

echo "<tr><td>{$row['Answer12']}</td></tr>";



$sql_timestamp = "SELECT
TIMESTAMPDIFF(SECOND,submit_timestamp,login_timestamp) as cTime FROM
Responses LEFT JOIN Candidates USING (user_id)";

$result_timestamp = mysql_query($sql_timestamp);
if (!$result_timestamp) {
echo "Could not successfully run query ($sql_timestamp) from DB: "
.. mysql_error();
exit;
}
while($row = mysql_fetch_assoc($result_timestamp)){
$formatted_completion_time = formatTime($row['cTime']);
echo "<tr><th class='complete'>Completion Time:</th></tr><tr><td>\n";
echo $formatted_completion_time;
echo "</td></tr>";
}

}
mysql_free_result($result);



?>

</table>
</body>
</html>

and my function code for the subtracting of the two fields is here:

<?php
function formatTime($cTime){
if($cTime < 60){ //less than 1 minute show seconds
$formatted_completion_time = date("s",$cTime)." seconds";
}elseif($cTime < 3600){ //1 hour
$formatted_completion_time = date("i",$cTime)." minutes";
}elseif($cTime < 86400){ //24 hours
$formatted_completion_time = date("H:i",$cTime)." hours";
}else{
$formatted_completion_time = round($cTime/86400,0)." days";
}
return $formatted_completion_time;
}
?>

any assistance that can be provided is greatly appreciated.

--
::Bruce::