Searching for time

[davidcriniti]

Hi everyone,

 

I've got a simple script that is searching for the results of running races in a database.

 

Searching for a finisher's position works perfectly with this script:

$position = $_POST['position'];
$pos_operator = $_POST['pos_operator'];
if ($pos_operator == '=' && $position != '' ) 
{
  $where .= " AND position='$position'";
}

if ($pos_operator == '<' && $position != '' ) 
{
  $where .= " AND position<'$position'";
}

$position refers to a text field in the form where the user can type in a digit.

$pos_operator refers to a text field in the form where a user can select = or <

 

However, when I try to do a similar thing with time, I'm not getting the expected results.

$time_stated = strtotime($_POST['time_stated']); 

if ($time_stated != '' ) { 
  $where .= " AND time < '$time_stated'"; }
  

$time_stated refers to a text field where the user is asked to look for a time less than the time they input in xx:xx:xx format.

 

I tried this with and without the strtotime

 

Any tips?

 

Cheers,

Dave

 

 

 

[requinix]

How are you storing the time in the table? With a TIME? Or as a string?

 

strtotime() works with dates only so don't use it for this. Besides, it would convert a HH:MM:SS string to a straight number and that doesn't really help here. Make sure they've inputted a valid HH:MM:SS value and use that right in the query.

[fastsol]

First I would try putting `backticks` around your column names. Time is a mysql function so you might be confusing the query with how you're putting it together.  Second, what data type is your Time column in the database?  It should be in the "time" type for what you're doing (based on the limited info you have provided).  If it's stored as a VARCHAR it won't work the way you want cause it can't mathematically figure out the time difference since it's a string rather than a number.  Plus strtotime() would not be effective in this instance unless you're storing the time as a INT which would be I believe 11 number characters ling for a timestamp type value.

 

Please provide more info for a better answer and help.

[davidcriniti]

Thank you both for your responses.

 

The time column was always formatted as TIME, so that wasn't a problem. Adding the `backticks` seemed to make all the difference fastsol, so thanks for that tip!

$time_stated = $_POST['time_stated']; 

if ($time_stated != '' ) 
{ 
  $where .= " AND `time` < '$time_stated'"; 
}

Cheers,

Dave