HOT NAKED GIRLS & Databases and Time

aaron_karp

Ok, so the title was just a teaser to get you in here. But we noble PHP coders don't need hot naked girls to have fun anyway. All we need are our keyboards and good sturdy 19" monitor, right?

Here's my question:

I'm trying to select all records from a table where the date is within the current month. Problem is, the dates are stored as UNIX timestamps. Does anyone have any ideas of how to pull this off? I know that there is a date_format command-type-thing that is usable when writing queries, but I can't seem to figure it out. I wrote this little bit of code......

$join_My = date("M y");
$result = mysql_query("SELECT * FROM members WHERE date_format(joindate, '%M %y')='$join_My' ");
echo mysql_num_rows($result);

It finds the "M y" format of the current date (i.e. May 03). I then try to compare this to my date_format of the table named "joindate" which holds a unix timestamp.

This query should echo 2, but it echos 0, without any compiler errors.

Whoever finds a solution will be given links to websites where one can find HOT NAKED GIRLS.

Thanks =)

nnichols

$sql = "SELECT * 
FROM members 
WHERE DATE_FORMAT(joindate, '%m %y') = DATE_FORMAT(NOW(), '%m %y')";

Hope this helps

aaron_karp

Unfortuantely that didn't work. I still turn up 0 results. I've checked the timestamps for the entries in my database, and they all turn up 05 03 which they should. This matches the %m %y of this month as well.

$result2 = mysql_query("SELECT DATE_FORMAT(joindate, '%m %y') FROM members");
echo mysql_num_rows($result2);

//The above code displays the number 4, since there are 4 entries in my database. 2 of them have timestamps in the joindate column that should, after formatting, show 05 03

$result = mysql_query("SELECT * FROM members WHERE DATE_FORMAT(joindate, '%m %y') = DATE_FORMAT(NOW(), '%m %y') ");
echo mysql_num_rows($result);

//The above code displays the number 0

Any more thoughts?

Thanks very much, by the way.

-Aaron

Sxooter

Originally posted by aaron_karp
Whoever finds a solution will be given links to websites where one can find HOT NAKED GIRLS.

Thanks =)

that's fark's job.🙂

csn

Ok, so the title was just a teaser to get you in here. But we noble PHP coders don't need hot naked girls to have fun anyway. All we need are our keyboards and good sturdy 19" monitor, right?

Whoever finds a solution will be given links to websites where one can find HOT NAKED GIRLS.

Thanks =)

Well, they're not naked, but they are fairly hot and apparently love PHP (and not PERL)...

http://www.asleep.net/keep/A.Sleep/fuck_mod_perl.jpg

nnichols

Are you using a MySQL TIMESTAMP field or are you storing a UNX TIMESTAMP.

If you are storing the latter you need to convert the timestamp -

$sql = "SELECT * 
FROM members 
WHERE DATE_FORMAT(FROM_UNIXTIME(joindate), '%m %y') = DATE_FORMAT(NOW(), '%m %y')";

Should do the trick

nnichols

SELECT * 
FROM members 
WHERE joindate BETWEEN UNIX_TIMESTAMP(DATE_FORMAT(NOW(),'%Y-%m-01'))
	AND UNIX_TIMESTAMP(DATE_SUB(DATE_ADD(DATE_FORMAT(NOW(),'%Y-%m-01'), INTERVAL 1 MONTH), INTERVAL 1 DAY))

This will be more efficient as it will allow MySQL to use any indexes you have on the column.

aaron_karp

Thank you nnichols, those last two bits of code are just what I needed. I forgot about the difference between UNIX and MySQL timestamps. It all works now. Thanks again!