Jump to content


Photo

help with a mysql query? Huh


  • Please log in to reply
8 replies to this topic

#1 w32

w32
  • Members
  • PipPip
  • Member
  • 28 posts

Posted 02 September 2006 - 02:32 PM

Hello guys I wondered whether you could help me with a mysql query..  :)

So...I have a field in a table, with a date int. It's a script that groups news elements by day. Then all the news for that specific day must be displayed. And I'm having trouble with that...

The int in the database field contains the info of the day, year, month etc (basically date("U") )

But I only need to get the day, to get all the news of a specific day...how could I make a query like that  ???

#2 AndyB

AndyB
  • Staff Alumni
  • Advanced Member
  • 5,465 posts
  • LocationToronto

Posted 02 September 2006 - 02:40 PM

When you say 'basically date("U")' - seconds since the Unix Epoch (January 1 1970 00:00:00 GMT) - is that actually what's in the database field or does the database field contain some sub-set or funny derived format from that?
Legend has it that reading the manual never killed anyone.
My site

#3 w32

w32
  • Members
  • PipPip
  • Member
  • 28 posts

Posted 02 September 2006 - 02:44 PM

Yip it inserts something like this =  1157230857

And I can format that date to my likings to get day/month/etc... that's why I use it. But I only need to compare the day...and I can't figure out how to make a query like that one..

thanks for your help ;)

#4 w32

w32
  • Members
  • PipPip
  • Member
  • 28 posts

Posted 02 September 2006 - 03:58 PM

a little help :(

#5 wildteen88

wildteen88
  • Staff Alumni
  • Advanced Member
  • 10,482 posts
  • LocationUK, Bournemouth

Posted 02 September 2006 - 04:14 PM

If you wnat to get the day from a timestamp use
<?php

$timestamp = time();

$day = date("j", $timestamp);

?>
Then use $day in your SQL query.

Note j in the date function returns the day without the leading zero eg (1 - 31)
if you wnat the leading zero use d

#6 w32

w32
  • Members
  • PipPip
  • Member
  • 28 posts

Posted 02 September 2006 - 05:34 PM

I figured that bit, but how do you would a comparison to the value in the database would work out?

in your example I would have to do something like

SELECT * FROM news WHERE dateint='$day'

and that wouldn't work out, would it?

#7 AndyB

AndyB
  • Staff Alumni
  • Advanced Member
  • 5,465 posts
  • LocationToronto

Posted 02 September 2006 - 05:46 PM

Life would be a lot simpler if you had stored a date yyyy-mm-dd field instead of the number of seconds since the start ot time ... the query would have been simple then.

As it is, the only route I see is to get a specific date (via form input or URL) and then process it so as to get the 'time' at 00:00 on that day and the 'time' at 23:59 on that day and then do a SELECT where yourtimefield is less than the 23:59 time AND yourtimefield is more than the 00:00 time.
Legend has it that reading the manual never killed anyone.
My site

#8 w32

w32
  • Members
  • PipPip
  • Member
  • 28 posts

Posted 02 September 2006 - 05:49 PM

yep though I think it's not impossible, wanted to know if someone knew how to do it :)

#9 AndyB

AndyB
  • Staff Alumni
  • Advanced Member
  • 5,465 posts
  • LocationToronto

Posted 02 September 2006 - 05:55 PM

... wanted to know if someone knew how to do it :)


Isn't that what I just told you?  Write a snippet of code to try.  The manual pages on the date() and time() functions tell you all you need to know.  If you run into trouble, post your code and explain the troubles you're having.
Legend has it that reading the manual never killed anyone.
My site




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users