Thanks for that - but I just sorted it literally 10 seconds ago! I used:
$sql = "SELECT * FROM $tabitem WHERE DATE_ADD(submitDate, INTERVAL 8 DAY) > CURDATE()";
Something happens with using INTERVAL 7 DAY though, in that only 6 days worth of posts
are
displayed, so 8 will have to suffice!
Thanks very much!
Russ
On Thu, 23 Aug 2001 11:09:32 +0200 Torgil Zechel <[EMAIL PROTECTED]> wrote:
> 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]
>
#-------------------------------------------------------#
"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]