Page 1 of 2

query records by date range using php variables

Posted: Sun Mar 23, 2014 11:05 pm
by kwkened
Hello,

I'm trying to query records from a mysql table using a date range selected by user of a site, like this:

Code: Select all

$query = "SELECT * `table` WHERE (`column` BETWEEN $startdate AND $enddate) ";
I'm not getting an error from the query, but I'm not getting any data either. I've verified that the date formats for the php variables are 'yyyy-mm-dd' and that there are records in the database that fall within the date range. Any insight would be greatly appreciated. Thanks.

Re: query records by date range using php variables

Posted: Mon Mar 24, 2014 5:45 am
by Celauran
Dates are strings so you will at the very least want quotes around them. Otherwise the syntax looks OK at a glance.

Re: query records by date range using php variables

Posted: Mon Mar 24, 2014 11:22 pm
by kwkened
Well I've been trying everything I can find to get this query to work properly with no success. What I've tried to do now is change the column datatype to timestamp and then converted the php variable to a timestamp for comparison. So basically `Date` is of timestamp datatype and then I'm trying to pull all the timestamp dates between the timestamp variables. Still not working though. Please advise thank you much in advance!

Code: Select all


$timeStampFrom = strtotime("$fromdate");
$timeStampTo = strtotime("$todate");

$query = "SELECT * ";
$query .= "FROM `table` ";
$query .= "WHERE (`Date` BETWEEN $timeStampFrom AND $timeStampTo)";

Also, I've verified that the date column is timestamp and that the variables are being saved as timestamps as well. I just don't know what I'm missing :/

Re: query records by date range using php variables

Posted: Tue Mar 25, 2014 9:25 pm
by kwkened
...crickets...

Re: query records by date range using php variables

Posted: Tue Mar 25, 2014 9:57 pm
by Christopher
So it was suggested that you put quotes around your dates. Instead you converted them to timestamps. Since you don't say what type of field 'Date' is then it's not clear whether timestamps will work. Since you tried dates in 'yyyy-mm-dd' format first, I am guessing that the field is of type DATE or DATETIME. So maybe try the quotes. And be careful if the fields are DATETIME and you use dates in the DATE format.

PS - is your table really named 'table' ?

Re: query records by date range using php variables

Posted: Wed Mar 26, 2014 8:24 am
by kwkened
Thank you for your reply Christopher. Yes, I did try using quotes around the string variables but thought that maybe strings aren't they way to go since I'm needing to compare the dates fields in the database with the date range between the variables. So I've changed the `Date` column of the database to timestamp, and I've converted the date string variables to timestamp for the query. Still no luck however.

... No, I just typed that stuff in to the example code to try and make it clear.

Thanks again.

P.S. My previous post clearly states that I changed the `Date` column to timestamp. Please re-read. Regards.

Re: query records by date range using php variables

Posted: Wed Mar 26, 2014 9:09 am
by Celauran
Have you tried the query directly in MySQL? The query itself looks fine to me and works fine where I've tested it (using YYYY-MM-DD format against a DATETIME field).

As an aside, I'd use DATE or DATETIME fields to store dates.

Re: query records by date range using php variables

Posted: Mon Mar 31, 2014 10:37 am
by kwkened
Thanks for the responses, I've set the Date column in the mysql database table to Datetime type, and then I manually entered the dates into the mysql query and it worked -- the records that had dates between the starting and ending dates in the query were pulled correctly and displayed on the page. So far so good. I then tried assigning those dates to php variables and used those variables in the query rather than the manually entered dates and I got nothing. So I performed a var_dump() on the variables to see if the type is being correctly set and the type is being set at datetime, which is correct, so I'm not sure why the query isn't working with the variables. I've tried all sorts of syntax adjustments and still no success. Any ideas on how I might further troubleshoot this sneaky problem? Thanks again.

Re: query records by date range using php variables

Posted: Mon Mar 31, 2014 10:46 am
by Celauran
You've examined the PHP-generated query string? Want to post some code and have us take a look?

Re: query records by date range using php variables

Posted: Mon Mar 31, 2014 10:50 am
by Christopher
One simple thing to do when working with databases is this:

Code: Select all

$query = "SELECT * `table` WHERE (`column` BETWEEN $startdate AND $enddate) ";
echo "SQL=$query<br>";
Then compare the SQL being generated with what you expected. Try the SQL directly in MySQL to see what the error is.

I also helps to check if there was an error after you execute the query and get an error message if a error occurred.

Re: query records by date range using php variables

Posted: Tue Apr 01, 2014 6:13 pm
by kwkened
Thanks for helping me isolate whatever the problem is though it still eludes me. I tried the suggestion to echo the query and this is what I got:

Code: Select all

SELECT * FROM `Table` WHERE '' >= `Date` AND `Date` >= ''
Its removing the PHP variable and leaving the single quotes. If I drop the single quotes in the query it'll display just a blank space -- no variable, no single quotes. But here's what I'm getting from the var_dump() of the two datetime variables:

Code: Select all

object(DateTime)[3]
  public 'date' => string '2014-03-12 00:00:00' (length=19)
  public 'timezone_type' => int 3
  public 'timezone' => string 'UTC' (length=3)

object(DateTime)[4]
  public 'date' => string '2014-03-19 00:00:00' (length=19)
  public 'timezone_type' => int 3
  public 'timezone' => string 'UTC' (length=3)
And again, I've double checked to make sure the date column is set to datetime type.

I also tried doing a query by a different column of type int. and I created an int type variable and plugged it into the query and I got the same result -- the variable was removed from the query when I tried to echo the query back to the browser. When plugging the integer directly into the query I got the correct result though. Something definitely amiss with the php variables in the query. and again, I tried both with single quotes and no quotes around the variables. Could there be some sort of setting that doesn't allow php variables in the mysql query? Thanks again.

Re: query records by date range using php variables

Posted: Tue Apr 01, 2014 6:23 pm
by Celauran

Re: query records by date range using php variables

Posted: Tue Apr 01, 2014 6:28 pm
by Christopher
Well ... you know from printing the SQL string the variables $startdate and $enddate are not being set. The code in your initial post had the correct string quoting (double) but was missing single quotes in the string around the dates. I am guessing that you are getting the $startdate and $enddate values from DateTime objects. Post the code that is setting $startdate and $enddate so we can see what is going on.

Re: query records by date range using php variables

Posted: Tue Apr 01, 2014 6:47 pm
by kwkened
Well I wasn't having any luck with:

Code: Select all

$query = "SELECT * `table` WHERE (`column` BETWEEN $startdate AND $enddate) ";
even with the date directly entered and single quotes or not, so I changed it to this:

Code: Select all

$query = "SELECT * FROM `Table` WHERE '$startdate' <= ` Column` AND `Column` <= '$enddate'";
and now I can at least get the correct results plugging the literal date directly in.

Re: query records by date range using php variables

Posted: Tue Apr 01, 2014 8:36 pm
by Christopher
If you don't post the code where you set $startdate and $enddate then there is really no way to tell why they are empty. I see that you have put quotes around your dates. That should at least prevent the query from erroring.