Problem selecting recordsets between entered dates

[Guest]

Hi,

I am using Adobe Dreamweaver CS4 with PhP and Mysql for developing my application.The situation that I have here is that I have 2 textfields where user will enter Range of dates ( Date from and Date To) to filter and view only the tickets that were open between those dates.I have a Timestamp column in my Mysql table and I am using function DATE() to get the Date Part. Now I want this tickets to be displayed only if it was opened between the dates entered by the user.To Do this I am creating a recordset in dreamweaver, where I capture the values entered in those text box in Date Variables(dt1 and dt2). Also, I want to be able to set a default value for the Date variable so that if nothing is entered by the user then it displays everything.

I am defining one of my variable as

Name:dt1

Type: Date

Default Value:This is a point of confusion, should I enter % or 0000-00-00 or I enter a pretty wide range of date so that if nothing is selected it automatically displays all records.

Run Time Value:$_POST['date_1']

In my Mysql query, I am just saying where Date( Timstamp field) BETWEEN dt1 and dt2

It doesnt seem to be working out, coz even when I put dummy runtime values and try to test it from dreamweaver then also it doesnt seem to be working.

I would really appreciate if someone could help me out with this.

[Guest]

You are trying to create complex search query. But it's okay, it's not really complex since it just have two fields(dt1 and dt2). Try to apply this search query. Assuming u have table named as 'tickets' with 'date' variable where u want to search it.

$query = 'SELECT * FROM tickets';

$where = false;

//input from date_from

if (isset($_GET['dt1']) && !empty($_GET['dt1'])) {

$query .= ' WHERE date >= ' . $_GET['dt1'];

$where = true;

}

//input from date_to

if (isset($_GET['dt2']) && !empty($_GET['dt2'])) {

if ($where) {

$query .= ' AND ';

} else {

$query .= ' WHERE ';

$where = true;

}

$query .= 'date <= ' . $_GET['dt2'];

}