sql php TIMEDIFF

IntenseMX

What would be a properway to use TIMEDIFF in the following script? I have column in database that does CURRENT_TIMESTAMP, Now I have it displayed in a table on a page, but I also need another table where the difference in days and hour is counted.
Currently it will echo all results in from that database, I need it to echo the time diff for each now separate.

Can someone please help me implement a correct script in to this:

<html>
<body>
<center>
<table>
	<tr>
		<td align="center"> Current</td>
<?php
mysql_connect("localhost","root","");//database connection
mysql_select_db("beyondmotors");

$result = mysql_query("SELECT * FROM currentrentals");

echo "<table border='2'>
<tr>
<th>ID</th>
<th>Year</th>
<th>Make</th>
<th>Model</th>
<th>First Name</th>
<th>Last Name</th>
<th>Insurance</th>
<th>Date</th>
<th>Notes</th>
</tr>";
while($row = mysql_fetch_array($result))
  {
  echo "<tr>";
  echo "<td>" . $row['id'] . "</td>";
  echo "<td>" . $row['year'] . "</td>";
  echo "<td>" . $row['make'] . "</td>";
  echo "<td>" . $row['model'] . "</td>";
  echo "<td>" . $row['firstname'] . "</td>";
  echo "<td>" . $row['lastname'] . "</td>";
  echo "<td>" . $row['insurance'] . "</td>";
  echo "<td>" . $row['date'] . "</td>";
  echo "<td>" . $row['notes'] . "</td>";
    echo ("<td><a href=\"edit_form.php?id=$row[id]\">Edit</a></td></tr>");
  echo "</tr>";
  }
echo "</table>";

I was recommended to do:

SELECT (fields), UNIX_TIMESTAMP(date) as date FROM currentrentals

Than use:

$timesince = date(format, time() - $row['date']);

but i'm having trouble to get that working

dagon

i would do this:

$result = mysql_query("SELECT *,TIMEDIFF(`date`,NOW()) as diff  FROM currentrentals");

then add diff to the table

IntenseMX

I tried doing it like this:

<html>
<body>
<center>
<table>
	<tr>
		<td align="center"> Current</td>
<?php
mysql_connect("localhost","root","");//database connection
mysql_select_db("beyondmotors");

$result = mysql_query("SELECT * FROM currentrentals");
$result = mysql_query("SELECT *,TIMEDIFF(date,NOW()) as diff  FROM currentrentals"); 
echo "<table border='2'>
<tr>
<th>ID</th>
<th>Year</th>
<th>Make</th>
<th>Model</th>
<th>First Name</th>
<th>Last Name</th>
<th>Insurance</th>
<th>Date</th>
<th>Notes</th>
<th>Days Out</th>
</tr>";
while($row = mysql_fetch_array($result))
  {
  echo "<tr>";
  echo "<td>" . $row['id'] . "</td>";
  echo "<td>" . $row['year'] . "</td>";
  echo "<td>" . $row['make'] . "</td>";
  echo "<td>" . $row['model'] . "</td>";
  echo "<td>" . $row['firstname'] . "</td>";
  echo "<td>" . $row['lastname'] . "</td>";
  echo "<td>" . $row['insurance'] . "</td>";
  echo "<td>" . $row['currentdate'] . "</td>";
  echo "<td>" . $row['notes'] . "</td>";
  echo $timesince = date(format, time() - $row['currentdate']);
  echo ("<td><a href=\"edit_form.php?id=$row[id]\">Edit</a></td></tr>");
  echo "</tr>";
  }
echo "</table>";

but i get an error

"Warning: mysql_fetch_array() expects parameter 1 to be resource, boolean given in C:\wamp\www\beyondmotors\currentrentals\current.php on line 26"

line 26 is where the array code is.

What am I doing wrong?

Weedpacket

http://www.phpbuilder.com/board/showthread.php?p=10991229

IntenseMX

So this is what i have so far:

while($row = mysql_fetch_array($result))
  {
  echo "<tr>";
  echo "<td>" . $row['id'] . "</td>";
  echo "<td>" . $row['year'] . "</td>";
  echo "<td>" . $row['make'] . "</td>";
  echo "<td>" . $row['model'] . "</td>";
  echo "<td>" . $row['firstname'] . "</td>";
  echo "<td>" . $row['lastname'] . "</td>";
  echo "<td>" . $row['insurance'] . "</td>";
  echo "<td>" . $row['currentdate'] . "</td>";
  echo "<td>" . $row['notes'] . "</td>";
  echo "<td>" . ($timesince = date( time()- $row['currentdate'])) . "</td>";
  echo ("<td><a href=\"edit_form.php?id=$row[id]\">Edit</a></td></tr>");
  echo "</tr>";
  }
  echo "</tr>";

I'm having trouble with where to insert the:

$result = mysql_query("SELECT *,TIMEDIFF(`date`,NOW()) as diff  FROM currentrentals");

I tried few variations but it doesnt seem to work 😕

Does it have to be with in the fetch_array function? Or after it? as of now I get no error but the date in table displays as "1333137310"

Weedpacket

That's because you're misusing the [man]date[/man] function.

bradgrafelman

To elaborate, you've got a few issues:

You SELECT the result of calling MySQL's TIMEDIFF() function, and yet you never try to use the resulting data in any way.
This:
```
($timesince = date( time()- $row['currentdate']))
```
is first assigning some value to a variable called $timesince, and then, due to the nature of the assignment operator, is evaluated as that same value. However, you don't appear to be using $timesince anywhere, so why create that variable in the first place?
As Weedpacket points out, you're not using the [man]date/man function correctly. See its manual page for more information about the argument(s) it expects.

However, I'm a bit confused at why date() is even there at all. Why are you doing some date operations in PHP when you're already doing something similar in the SQL query?