Resctricting querys with datetime 2

[GAstrada]

Hello all.
I'm very new to php and have a Mysql table with a datetime field, (filled with now() ), a lot of records new every hour.

My question:
My clients want to fill a html form in where they write how many hours ago they want to view records, (last 1 hour, last 2 hours, etc.). Of course no problem to enter this variable in a php script.
But I don't know how to make such "restrictive" query.

Any help will be appreciated.
Thanks in advance.

gus

 

[Vragabond]

Questions about Mysql queries would better be answered in forum436.

___________________________________________________________
[small]Do something about world cancer today: PACT[/small]

 

[jpadie]

since you posted in the php forum, i guess you are looking for help constructing the query in php rather than the query per se and that your comment about being able to insert the value into a script has a meaning that i don't immediately understand.

assuming the form control is called "timerestriction", your php code would look something like this

if (isset($_POST['timerestricton'])){
 $query = "Select * from table where datecolumn > (subtime(datecolumn, '$_POST[timerestriction]:0:0'))";
}

this presumes that timerestriction reports an integer value such as 1, 2 ,3 etc

 

[GAstrada]

Thanks Vragabond !!
Thanks jpadie !!

I'll try your's concepts. I'm a bigginer in PHP!!!!

By now, I've working with the following test code, and it works. I realized that only working with UnixTimeStamp I have the ABSOLUTE control over time, hours, etc., regardless midnight, and other troubles.


<?php

// ideas para construir "fechas y horas" armadas

$resto = 3600; // esta variable va a venir como $_POST desde un form. (segundos en una hora)

function mysqlDatetimeToUnixTimestamp($datetime)
{
$val = explode(" ",$datetime);
$date = explode("-",$val[0]);
$time = explode(":",$val[1]);
return mktime($time[0],$time[1],$time[2],$date[1],$date[2],$date[0]);
}

$actual = date('Y-m-d H:i:s');

echo "$actual";
echo "<br>";

$tounix = mysqlDatetimeToUnixTimestamp($actual);
$tounix = $tounix - $resto;
$sqlDateTime = date('Y-m-d H:i:s', $tounix);
echo "$sqlDateTime";


?>

$resto is the variable that I "catch" from a html form. (of course in number of hours, not in "hardcoded" seconds like now is).

The next issue is to make the query starting from the records that satisfy the number of backwards hours entered.
This implies comparison between the field datetime against $resto.

Regards.
Thanks for all !!!!

 

[jpadie]

to convert from mysql date to unix timestamp

$unixtimestamp = strtotime($mysqldate);

to convert to a mysql datetime

$mysqltimestamp = date("Y-m-d H:i:s", $unixtimestamp);