PHP/MySQL date range search

[airsoftie]

Hi, all...

I'm building a joblog.

I want to search a range of dates (...WHERE timestamp >= $date1 AND timestamp <= $date2...).

I built my own query, and it worked using strtotime, etc..

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

$searchFrom = date("Y-m-d 00:00:00",$searchFrom);

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

$searchTo = date("Y-m-d 24:59:59",$searchTo);

However, I wanted to use DW's Master Detail Page.

(Trying to save time, not going so well...)

When I setup my query and all vars, It wigs out on the dates everytime.

Is there something in the recordset that is stripping away the formatting I put into my vars?

[nstratton]

Is the database field a date field or datetime.  Also is the user entering the date in the field like YYYY-MM-DD format?  Normally I require my users to enter the date in YYYY-MM-DD format and then on my search page I do this after creating sessions so I can use the dates in other pages:

<?php
$startdate=$_SESSION['start_date'];
$startdate=$startdate . " 00:00:00";
$enddate=$_SESSION['end_date'];
$enddate=$enddate . " 23:59:59";?>

Then my query would be timstamp between '$startdate' and '$enddate'  Make sure to have the single quotes around the date range.

[airsoftie]

Thanks. I like your method. I found out that it was the single quotes that got me.

Now, if only I could get my pagers to work. You wouldn't know how variables are passed to keep pagination correct, would you?

[nstratton]

Not sure what you mean by keeping pagination correct?  However, to pass variables to all my pages I setup sessions.  So in my example of the date range.  I setup my form to go to a page that has this code:

<?php

session_start();

$_SESSION['start_date']=$_POST['start_date'];

$_SESSION['end_date']=$_POST['end_date'];

header ('Location: search_date.php');

?>

With this code I set up two sessions for start date and end date and then forward the user on.  Each page after that must start with <?php session_start();?>

Now that the session is stored I can use those variables on all of my pages.  This is very usefull when you want take the person to a page and let them view brief details and then forward on to more details and the ability to come back to your original search page.

Also usefull if you are updating a record and then on the next page want to setup an email script.

[airsoftie]

Oh, I see! You already pointed out sessions.

It works now. Here's my solution...

session_start();
if(!empty($_POST['searchFrom']))
    {
    $searchFrom=$_POST['searchFrom'];
    $searchFrom=$searchFrom." 00:00:00";
    $_SESSION['$searchFrom']=$searchFrom;
    }
if(!empty($_POST['searchTo']))
    {
    $searchTo=$_POST['searchTo'];
    $searchTo=$searchTo." 23:59:59";
    $_SESSION['$searchTo']=$searchTo;
    }

[nstratton]

That will work. The only problem I found is that I have to have a middle page set the session and then forward the user on. I found if the user refreshed the page and the post data was gone, then the session gets reset. This usually happens on a page that has an insert or update record. However, I see by you putting the if statement in there you are avoiding that. That is good thinking!