BETWEEN two fields

[timmah1]

How can I grab everything from two fields if a variable falls between them?

 

Meaning, I set the start time and end time in military time in the database

Start Time 13:00

End Time 13:30

 

Then, on the script, I declare the current time

$Dtime = date("G:i");

 

How can I query the database to grab everything that the current time is between start time and end time?

 

This is what I have, but it is not working

<?php
$query3 = "SELECT * FROM specials1 WHERE starttime <= '$Dtime' AND endtime >= '$Dtime' ORDER BY 'id' ASC LIMIT 1";
$w = mysql_query($query3);
$numrows1 = mysql_num_rows($w);
	if($numrows1 == 0) {
		echo "Nothing 2";
	}
?>

 

I thought using the BETWEEN query would be easier, but I cannot figure out how to use it.

 

Any help would be greatly appreciated.

 

Thanks in advance

[rhodesa]

what type of field is 'starttime' and 'endtime'?

[timmah1]

they're both varchar, and the time is listed as military time.

 

I'm not 100% positive on how to do this, but I'm working around.

 

If there are no articles posted with any times, then it's suppose to show the last one posted.

 

Here is my entire code, it seems to be working. I'm sure there's an easier way to do this

 

<?php
$Ddate = date("Y-m-d");
$Dtime = date("G:i");

$query1 = "SELECT * FROM specials WHERE sport = '$sport' AND startdate = '$Ddate'";
$w1 = mysql_query($query1);
$numrows = mysql_num_rows($w1);

	if($numrows == 0){		
		//echo "Nothing Posted For Today ";
		$query2 = "SELECT * FROM specials WHERE sport = '$sport' ORDER BY `id` DESC LIMIT 1";
		$w = mysql_query($query2);
		while ($b = mysql_fetch_array($w)) {
		$id = $b['id'];

		$title = $b['title'];
		$special = $b['special'];

		$startY = date("Y", strtotime($b['startdate']));	
		$startD = date("d", strtotime($b['startdate']));	
		$startM = date("m", strtotime($b['startdate']));
		$startT = $b['starttime'];	
		$time1 = $b['time1'];

		$endY = date("Y", strtotime($b['enddate']));	
		$endD = date("d", strtotime($b['enddate']));	
		$endM = date("m", strtotime($b['enddate']));
		$endT = $b['endtime'];	
		$time2 = $b['time2'];

		$sport = $b['sport'];

		$team01 = $b['team1'];
		$team02 = $b['team2'];	
		$overunder = $b['overunder'];
		$favorite = $b['favorite'];
		$lockdate = date("F j, Y", strtotime($b['lockdate']));
		$locktime = $b['locktime'];
		}


	}
	elseif($numrows > 0){
		//echo "Something Is Posted For Today";
		$query3 = "SELECT * FROM specials WHERE sport = '$sport' AND '".date("G:i")."' BETWEEN starttime AND endtime ORDER BY 'id' DESC LIMIT 1";
		$w = mysql_query($query3);

		$numrows2 = mysql_num_rows($w);
			if($numrows2 == 0) {
			$query2 = "SELECT * FROM specials WHERE sport = '$sport' ORDER BY `id` DESC LIMIT 1";
				$w = mysql_query($query2);
				while ($b = mysql_fetch_array($w)) {
				$id = $b['id'];

				$title = $b['title'];
				$special = $b['special'];

				$startY = date("Y", strtotime($b['startdate']));	
				$startD = date("d", strtotime($b['startdate']));	
				$startM = date("m", strtotime($b['startdate']));
				$startT = $b['starttime'];	
				$time1 = $b['time1'];

				$endY = date("Y", strtotime($b['enddate']));	
				$endD = date("d", strtotime($b['enddate']));	
				$endM = date("m", strtotime($b['enddate']));
				$endT = $b['endtime'];	
				$time2 = $b['time2'];

				$sport = $b['sport'];

				$team01 = $b['team1'];
				$team02 = $b['team2'];	
				$overunder = $b['overunder'];
				$favorite = $b['favorite'];
				$lockdate = date("F j, Y", strtotime($b['lockdate']));
				$locktime = $b['locktime'];
				}


			}
		while ($b = mysql_fetch_array($w)) {

		$id = $b['id'];

		$title = $b['title'];
		$special = $b['special'];

		$startY = date("Y", strtotime($b['startdate']));	
		$startD = date("d", strtotime($b['startdate']));	
		$startM = date("m", strtotime($b['startdate']));
		$startT = $b['starttime'];	
		$time1 = $b['time1'];

		$endY = date("Y", strtotime($b['enddate']));	
		$endD = date("d", strtotime($b['enddate']));	
		$endM = date("m", strtotime($b['enddate']));
		$endT = $b['endtime'];	
		$time2 = $b['time2'];

		$sport = $b['sport'];

		$team01 = $b['team1'];
		$team02 = $b['team2'];	
		$overunder = $b['overunder'];
		$favorite = $b['favorite'];
		$lockdate = date("F j, Y", strtotime($b['lockdate']));
		$locktime = $b['locktime'];

}			
}


?>

[kickstart]

Hi

 

Between should work, but not sure on what you mean by a "military" time.

 

<?php
$query3 = "SELECT * FROM specials1 WHERE '$Dtime' BETWEEN starttime AND endtime ORDER BY 'id' ASC LIMIT 1";
$w = mysql_query($query3);
$numrows1 = mysql_num_rows($w);
	if($numrows1 == 0) {
		echo "Nothing 2";
	}
?>

 

All the best

 

Keith

[timmah1]

Thank for the reply kickstart, but that don't do. I tried that and realized that '$Dtime' isn't a field in the database, so it returns nothing.

 

Military time, meaning if it's 2:00pm, in military time it is 14:00

[kickstart]

Hi

 

Shouldn't matter as it should have substituted the value of $Dtime into it.

 

All the best

 

Keith