DateTime column using a variable

[newbiePHP]

Hi guys,

 

I have a webpage where i read in a date from a user in the format yyyy-mm-dd. lets call that $userdate

 

I then have an SQL statement where i i am trying to say get all information between the userdate 04:00:00 and the day after 04:00:00.

 

in code terms

SELECT * FROM table1 WHERE DateTime BETWEEN '$Userdate 04:00:00' AND '($Userdate+1) 04:00:00'  ORDER BY `DateTime`

 

However this does not work. can anyone help me with this?

 

would be such a load of my mind.

 

thanks

[rhodesa]

first, DateTime is a reserved word, so you should avoid naming your fields this. you wrapped it properly with backticks in the ORDER clause, but forgot to in the WHERE clause. also, to get a day later you can use the following:

 

SELECT * FROM table1 WHERE `DateTime` BETWEEN '$Userdate 04:00:00' AND TIMESTAMPADD(DAY,1,'$Userdate 04:00:00') ORDER BY `DateTime`

[aschk]

I'll put this question you:

 

Q. Assuming that we adhere to your format, and do

$userdate = "2008-02-12"; // Bear in mind this is a string.

 

What happens in PHP when you do :

echo "blah blah blah $Userdate+1 blah blah blah"; // This is inside a string just like your SQL query.

[newbiePHP]

thanks rhodesa.

 

aschk, when you do that it just returns 2009. I.E the year +1

[aschk]

I'm not sure you're correct there i'm afraid.

Are you telling me that string+1 INSIDE ANOTHER STRING will give you the year component of that string?

Try what I wrote and see for yourself

 

note: there is a syntax error, the 2nd part should use $userdate, NOT $Userdate.

[aschk]

You are correct in saying as much that

 

$date = "2008-02-12";
echo $date+1;

 

gives you 2009, the question is do you know why? PHP Certainly doesn't know that the string you have given it is in a particular date format. What it actually does is converts the string to an integer by reading each character in order if it's numerical until it hits a non-numeric at which point it chops off the rest (x is an exception). So you're lucky to get 2008 from the string to int conversion.

 

If the string was "08-02-12" it would give you 9, NOT 2009...

And again, this is because you're doing the calculation outside a string.