Date issue in search

[DavidRoberts60]

I have a field in my MySQL database that is a timestamp. So when it is pulled onto the PHP page it looks something like 2011-10-28 10:46:47.

 

Obviously this looks ugly on the page so I do:

 

$jobtime2 = date("M j Y", strtotime($row["jobtime"]));

 

to make it look nice so it looks like Nov 3 2011.

 

I presume if I want to search for all records between 2 dates I use a form and in the SELECT statement use BETWEEN or do something like:

 

SELECT fields from table WHERE jobtime >= '$starttime' AND jobtime <= '$endtime';

 

however, if the 2 dates passed through via the form are Oct 1 2011 and Nov 1 2011 for example, it won't work, presumably because it wants input in the form 2011-10-28 10:46:47.

 

So what do I do?

 

Thanks

[The Little Guy]

convert it to a time:

 

$starttime = date("Y-m-d H:i:s", strtotime($_POST['date']));

 

This isn't the best solution, because not everything will be converted properly. I would recommend having pre-define values that users can select, such as a drop down, or separate input fields that each get validated as a month, day or year.

[DavidRoberts60]

convert it to a time:

 

$starttime = date("Y-m-d H:i:s", strtotime($_POST['date']));

 

This isn't the best solution, because not everything will be converted properly. I would recommend having pre-define values that users can select, such as a drop down, or separate input fields that each get validated as a month, day or year.

 

Wouldn't that be a lot of work?

 

So say I have one drop down for days of the month, one for months of the year and one for the year. How would that work? I'd need to check all three separately?

 

Thanks

[Pikachu2000]

You could use a jQuery datepicker, and put the 3 <select> fields in <noscript> tags as a backup for people who don't have JS enabled. In either case you'd still want to validate the form data as valid, just as you should be doing with all form data.

[The Little Guy]

Wouldn't that be a lot of work?

 

Programming isn't a walk in the park. If you wan't valid reliable data you need to put work into it. If you don't validate the data, what would you do if I decide I want to use the date "Monkey Pants"?

[fenway]

What you show and what you submit shouldn't be the same.