This should work:

SELECT * FROM items WHERE TO_DAYS(NOW()) - TO_DAYS(submitDate) <= 7;

> -----Ursprungligt meddelande-----
> Fran: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]For Russ Michell
> Skickat: den 23 augusti 2001 11:46
> Till: Gremlins Mailing List
> Kopia: [EMAIL PROTECTED]
> Amne: Re: [PHP-DB] Query construction (again)
>
>
> (Apologies for cross-postings here but I lost a php-db list
> members personal email address..)
>
> I need a query that in English would read something like:
>
> "Select all records from table: 'items' where each record is
> displayed for 7days after it's
> submission."
>
> It was suggested I may have to modify the output of now() to
> match my MySQL DB 'submitDate' field
> as in the query below:
>
> $sql = "SELECT * FROM $tabitem WHERE DATE_ADD(submitDate,
> INTERVAL 7 DAY) =
> now()";
>
> So I tried the following:
>
> $sql = "SELECT * FROM $tabitem WHERE DATE_ADD(submitDate,
> INTERVAL 7 DAY) = DATE_FORMAT(NOW(),'Y-M-D')";
>
> MySQL didn't complain but nor did it print out all postings
> submitted in the last seven days which
> is what it is suppposed to be doing!
>
> The 'submitDate' field is a MySQL DATE field and I'm using
> MySQL-3.22.32 if that's any use.
> Why is the query not doing what it's told!!?
>
>
> Cheers for your help thus far!
> Russ
>
> Depending on how the date is stored (date + time, or just date)
> On Wed, 22 Aug 2001 21:39:19 +0800 Gremlins Mailing List
> <[EMAIL PROTECTED]> wrote:
>
> > ----- Original Message -----
> > From: Russ Michell <[EMAIL PROTECTED]>
> > To: <[EMAIL PROTECTED]>
> > Sent: Wednesday, August 22, 2001 10:16 PM
> > Subject: [PHP-DB] Query construction (again)
> >
> >
> > > Hey there folks - similar problem - different project!
> > >
> > > I want to select some records for a period of 7days after their insert
> > [dateFrom] date.
> > > Last time I asked you guys for help I was helped toward the following
> > solution:
> > >
> > > $sql = "SELECT * FROM $Tpostings WHERE now()>=dateFrom AND
> now()<dateTo";
> > >
> > > The problem in this new project is that the 'dateTo' field is
> not included
> > in the DB. It is 7-days
> > > after 'dateFrom'. So why does the following query not work:
> > >
> > > $sql = "SELECT * FROM $tabitem WHERE DATE_ADD(submitDate, INTERVAL 7
> > DAY)";
> > > No error is received though...
> >
> > There is no comparison in your WHERE clause. Try something like:
> >
> > $sql = "SELECT * FROM $tabitem WHERE DATE_ADD(submitDate,
> INTERVAL 7 DAY) =
> > now()";
> >
> > Depending on how the date is stored (date + time, or just date)
> you may have
> > to modify the output of now() to match.
> >
> > hth
> > --
> > Jason Wong
> > Gremlins Associates
> > www.gremlins.com.hk
> >
> >
> >
> >
> > --
> > PHP Database Mailing List (http://www.php.net/)
> > To unsubscribe, e-mail: [EMAIL PROTECTED]
> > For additional commands, e-mail: [EMAIL PROTECTED]
> > To contact the list administrators, e-mail: [EMAIL PROTECTED]
> >
>
> #-------------------------------------------------------#
>
>   "Believe nothing - consider everything"
>
>   Russ Michell
>   Anglia Polytechnic University Webteam
>
>   e: [EMAIL PROTECTED]
>   w: www.apu.ac.uk/webteam
>   t: +44 (0)1223 363271 x 2331
>
>   www.theruss.com
>
> #-------------------------------------------------------#
>
>
> --
> PHP Database Mailing List (http://www.php.net/)
> To unsubscribe, e-mail: [EMAIL PROTECTED]
> For additional commands, e-mail: [EMAIL PROTECTED]
> To contact the list administrators, e-mail: [EMAIL PROTECTED]
>
>


-- 
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
To contact the list administrators, e-mail: [EMAIL PROTECTED]

Reply via email to