Programming Help

Web Development

Projects



What's Here?

  • Members: 99,784
  • Replies: 389,613
  • Topics: 58,612
  • Snippets: 2,047
  • Tutorials: 550
  • Total Online: 1,551
  • Members: 47
  • Guests: 1,504

Who's Online?

Welcome to Dream.In.Code
Getting PHP Help is Easy!

Join 99,784 PHP Programmers for FREE! Ask your question and get quick answers from experts. There are 1,551 online right now! We've got more than 500 tutorials and 2,000 snippets. Join and find out why Dream.In.Code is the #1 programming help community on the internet! Registration is fast and FREE... Join Now!



Mysql + PHP time help

 
 Reply to this topicStart new topic

Mysql + PHP time help
musya
post 8 May, 2008 - 12:00 PM
Post #1


D.I.C Head

Group Icon
Joined: 25 Apr, 2007
Posts: 213



Dream Kudos: 50
My Contributions
We have fields in a colum that are a date and time in this form:
CODE
05-07-08 03:07 pm


and i need to retrieve the values that are only from the last week or last 7 days, how can i retireve only that data? im not very good at mysql so sorry if im missing something very simple, and thank you guys.
User is offlineProfile CardPM

Go to the top of the page


Martyr2
post 8 May, 2008 - 12:31 PM
Post #2


Programming Theoretician

Group Icon
Joined: 18 Apr, 2007
Posts: 3,967



Thanked 45 times

Expert In: C/C++, Java, VB, VB.NET, C#, PHP, Web Development, HTML & CSS, Javascript

My Contributions
You could try something like...

php

$sevendaysago = date("Y-m-d",mktime(date("H"), date("i"), 0, date("m")  , date("d")-7, date("Y")));
mysql_query("select * from table where datecolumn >= '$sevendaysago'");



Here we use mktime to create a time 7 days in the past and then using that mktime to create a new date. We use the date in the query to say pull all records greater than or equal to this date from the past.

Try it out and play with it a little and surely you will get it working. smile.gif
User is offlineProfile CardPM

Go to the top of the page

musya
post 8 May, 2008 - 01:12 PM
Post #3


D.I.C Head

Group Icon
Joined: 25 Apr, 2007
Posts: 213



Dream Kudos: 50
My Contributions
QUOTE(musya @ 8 May, 2008 - 12:00 PM) *

We have fields in a colum that are a date and time in this form:
CODE
05-07-08 03:07 pm


and i need to retrieve the values that are only from the last week or last 7 days, how can i retireve only that data? im not very good at mysql so sorry if im missing something very simple, and thank you guys.

Thank you works perfect now.
User is offlineProfile CardPM

Go to the top of the page

mocker
post 8 May, 2008 - 01:15 PM
Post #4


D.I.C Head

**
Joined: 14 Oct, 2007
Posts: 126



Thanked 1 times
My Contributions
You might want to put the date functions directly in mysql instead of making a date in your script. MySQL has some great functions for manipulating and formating dates .. (http://dev.mysql.com/doc/refman/5.0/en/date-and-time-functions.html)

To get everything within the last seven days, you would just change your query to
CODE

select * from table where datecolumn >= date_sub(now(), INTERVAL 7 DAY)


This also avoids the complication in the above suggestion, where your mysql server/database timezone may be different than your local (or whever you run the script).

This post has been edited by mocker: 8 May, 2008 - 01:15 PM
User is offlineProfile CardPM

Go to the top of the page

Fast ReplyReply to this topicStart new topic
Time is now: 7/25/08 01:21AM
Invision Power Board v2.1.7 © 2008  IPS, Inc.
Licensed to: MediaGroup1

Live PHP Help!

PHP Tutorials

Reference Sheets

PHP Snippets

Bye Bye Ads

Free DIC T-Shirt

T-Shirt Example

Related Sites

Monthly Drawing

Thumb Drive

Partners

Top Contributors

Top 10 Kudos This Month
-->