PHP query between two dates

Skeelo

I'm wanting to list contents from a database by setting two date boundries. i thought there might be a way to just in the query put a between... Example ->

$query =  "SELECT * FROM ".$tour_db." WHERE tour='1' and date BETWEEN ".$list_date." AND ".$next_year." ORDER by date asc";

Can someone please help me... I'm stuck.
Thanks

lazzerous

$query =  "SELECT * FROM ".$tour_db." WHERE tour='1' and date > ".$list_date." AND date < ".$next_year." ORDER by date asc";

Skeelo

i've tried that and it will list the results untill i add the second date var to limit and then it wont list anything but it will keep returning down the screen without showing anything.

This Works -

$query = "SELECT * FROM ".$tour_db." WHERE tour='1' and date >= '".$list_date."'  ORDER by date asc";

This doesn't -

$query = "SELECT * FROM ".$tour_db." WHERE tour='1' and date > '".$list_date."' AND date < '".$next_year."' ORDER by date asc";

kburger

Can I see your table structure and all relevant code? It sounds like the dates may not be stored in an actual MySQL date field.

Skeelo

here is the code i am working with. If you can help me clean it up that would be great to. I am wanting it to list one set of tour dates , from todays date to the begining of next year, then list one more year of dates.

	<body bgcolor="#acd6f2" text="#25303a">
		<div align="center">
			<table width="550" border="0" cellspacing="0" cellpadding="0">
<?php
//+++++++++++++++++++++++++++++++++++++++++++
//Lists NEW and Coming Dates
//+++++++++++++++++++++++++++++++++++++++++++

$this_year = date("Y");
$next_year = $this_year+1;
$prev_year = $this_year-1;

$date = "1";

while ($date!="") { 
if($t==""){
$t="1";
$list_date = date("Y-m-d");
}elseif ($t=="2"){
$this_year = $this_year+1;
$list_date = $this_year;
$t="3";
}else{
exit();
}
if($t=="1"){
			$query = "SELECT * FROM ".$tour_db." WHERE tour='1' and date >= '%".$list_date."%' ORDER by date asc";
$t="2";
			 //"SELECT * FROM ".$tour_db." WHERE tour='1' and date between '%".$list_date."%' ORDER by date asc";
}else{
			$query = "SELECT * FROM ".$tour_db." WHERE tour='1' and date like '%".$list_date."%' ORDER by date asc";
}			
			$result = mysql_query ($query) or die ( mysql_error ());
	$row1 = mysql_fetch_array($result);
	$id1 = $row1['id'];
	if($id1 != ""){		
?>
	<tr>
		<td valign="top" width="10"></td>
		<td colspan="2" align="left" valign="bottom">
			<div align="right">
				<font size="+2" color="#93bdd9" face="Arial,Helvetica,Geneva,Swiss,SunSans-Regular"><b><?php echo $this_year; ?> Tour</b></font></div>
		</td>
	</tr>
<?php 
		while($row = mysql_fetch_array($result)) {
			$nid = $row['id'];
			$t_time = $row['time'];
		$t_city = $row['tour_city'];
		$t_state = $row['tour_state'];
		$t_event_name = $row['event_name'];
		$end_date = $row['end_date'];
		$date = $row['date'];
		$ncontent = $row['content'];		
		$date = explode ("-", $date);				
								$date_m = $date[1];
								$date_d = $date[2];
								$date_y = substr($date[0], 2);
								$ndate = $date_m.".".$date_d.".".$date_y;	



?>
	<tr height="30">
		<td align="left" valign="middle" <?php if ($line == "") {echo"bgcolor='#93bdd9'"; } else { echo ""; } ?> width="10" height="30"></td>
		<td align="left" valign="middle" <?php if ($line == "") {echo"bgcolor='#93bdd9'"; } else { echo ""; } ?> width="65" height="30"><font size="-1" face="Arial,Helvetica,Geneva,Swiss,SunSans-Regular"><b><?php echo $ndate; if($end_date != "0") { if($end_date < "10") {echo "-0".$end_date;} else { echo "-".$end_date;}} ?></b></font></td>
		<td valign="middle" <?php if ($line == "") {echo"bgcolor='#93bdd9'"; } else { echo ""; } ?> height="30"><font size="-1" face="Arial,Helvetica,Geneva,Swiss,SunSans-Regular">- <?php echo $t_event_name; if($t_city != ""){echo " - ".$t_city;} if($t_state != ""){echo ", ".$t_state;} if($t_time != ""){echo " - ".$t_time;} ?></font></td>
	</tr>
<?php

			if ($line == "") { $line = "1"; } else { $line = "";}

}

}
?>
	<tr>
		<td valign="top" width="10"></td>
		<td colspan="2" align="left" valign="bottom"><br></td>
	</tr>
<?php

}
?>
</table>

	</div>
</body>

The date from the database is in the date format YYYY-MM-DD

kburger

// between now and the end of this year
$query = "SELECT * FROM ".$tour_db." WHERE tour='1' AND (date BETWEEN NOW() AND '".date{"Y-12-31"}."') ORDER BY date ASC";

// next year
$query = "SELECT * FROM ".$tour_db." WHERE tour='1' AND (date BETWEEN '$next_year-01-01' AND '$next_year-12-31') ORDER BY date ASC";