Attempting to pull results from database based on TODAY's date.

I'm not sure why it isn't working - probably something simple but need help

[mylungsarempty]

I think the code is self explanatory here; thanks in advance for any advice you can offer...

mysql_select_db("d60648383", $con);

echo "<br>Today's date is: ";
echo date("m/d/Y");

echo "<br><br>Browse local shows by:<form><input type='radio'>Date &nbsp; &nbsp; <input type='radio'>Venue &nbsp; &nbsp;<input type='radio'>Age Allowed<br>";

echo "<br><table border='0' align='left' cellpadding='4' cellspacing='1' width='97%' style=''>";

$currenttime = time();

date("m/d/Y", $currenttime);

$today = date("m/d/Y");

$result = mysql_query("SELECT * FROM SHOWS WHERE DATETIME = $today ORDER BY DATETIME DESC");

I really appreciate your help.

[Readie]

"SELECT * FROM SHOWS WHERE DATETIME = $today ORDER BY DATETIME DESC"

For anything not an integer you need to quote the data:

"SELECT * FROM SHOWS WHERE DATETIME = '$today' ORDER BY DATETIME DESC"

[Demaestro]

mlae,

a few questions.

What datatype is the field DATETIME?

What does the output of $today look like?
(do a print $today and give us the output)

What does the output of DATETIME look like?
(do a "select DATETIME from SHOW limit 5" and give us the output)

Once we have this info it will be much easy to see if they are formatted the same and would return a match and if not what we need to do to make them match.

[rocknbil]

^ ^ Right . . . doesn't look like it's date format

echo date("m/d/Y");

and you are denying yourself a whole list of date and time sorting and date_math functions by using it as varchar or text.

Most of the time it's done like this because you want mm/dd/yyyy, mysql has an easy fix for that too (example below)

alter table SHOWS change `DATETIME` date_time date not null default '0000-00-00';

Note I changed the name too, datetime is a mysql format and generally won't work unless you backtick it.

Now you can do all sorts of cool things like

select date_format(date_time,"%m/%d/%Y") where date_time=curdate();

-> 05/07/2010

// yesterday

select date_format(date_sub(date_time, interval 1 day), "%m/%d/%Y") where date_time=curdate();

-> 05/06/2010

// last 5 entries from today back, most recent first

select date_format(date_time, "%m/%d/%Y") where date_time <= curdate() order by date_time desc limit 5;

-> 05/07/2010
05/07/2010
05/06/2010
05/02/2010
04/28/2010

Since you labeled it DATETIME, if you wish, all of the above is also true for the datetime format, and can be formatted as well.

alter table SHOWS change `DATETIME` date_time datetime not null default '0000-00-00 00:00:00';

date_format() [dev.mysql.com]

date_sub() [dev.mysql.com]

curdate() [dev.mysql.com]

[mylungsarempty]

Thank you for the feedback. Let me use it to rephrase my question then...

How would I retrieve today's date, and list Unix timestamp integers from that date, without regard to the time of day? With the way I have my site built, I need to list Unix timestamp values from a specific date. Surely this isn't impossible to do?

[g1smd]

05/07/2010 - May 7th in the US, but 5th July in UK!

Try using 2010-05-07 (for 2010-May-07 example date) as that's shown in RFC 3339 and other places. It's often the best format to use on the web, as it ALWAYS runs YYYY-MM-DD.

It retains the US mm/dd ordering while moving the year first. This allows an alpha sort to also automatically sort by date.

[mylungsarempty]

Thank you, but that's not the solution I'd like to find, though. No way to select Unix timestamp from database for specific date?

[rocknbil]

Not **exactly** following you, but are you saying the data type in your database is unix timestamp?

All the functions I listed above perform exactly the same on timestamp data types as they do date/datetime. Had I known that, your problem is easily solved by this, did you try it?

SELECT * FROM SHOWS WHERE DATETIME = curdate() ORDER BY DATETIME DESC

--------------------------
OK, couldn't resist, don't work much with timestamp in mySQL so had to test it:

create table test (date_time timestamp not null);

insert into test values(curdate());
insert into test values (date_sub(curdate(),interval 1 day));
insert into test values (date_sub(curdate(),interval 5 day));

select date_format(date_time, "%m/%d/%Y") from test order by date_time desc;

--> 05/08/2010
05/07/2010
05/03/2010

I re-tested using now() for the inserts, which is a datetime value, and it worked exactly the same, but when you do this,

select date_format(date_time, "%m/%d/%Y %r") from test order by date_time desc;

You get 12:00:00 for the ones that were inserted with curdate(), and an actual time for the ones inserted with now() (example: 05/06/2010 05:48:57 PM).

Yeah, it works. :-)

[mylungsarempty]

The date and time is stored as an integer named DATETIME. The format you would use mktime() with. I'm trying to determine how to select date/time's of a certain day, but not a certain time. It's a little confusing to me but I'd like to understand it.

[rocknbil]

Well, do you have many records in it yet? I'd suggest changing the type to timestamp or one of the valid date types. Otherwise you'd probably have to do a mktime() every time and do a string match (I think, I'd just never do it that way.)