ba_h — 2011-01-09T23:11:21-05:00 — #1
Hi All, I'm new to PHP/MySQL and am stumped with what should be a simple process of posting a date range to a PHP script that will query data for the selected range. The script receives the two dates as confirmed when I echo for the variables. For example:
<?php echo $_POST ["date3"]; ?> to
<?php echo $_POST ["date4"];?>
This properly displays "2011-01-08 to 2011-01-09" when I set those dates in the calendar form and submit. But what I can't figure out is how to create variables for these dates and query the database for the submitted range. If I hard code a date into the query using "WHERE posttime LIKE '%2011-01-08%" the query succeeds and produces expected results:
$result = mysql_query('SELECT points, service, posttime, network, name, value FROM points, redirects WHERE points.qotdid = redirects.qotdid AND points.posttime LIKE "%2011-01-08%" ORDER BY redirects.network, redirects.name, points.posttime');
How should the query look if I want to replace the static date with the range passed from the form? I would assume:
$fromdate = $_POST['date3'];
$todate = $_POST['date4'];
$result = mysql_query('SELECT points, service, posttime, network, name, value FROM points, redirects WHERE points.qotdid = redirects.qotdid AND points.posttime BETWEEN $fromdate and $todate ORDER BY redirects.network, redirects.name, points.posttime');
but this doesn't work....what am I doing wrong?
Thanks in advance for any help.
r937 — 2011-01-09T23:19:21-05:00 — #2
BETWEEN '$fromdate' AND '$todate'
ba_h — 2011-01-09T23:34:03-05:00 — #3
Sorry I failed to include the quotes, although I tried that too...it gives me this error:
Parse error: syntax error, unexpected T_VARIABLE in /Applications/MAMP/htdocs/offers/admin/querystats.php on line 45
Any idea why? It's almost as if the 'BETWEEN' operator is not recognized - it doesn't change colors in my text editor like 'SELECT', 'FROM', 'WHERE' etc.
r937 — 2011-01-09T23:36:09-05:00 — #4
construct your SQL statement, and then, instead of executing it, echo it, then copy/paste it into mysql and see what the actual mysql error is
ba_h — 2011-01-09T23:37:24-05:00 — #5
Do I have to do anything to convert the posted date format to how the time stamps are recorded in the database? For example, the form is using 'YYYY-MM-DD' and the database includes time as in 'YYYY-MM-DD HH:MM:SS'.
Thanks for the help!
paul_wilkins — 2011-01-09T23:43:52-05:00 — #6
Your SQL string is constructed using single quotes, so you will need to use double quotes within it around the date values
$result = mysql_query('... BETWEEN "' . $fromdate '" and "' . $todate . '" ORDER BY ...');
When double quotes are used for string delimeter, you'll need to instead use single quotes inside it around the values.
$result = mysql_query("... BETWEEN '" . $fromdate "' and '" . $todate . "' ORDER BY ...");
Double quotes also allow you to make use of variable expansion as well.
$result = mysql_query("... BETWEEN '$fromdate' and '$todate' ORDER BY ...");
The strings documentation page has full details about all of this, and more.
ba_h — 2011-01-09T23:58:05-05:00 — #7
Thanks Paul! When I tried the first solution I got this error:
Parse error: syntax error, unexpected T_CONSTANT_ENCAPSED_STRING in /Applications/MAMP/htdocs/offers/admin/querystats.php on line 45
But when I tried the last, it worked!!! Thank you VERY MUCH!
paul_wilkins — 2011-01-10T00:00:33-05:00 — #8
Yes, I spotted that after posting, and fixed it right up.