How to retrieve certain tables from database using PHP?

Question

I have a table with these columns:

+------------------------------------------------------+
| ad_id | ad_title | active |   placed_on   | duration | 
+------------------------------------------------------+
|     1 | First    |      1 |  2010-19-10   |      365 |
+------------------------------------------------------+
|     2 | Second   |      1 |  2011-18-10   |      365 |
+------------------------------------------------------+

It's a table for listings that expire in 365 days from day it's placed_on and I wanted to create a PHP + SQL statement that would show listings that expire this month (e.g. first ad which was placed last year and expires today). How can I do math on those dates, like this 2010-19-10 + 365? and show the ones that expire this month. My brain froze after getting to:

mysql_query("SELECT `ad_id`, `ad_title`, `active` FROM `listings` WHERE `placed_on` = ``;

Is it even possible, if yes, please advise me. Thank you!

mu is too short · Accepted Answer · 2011-10-20 01:20:53Z

1

You can use adddate for your date arithmetic:

select ad_id, ad_title, active
from listings
where adddate(placed_on, concat(duration, ' days')) between '2011-10-01' and '2011-10-31'

Presumably you can supply the first and last days of the month from your PHP.

answered Oct 20, 2011 at 1:20

mu is too short

436k71 gold badges863 silver badges822 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Daren Schwenke · Accepted Answer · 2011-10-20 01:21:33Z

0

Well if this is not your table and you can't make placed_on into something a little nicer to work with, you can generate your offset date on the front end and pass that to:

STRCMP()

http://dev.mysql.com/doc/refman/5.0/en/string-comparison-functions.html#function_strcmp

WHERE STRCMP(placed_on,'2010-19-10') = 1 ORDER BY placed_on

will give you everything newer than 2010-19-10.

answered Oct 20, 2011 at 1:21

Daren Schwenke

5,4883 gold badges32 silver badges34 bronze badges

Collectives™ on Stack Overflow

How to retrieve certain tables from database using PHP?

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related