MySQL query problem

Trying to query table where date => today

[BadGoat]

Hello!

I am trying to query my database and echo results where the date of the entry is equal to or greater than today. Currently, it kinda works, but it echoes everything. Below is my MySQL query which is in my code:

$today = date("j, n, Y");
$sqlquery = "SELECT timestamp, diwtitle, id from diw_alpha WHERE timestamp >= '$today'";

The variable 'timestamp' is a timestamp column in my table in the format:

date("Y-m-d h:i:s")

So.. I am not sure if my query echoes everything because there is a difference in date syntax ("j, n, Y") vs. ("Y-m-d h:i:s") or if there is a correct way to do the query.. Does anyone have an idea what I am not doing right?

Thanks! :)

[jusdrum]

Let MySQL do the work. Try this:

SELECT timestamp, diwtitle, id from diw_alpha WHERE timestamp >= NOW()

That way you don't have to worry about making the right format, MySQL does it for you.

Hope this helps!

[BadGoat]

Hello jusdrum!

I made that change and it still returns zero entries, despite having entries both before, during, and after today's date. Also a follow-up question when using the NOW() function, does it cut off entries that happened today but earlier in the day?

[jusdrum]

Yes, it only grabs items from the current time. I believe you can use CURDATE() instead of NOW() to pull everything from today's date.

As for no items returned, try the query in the mysql shell or through phpMyAdmin and see if it returns any results. It could tell you if there is a problem with the query.

If all else fails, change the timestamp format to datetime. This is MySQL's default date format and is easier all around to use.

[BadGoat]

I tried the CURDATE() as well, and that does not work for me.. so as you suggested, I switched to datetime instead of timestamp. I have tried both CURDATE() and NOW() and neither yield any results. When I type

SELECT timestamp, diwtitle, id from diw_alpha WHERE timestamp >= CURDATE()

or

SELECT timestamp, diwtitle, id from diw_alpha WHERE timestamp >= NOW()

I get zero results in phpmyadmin, but when I type

SELECT timestamp, diwtitle, id from diw_alpha

I get plenty. I am not sure what I am doing wrong, as I haven't used the datetime() function before.. But I am reading about it now! If you see something glaring, or have another clue, please let me know :)

Thank you!

[jusdrum]

If you change the column types from timestamp to datetime, it does not update the data in the fields to reflect the new format. Try updating the data to reflect the new format, then try using CURDATE() and NOW().

[BadGoat]

I just had a terrible thought.. None of them will shwo up anyways, since they are all created in the past (before NOW()). In reality, now that I think about it, I want to only echo entries which have an 'order_date_ after today's date. And this date is added using dropdown menus to select month, day, year, hour, and minute.. Is there a better way to do that, and which would also allow me to compare against CURDATE() or NOW()?

[BadGoat]

To prove this point to myself, I changed the query to

$sqlquery = "SELECT timestamp, diwtitle, id from diw_alpha WHERE timestamp < NOW()";

and sure enough, it works, all of thd entries are added.. I am laughing at myself now.. And trying to think of how to fix it!

[gettopreacherman]

Okay, you have your timestamp, you need to convert the values somehow or get them to show...so why don't you try...

SELECT * from [table] where timestamp >= (NOW()-1);

Just go back a day...:-)

[plateaultd]

NOW()-1 does NOT goe back a day, it goes back one second.

You can play around with this some at the MYSQL prompt to get a better idea of waht happens.

Example:
mysql> SELECT NOW()-0, NOW()-1;
+----------------+----------------+
¦ NOW()-0 ¦ NOW()-1 ¦
+----------------+----------------+
¦ 20050412173727 ¦ 20050412173726 ¦
+----------------+----------------+
1 row in set (0.00 sec)

[coopster]

Welcome to WebmasterWorld, plateaultd.

That's correct. To get one day in the past, use INTERVAL.

SELECT NOW() - INTERVAL 1 DAY;