CODEWITHSUNDEEP
phpsqlweb
Adan Sandoval
Adan Sandoval

Reputation: 466

Automatically update data in sql for a webpage

So I'm a software Development student and for my web class I created a project that uses among other things Php and SQL; In this project, users can create posts and other users can comment on them.

The thing is I want posts to only be available for a certain period of time.

Then I have an SQL table named 'Posts' and they have a column named 'Status' (you know, if the status it's 0 they're not available and else they are.)

When a user creates a post I make SQL: INSERT INTO posts *All the post data*, I set the Status to 1 and make a TIMESTAMP to register the date of creation of the post. I want that a week after the date registered in the Timestamp changes the status column to 0 but I don't want it to be with a page request (I need it to be automatic) and I want the user to be notified via email or something.

Can it be made with some python CGI that checks the date, updates the Status and sends the email or is there a better/easier way to do it?

Thanks a lot for your help :)

Upvotes: 0

Views: 550

Answers (2)

Adan Sandoval
Adan Sandoval

Reputation: 466

Turns out the best way to solve this was using Cron Jobs.

I run a PHP script every day and I modify the posts which are exactly 7 days old, using 

UPDATE Posts SET Status = 0 WHERE DATE(timestamp_col) = DATE_SUB(NOW(), INTERVAL 7 DAY)

And then I iterate through the affected rows emailing the users.

Upvotes: 0

RiggsFolly
RiggsFolly

Reputation: 94652

You dont need the status 0/1 AND the timestamp column, if all you want to do is show a post for a set period of time.

Just use the timestamp column and amend the queries that fetch the posts to only show those posts that are < 7 days old (or any period you decide)

EG

SELECT * from posts where timestamp_col < DATE_SUB(NOW(), INTERVAL 7 DAY)

or something similiar that meets your needs

Upvotes: 1

Related Questions