Getting date range's

[Esqulax]

Hiya... is it possible to get a date, but use mathematical operators to select the range?

f'rinstance:

SELECT from db * WHERE var = ['$var_assigned_earlier'] AND date1 >=Today AND date2<= (today+7)

i know today +7 can be done before this using strtotime(), and i can just pass the variable striaght into it.

plus for this to work, im guessing it;d be easiest to store the dates put in as a timestamp.

After i have that info, id like to be able to count how many records ive gotten...

(i have a habit of... thinking aloud on forum posts :p)
Id appreciate any thoughts

[whoisgregg]

Absolutely and strtotime is the easiest function to use to accomplish it. Your final query would look something like this:

$sql = "SELECT * FROM `db` WHERE `var`='".mysql_real_escape_string($var_assigned_earlier)."' AND `date1`>='".date("Y-m-d")."' AND `date2`<='".date("Y-m-d", strtotime("+7 days"))."';"

If your DATE fields are actually DATETIME fields, then use "Y-m-d H:i:s" for the date() function.

[Esqulax]

I modified that slightly, as i worked out it'd be pointless to use the var assigned earlier
i get
Parse error: syntax error, unexpected T_CONSTANT_ENCAPSED_STRING

$sql1= "SELECT * FROM guest_data WHERE arr_date >= ".date('y-m-d')"";

In my database arr_date is a DATE field and is in the format y-m-d

::EDIT::
Now worries... for got a full-stop (period in america-talk)
(i hate when that happens)

[edited by: Esqulax at 4:22 pm (utc) on Feb. 18, 2008]

[Esqulax]

Rightio...
Got it

Now im confused as to why this part aint working....
I want it to:

Select records where the room number is = to the room number gotten from the previous sql query AND whos arr_date is today or later. (i think this bit works)

I then want it to display §§ in an new cell, when dep_date, is >= today, today+1, today +2...up to 7

CODE

while($row = mysql_fetch_array($rs))
{
echo("<tr>");
echo("<td align=\"center\"> " .$row["room_number"]);
echo("<td align=\"center\">" .$row["beds"]);

$current_room= $row["room_number"];

$sql1= "SELECT * FROM guest_data WHERE room=$current_room AND arr_date >= ".date('y-m-d')."";
$rs2= mysql_query($sql1,$conn);

while($row= mysql_fetch_array($rs2))
{
for($k=0;$k<8;$k++)
{
if ($row["dep_date"] >= date('y-m-d',strtotime('{+$k} days')))
{
echo(' <td> §§');
}
}

}
}

[cameraman]

I think your problem is here:
strtotime('{+$k} days')

Try moving the + outside the braces and change to double-quotes:
strtotime("+{$k} days")

[Esqulax]

Good call...
however.. now i get §§ in every cell for the 8 days(well.. 7 now)
Im not sure about the line:

if ($row["dep_date"] >= date('y-m-d',strtotime("+{$k} days")))

That SHOULD run if the dep_date is today or tomorrow, and up to $k days, if not, it should return nothing.. but i dont think its comparing the values... any ideas?

I put in

 else echo(' <td> xx'); 

after the if staement... theoretically this put xx in cells if theres nothing there... doesnt do this either....

[edited by: Esqulax at 6:11 pm (utc) on Feb. 18, 2008]

[cameraman]

Ah, I wasn't paying enough attention - I was looking for syntax-type errors instead of at the bigger picture.
Use strtotime to turn $row['dep_date'] into a timestamp. Remove the date() call in the if so that you're comparing two timestamps.

[Esqulax]

YES!
High five Mr cameraman!