Between....

jdh

I am trying to find a way that I can get the values in a database where they are between 2 dates. The form before this has them select 2 dates, from, and to. All the values that I am trying to select between are in the mysql date format, YYYY-MM-DD. This is what I am trying, and it doesn't work.

$sql = "SELECT * FROM dates WHERE BETWEEN $from AND $to";

It does not return any errors, or any values. Anyone know how I could get all the data from one date, to another?

TGixer

jdh

looks like your missing part of the where clause

$sql = "SELECT * FROM dates WHERE BETWEEN $from AND $to";

select * from dates Where (field name) between $from and $to;

jdh

I tried putting in the field in there, but I still get no result back. I know for a fact I have some in that range too. Here is my code:

$from = "$fr_year-$fr_month-$fr_date";
$to = "$to_year-$to_month-$to_date";
$sql = "SELECT customer, date_expected, amount, contracts, date_recd, amount_recd FROM check_forecast WHERE date_expected BETWEEN $from AND $to ORDER BY customer";
$result = mysql_query($sql);
if(!$result) { echo mysql_error(); exit(); }

The field date_expected is in the mysql date format, YYYY-MM-DD. Before this page, a user selects the dates in the drop down format, first by month, date, and year. So I made it format the values in the mysql date format.

Any suggestions?

Thanks in advance,

thompsonsco

Here is my solution to your similar problem. I think the difference is the single quotes.

I am passing variables from the input page that are dropdown lists of start month then day then year. Followed by an end month, day, year. I construct the MySQL date format using posts.

The table I am pulling from is called main.

$StartDate = $_POST['YearStart'] . "-" . $_POST['MonthStart'] . "-" . $_POST['DayStart'];
$EndDate = $_POST['YearEnd'] . "-" . $_POST['MonthEnd'] . "-" . $_POST['DayEnd'];
$sensors = $_POST['SensorList'];

$query_DataRequest = "SELECT * FROM main WHERE 
main.`Date` BETWEEN '$StartDate' AND '$EndDate' ORDER BY 
main.`Date`, main.`Time`";

This thread is kind of old, but I hope it helps someone.