query records by date range using php variables

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

kwkened
Forum Newbie
Posts: 8
Joined: Sun Mar 23, 2014 10:53 pm

query records by date range using php variables

Post 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.
User avatar
Celauran
Moderator
Posts: 6427
Joined: Tue Nov 09, 2010 2:39 pm
Location: Montreal, Canada

Re: query records by date range using php variables

Post by Celauran »

Dates are strings so you will at the very least want quotes around them. Otherwise the syntax looks OK at a glance.
kwkened
Forum Newbie
Posts: 8
Joined: Sun Mar 23, 2014 10:53 pm

Re: query records by date range using php variables

Post 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 :/
kwkened
Forum Newbie
Posts: 8
Joined: Sun Mar 23, 2014 10:53 pm

Re: query records by date range using php variables

Post by kwkened »

...crickets...
User avatar
Christopher
Site Administrator
Posts: 13596
Joined: Wed Aug 25, 2004 7:54 pm
Location: New York, NY, US

Re: query records by date range using php variables

Post 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' ?
(#10850)
kwkened
Forum Newbie
Posts: 8
Joined: Sun Mar 23, 2014 10:53 pm

Re: query records by date range using php variables

Post 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.
User avatar
Celauran
Moderator
Posts: 6427
Joined: Tue Nov 09, 2010 2:39 pm
Location: Montreal, Canada

Re: query records by date range using php variables

Post 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.
kwkened
Forum Newbie
Posts: 8
Joined: Sun Mar 23, 2014 10:53 pm

Re: query records by date range using php variables

Post 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.
User avatar
Celauran
Moderator
Posts: 6427
Joined: Tue Nov 09, 2010 2:39 pm
Location: Montreal, Canada

Re: query records by date range using php variables

Post by Celauran »

You've examined the PHP-generated query string? Want to post some code and have us take a look?
User avatar
Christopher
Site Administrator
Posts: 13596
Joined: Wed Aug 25, 2004 7:54 pm
Location: New York, NY, US

Re: query records by date range using php variables

Post 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.
(#10850)
kwkened
Forum Newbie
Posts: 8
Joined: Sun Mar 23, 2014 10:53 pm

Re: query records by date range using php variables

Post 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.
User avatar
Celauran
Moderator
Posts: 6427
Joined: Tue Nov 09, 2010 2:39 pm
Location: Montreal, Canada

Re: query records by date range using php variables

Post by Celauran »

User avatar
Christopher
Site Administrator
Posts: 13596
Joined: Wed Aug 25, 2004 7:54 pm
Location: New York, NY, US

Re: query records by date range using php variables

Post 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.
(#10850)
kwkened
Forum Newbie
Posts: 8
Joined: Sun Mar 23, 2014 10:53 pm

Re: query records by date range using php variables

Post 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.
User avatar
Christopher
Site Administrator
Posts: 13596
Joined: Wed Aug 25, 2004 7:54 pm
Location: New York, NY, US

Re: query records by date range using php variables

Post 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.
(#10850)
Post Reply