SQL query with date? little help [SOLVED]

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
synical21
Forum Contributor
Posts: 150
Joined: Tue Jul 28, 2009 8:44 am
Location: London UK

SQL query with date? little help [SOLVED]

Post by synical21 »

Hey gurus,

I would like to know how to make a query with time involved, online searches are just confusing me and im not getting far. The objective is to create a query which finds all records 7 days old. The dates are stored in the database like "2010-01-31".

So the query would probably have to use curdate() then + 7 days or more, i dunno.

Can anyone give me an example? I appreciate it.

Regards
Tony
Last edited by synical21 on Wed Feb 03, 2010 1:16 pm, edited 1 time in total.
User avatar
social_experiment
DevNet Master
Posts: 2793
Joined: Sun Feb 15, 2009 11:08 am
Location: .za

Re: SQL query with date? little help

Post by social_experiment »

So the query would probably have to use curdate() then + 7 days or more, i dunno.
Im guessing you want records 7 days older than the current day. Here's how I would start;

Code: Select all

<?php 
 $sevenDayVariable = date('Y-m-d', time()-604800);
 $query = mysql_query("SELECT * FROM table WHERE datefield = curdate() AND datefield >= '".$sevenDayVariable."' ");
 ?> 
 
“Don’t worry if it doesn’t work right. If everything did, you’d be out of a job.” - Mosher’s Law of Software Engineering
User avatar
social_experiment
DevNet Master
Posts: 2793
Joined: Sun Feb 15, 2009 11:08 am
Location: .za

Re: SQL query with date? little help

Post by social_experiment »

This should work better :

Code: Select all

<?php $sevenDayVariable = date('Y-m-d', time()-604800);    
    $query = mysql_query("SELECT * FROM dates WHERE date <= CURDATE() AND date >= '".$sevenDayVariable."' "); ?>
“Don’t worry if it doesn’t work right. If everything did, you’d be out of a job.” - Mosher’s Law of Software Engineering
User avatar
pickle
Briney Mod
Posts: 6445
Joined: Mon Jan 19, 2004 6:11 pm
Location: 53.01N x 112.48W
Contact:

Re: SQL query with date? little help

Post by pickle »

Keep it simple & do it all in the query with DATE_SUB
Real programmers don't comment their code. If it was hard to write, it should be hard to understand.
synical21
Forum Contributor
Posts: 150
Joined: Tue Jul 28, 2009 8:44 am
Location: London UK

Re: SQL query with date? little help

Post by synical21 »

Thanks for the replied, i will try use them now. :)
synical21
Forum Contributor
Posts: 150
Joined: Tue Jul 28, 2009 8:44 am
Location: London UK

Re: SQL query with date? little help

Post by synical21 »

Thanks for the help i have done it now :D

Code: Select all

 
SELECT * FROM table WHERE `date` BETWEEN DATE_SUB( CURDATE( ) ,INTERVAL 7 DAY ) AND CURDATE( )
 
For those who need it.
Post Reply