[SOLVED] Read DATETIME from mysql

[daveh33]

$time = $row['time'];

I have a field in my mysql which is a datetime stamp - an example output: 2007-10-28 15:39:45

 

How can I select just the  H:i:s from the $time?

[Dragen]

<?php
list($date $time) = explode(' ', $row['time']);
?>

$time will now hold just the time, whereas date will have day, month and year

[ERuiz]

$time = $row['time'];

I have a field in my mysql which is a datetime stamp - an example output: 2007-10-28 15:39:45

 

How can I select just the  H:i:s from the $time?

 

Try this:

$time = $row['time'];
$time = date("H:i:s", $time);

[Dragen]

Try this:

$time = $row['time'];
$time = date("H:i:s", $time);

I don't think that will work, because the date needs to be stored as a unix timestamp using a function like mktime, to be recognised by the date() function

[daveh33]

<?php
list($date $time) = explode(' ', $row['time']);
?>

$time will now hold just the time, whereas date will have day, month and year

 

I tried this but got this error: -

 

 

Parse error: parse error, unexpected T_VARIABLE, expecting ',' or ')' in chat.php on line 65 - list($date $time) ....

 

[Dragen]

sorry missed out the ','

<?php
list($date, $time) = explode(' ', $row['time']);
?>

[daveh33]

OK great that works - is there a way to extend that with the same method so $time can be split into hours, minutes, and seconds?

 

I am trying to set up an if statement where it displays entries from database within past 5 minutes.

 

How is this possible?

[Dragen]

to split the time up just explode them by the ':'.

<?php
list($date, $time) = explode(' ', $row['time']);
list($hours, $mins, $secs) = explode(':', $time);
?>

[daveh33]

OK - how could I integrate that with my query:

 

$query = mysql_query("SELECT * FROM messages WHERE roomid='$rid' && `to`='all' ORDER BY `time`") or die (mysql_error());

 

so that it only gets messages from within 5 minutes ago from $today which is the current datetime

 

 

[Dragen]

ah, forget about exploding it all then.

If you convert the current time +5 minutes to unix:

$time = strtotime($time); //$time is a timestamp set five minutes before the current time
$query = mysql_query("SELECT * FROM messages WHERE roomid='$rid' && `to`='all' && UNIX_TIMESTAMP(time) < '$time' ORDER BY `time`") or die (mysql_error());

[daveh33]

 else {
$query22 = mysql_query("SELECT * FROM messages WHERE roomid='$rid' && `to`='all'") or die(mysql_error());
$row22 = mysql_fetch_array( $query22 );
$row22['time'];
$time = strtotime($time); //$time is a timestamp set five minutes before the current time
$query = mysql_query("SELECT * FROM messages WHERE roomid='$rid' && `to`='all' && UNIX_TIMESTAMP(time) < '$time' ORDER BY `time`") or die (mysql_error());
while($row = mysql_fetch_array( $query )) {
$to = $row['to'];
$id = $row['id'];
$from = $row['from'];
$message = $row['message'];
$username = $_SESSION['username'];
if ($from==$username) {
$prestyle = "<FONT COLOR=\"#0000FF\">";
$endstyle = "</font>";
}

 

Can you tell me where I am going wrong in my code using this

[Dragen]

okay..

1. what problem are you having?

2. have you set $time to anything?

[daveh33]

Is simply displays no results.

 

From the code from above, I set $time = strtotime($time);

[Dragen]

that's probably your problem then.

For one, you're setting $time to strtotime($time).. when $time hasn't been set in the first place. Try this instead:

$time = strtotime("+5 minutes");

[daveh33]

Ah ok that works now - how can I use that time to automatically refresh the page every 30 seconds?

[Dragen]

at the very top of your page, just after the <?php, add a header like this:

<?php
header( 'refresh: 30;' );

 

That will refresh every 30 seconds.

 

Something I've got to refresh a page every 10 minutes based on a variable is this:

$rtime = '10';
header('refresh: ' . $rtime*60);

It needs to be multiplied by 60 to put it into seconds, otherwise it would refresh after 10 seconds, not minutes.

[daveh33]

Thanks that refresh works

 

just tested with the time on the mysql query - its showing messages from over 5  minutes ago - the code is

 

$time = strtotime("+5 minutes");
$query = mysql_query("SELECT * FROM messages WHERE roomid='$rid' && `to`='all' && UNIX_TIMESTAMP(time) < '$time' ORDER BY `time`") or die (mysql_error());
while($row = mysql_fetch_array( $query )) {
$to = $row['to'];
$id = $row['id'];
$from = $row['from'];
$time2 = $row['time'];
$message = $row['message'];
$username = $_SESSION['username'];
if ($from==$username) {
$prestyle = "<FONT COLOR=\"#0000FF\">";
$endstyle = "</font>";
echo $prestyle;
}
list($date2, $time2) = explode(' ', $row['time']);
echo "<b><a href=\"viewmembers.php?id=$from\">$from</a></b><small>-$time2</small>>>          <i>$message</i>$endstyle<p> <p>";
}

[Dragen]

sorry.. try switching < to a >.

$query = mysql_query("SELECT * FROM messages WHERE roomid='$rid' && `to`='all' && UNIX_TIMESTAMP(time) > '$time' ORDER BY `time`") or die (mysql_error());

[daveh33]

With that - no messages are displayed...

[Dragen]

Ah, sorry.. I'm being stupid.

I've got you to set the $time to 5 minutes in the future. try this instead:

$time = strtotime("+5 minutes");

[daveh33]

Ah ok I understand how its working, but you mean -5

[Dragen]

oh yeah.. sorry, it's been a long day

[daveh33]

thanks for your help its much appreciated