WHERE date >= '"$date1'" . AND date <='"$date2'" Problem

[suttercain]

Hi Everyone 

 

I have a form where people can select two dates, $date1 and $date2. I have the WHERE statement set so it's greater or equal to, and lesser or equal to.

 

My Issue:

When I select a date from $date1 it works fine. But when I select a date from $date2 it gives me the entire year. So if I select June 15th, 2000 through June 1st, 2007, it echos the results from

June 15th, 2000 through December 31st, 2007.

 

Here is MySQL query code:

<?php
//Define the Date Range
function date_range() {
$date1 = $_POST['year1'] . $_POST['day1'] . $_POST['month1'];
$date2 = $_POST['year2'] . $_POST['day2'] . $_POST['month2'];
echo strtoupper("<br><b>You selected: $date1 through  $date2.</b>");

require ('get_connected.php');
    $sql = "SELECT * FROM canada WHERE date >= '" . $date1 . "' AND date <='" . $date2 . "'";
    if ($result = mysql_query($sql)) {
      if (mysql_num_rows($result)) {       
      }
    }
?>

 

Does someone know how to rectify this? The BETWEEN command only gives me dates between, I would like the result to include the date selected.

 

Thanks!

 

SC

[paul2463]

you problem may be stemming from the fact that date is a mysql reserved word I believe so if you use `date` it may work better

[suttercain]

Thank you for replying Paul, good suggestion. I made the changes but sadly it's still not processing correctly.

 

Anyone, any advice?

 

Thanks.

[paul2463]

so can i get a couple of things straight

$date1 = $_POST['year1'] . $_POST['day1'] . $_POST['month1'];
$date2 = $_POST['year2'] . $_POST['day2'] . $_POST['month2'];

 

$date1 is the start date for whatever it is

$date2 is the end date for whatever it is

 

are they to select both dates before it is posted/acted on??? or can they just select one date???

 

as far as I am aware BETWEEN is INCLUSIVE  so "BETWEEN 1 AND 4" = "1, 2, 3, 4" not just "2, 3".