Filtering Records using Date range!!
Posted: Mon Mar 22, 2010 8:33 am
Hi ALL,
I am working on a application wher i need to filter some records using a particular date range, I have used jquery datepicker & able to get two dates (i.e. FROM: To:) now after getting this date value i need to fetch data from multiple tables, so i used joins but how i should i use Between clause for getting a date range.Here is my code:
<?php
$start_date=date("Y-m-d",strtotime($_POST['obdate']));
$end_date=date("Y-m-d",strtotime($_POST['obdate1']));
/* these are two dates in between i need to filter my records */
$sql =mysql_query("SELECT user_tree_table.user_tree_id,tree_nickname,
user_tree_observations.observation_id,user_tree_observations.date,
Species_master.species_primary_common_name
FROM user_tree_observations
INNER JOIN
(users, user_tree_table, trees,Species_master)
ON user_tree_table.user_tree_id = user_tree_observations.user_tree_id
AND user_tree_table.user_id='$_SESSION[user_id]'
AND trees.species_id=Species_master.species_id
/* stuff for date range----
AND user_tree_observations.date=(BETWEEN '$start_date' AND '$end_date')
*/
ORDER BY user_tree_table.tree_id");
while($row = mysql_fetch_array($sql))
{
print $row['user_tree_id'];
print "<td>".$row['date']."</td>";
print "<td>".$row['tree_nickname']."</td>";
}
?>
Tried to use BETWEEN but seems not to be working ,Please let me know how i can solve this problem,
Thanks In Advance!!
I am working on a application wher i need to filter some records using a particular date range, I have used jquery datepicker & able to get two dates (i.e. FROM: To:) now after getting this date value i need to fetch data from multiple tables, so i used joins but how i should i use Between clause for getting a date range.Here is my code:
<?php
$start_date=date("Y-m-d",strtotime($_POST['obdate']));
$end_date=date("Y-m-d",strtotime($_POST['obdate1']));
/* these are two dates in between i need to filter my records */
$sql =mysql_query("SELECT user_tree_table.user_tree_id,tree_nickname,
user_tree_observations.observation_id,user_tree_observations.date,
Species_master.species_primary_common_name
FROM user_tree_observations
INNER JOIN
(users, user_tree_table, trees,Species_master)
ON user_tree_table.user_tree_id = user_tree_observations.user_tree_id
AND user_tree_table.user_id='$_SESSION[user_id]'
AND trees.species_id=Species_master.species_id
/* stuff for date range----
AND user_tree_observations.date=(BETWEEN '$start_date' AND '$end_date')
*/
ORDER BY user_tree_table.tree_id");
while($row = mysql_fetch_array($sql))
{
print $row['user_tree_id'];
print "<td>".$row['date']."</td>";
print "<td>".$row['tree_nickname']."</td>";
}
?>
Tried to use BETWEEN but seems not to be working ,Please let me know how i can solve this problem,
Thanks In Advance!!