MySQL Date Field Help Needed – PHP

  php

Q(Question):

I have a Date type in my MySQL table – I’m trying to do a query on all
rows within the last 30 days based on that Date field – I’m having
trouble figuring out how to form the query???

$php_SQL = "SELECT * FROM basics WHERE creation_date =
DATE_ADD(creation_date, INTERVAL -30 DAY)";

….doesn’t do anything…

Thanks for any help.

A(Answer):

* Ralph Freshour <ra***@primemail.com>:

I have a Date type in my MySQL table – I’m trying to do a query on all
rows within the last 30 days based on that Date field – I’m having
trouble figuring out how to form the query???

$php_SQL = "SELECT * FROM basics WHERE creation_date =
DATE_ADD(creation_date, INTERVAL -30 DAY)";

…doesn’t do anything…

I’m not too familiar with date calculations in MySQL, but perhaps changing
"creation_date =" to "creation_date >=" will solve your problem.


Benjamin D. Esham { http://bdesham.net
bd*****@iname.com } AIM: bdesham 1 2 8
"Hmm… Earl Grey tea. Maybe that’s how an old
bald guy can kick the Borg’s collective ass."
— bsharitt on Slashdot

A(Answer):

No, that didn’t work…any other thoughts???
On Sun, 31 Aug 2003 22:35:55 -0400, Benjamin Esham <bd*****@iname.com>
wrote:

* Ralph Freshour <ra***@primemail.com>:

I have a Date type in my MySQL table – I’m trying to do a query on all
rows within the last 30 days based on that Date field – I’m having
trouble figuring out how to form the query???

$php_SQL = "SELECT * FROM basics WHERE creation_date =
DATE_ADD(creation_date, INTERVAL -30 DAY)";

…doesn’t do anything…

I’m not too familiar with date calculations in MySQL, but perhaps changing
"creation_date =" to "creation_date >=" will solve your problem.

A(Answer):

Ralph Freshour <ra***@primemail.com> wrote:

I have a Date type in my MySQL table – I’m trying to do a query on all
rows within the last 30 days based on that Date field – I’m having
trouble figuring out how to form the query???

$php_SQL = "SELECT * FROM basics WHERE creation_date =
DATE_ADD(creation_date, INTERVAL -30 DAY)";

You’re searching rows which are exactly 30 days old – if you want all rows
within the last 30 days use:

SELECT * FROM basics WHERE creation_date > DATE_ADD(creation_date, INTERVAL
-30 DAY);

or:

SELECT * FROM basics WHERE creation_date > DATE_SUB(creation_date, INTERVAL
30 DAY);

HTH;
JOn

A(Answer):

Hi Jon –

I tried both of your suggestions but neither worked – I doubled
checked my MySQL table col (field) data and all the dates are of Date
type and they are all in the month of August 2003 (varying days; 5th,
10th, etc.)

I don’t have to tell the query to compare the last 30 days from today?
That’s assumed in the query right?
On Mon, 01 Sep 2003 10:24:33 +0100, Jon Kraft <jo*@jonux.co.uk> wrote:

Ralph Freshour <ra***@primemail.com> wrote:

I have a Date type in my MySQL table – I’m trying to do a query on all
rows within the last 30 days based on that Date field – I’m having
trouble figuring out how to form the query???

$php_SQL = "SELECT * FROM basics WHERE creation_date =
DATE_ADD(creation_date, INTERVAL -30 DAY)";

You’re searching rows which are exactly 30 days old – if you want all rows
within the last 30 days use:

SELECT * FROM basics WHERE creation_date > DATE_ADD(creation_date, INTERVAL
-30 DAY);

or:

SELECT * FROM basics WHERE creation_date > DATE_SUB(creation_date, INTERVAL
30 DAY);

HTH;
JOn

A(Answer):

Ralph Freshour <ra***@primemail.com> wrote:

On Mon, 01 Sep 2003 10:24:33 +0100, Jon Kraft <jo*@jonux.co.uk> wrote:

Ralph Freshour <ra***@primemail.com> wrote:

I have a Date type in my MySQL table – I’m trying to do a query on all
rows within the last 30 days based on that Date field – I’m having
trouble figuring out how to form the query???

$php_SQL = "SELECT * FROM basics WHERE creation_date =
DATE_ADD(creation_date, INTERVAL -30 DAY)";

You’re searching rows which are exactly 30 days old – if you want all rows
within the last 30 days use:

SELECT * FROM basics WHERE creation_date > DATE_ADD(creation_date,
INTERVAL -30 DAY);

or:

SELECT * FROM basics WHERE creation_date > DATE_SUB(creation_date,
INTERVAL 30 DAY);

I tried both of your suggestions but neither worked – I doubled
checked my MySQL table col (field) data and all the dates are of Date
type and they are all in the month of August 2003 (varying days; 5th,
10th, etc.)

I don’t have to tell the query to compare the last 30 days from today?
That’s assumed in the query right?

Hi Ralph,

Sorry about that, you’re absolutely right, you have to tell the period of
days from now, not from the datefield!

SELECT * FROM basics WHERE creation_date > DATE_SUB(NOW(), INTERVAL 30 DAY);

HTH;
JOn

A(Answer):

On Sun, 31 Aug 2003 20:05:30 -0500, in message
<qk********************************@4ax.com>, the AI program named "Ralph
Freshour" <ra***@primemail.com> randomly printed:

I have a Date type in my MySQL table – I’m trying to do a query on all
rows within the last 30 days based on that Date field – I’m having
trouble figuring out how to form the query???

$php_SQL = "SELECT * FROM basics WHERE creation_date =
DATE_ADD(creation_date, INTERVAL -30 DAY)";

…doesn’t do anything…

Thanks for any help.

Untested:

select * from basics where creation_date >=
date_add(now(), interval -30 day);

LEAVE A COMMENT