I have a mySQL table containing items for sale by registered users of a site, which has the following fields: ID (an aujto-increment index field), ItemName, ItemPrice, ItemDescription, Image, and Published. I’m also going to be adding in a DateAdded field which will contain the date and time the item was added (by sending the PHP date() value with the form).
The Published field is set to either N or Y. Items with Published = N don’t show in the published items listing, items with Published = Y do. The value is toggled on and off in the backend by an admin.
So what I need to do is have items whose Published value is Y automatically change status to N, three months after they are first added to the database.
Presumably this can be done by comparing the current date and time with the date and time the item was created, but not sure how I’d go about it.
It needs to happen without any intervention, so would need to be via the mySQL query.
At the moment the mySQL query is:
SELECT * FROM itemsforsale WHERE Published LIKE 'Y' ORDER BY ItemName
So it needs to include the mySQL equivalent of “and where date added is less than three months ago”.
Would write a php script to be called by a cron job. I would run it every night with this logic. Before you implement this you will need to run a 1 time SQL statement to bring everything current.
(to bring everything current)
UPDATE published = N WHERE date <= current_date-3months
(then in a nightly cron)
UPDATE published = N WHERE publish date <= current_date-3months AND published = Y
On a side note, I would prefer to have published be an int 1 or 0 (1 = yes, 0 = no). Not sure if this will effect the speed/efficiency of your query, maybe others will chime in. Maybe it’s just my personal preference.
Unfortunately I don’t have root or admin access to the server as it’s hosted with a hosting company (not with me), I was also hoping this could be done just through mySQL rather than running a job through the underlying operating system each night?
Also I’m thinking if it was set up and only ran once a day it wouldn’t archive the items at the exact required time of the day (for example at 17:00 if an item was added 3 months ago at 17:00).
I’m sure it could work, but hope there’s an easier way that actually just uses the query code and db rather than playing with the OS?
The hosting panel is cPanel. But rather than mess with the underlying system (even if I had access) I’m really trying to get a PHP script or query that will compare the current date / time with the date / time when the item was created, and if greater than 3 months, change the status of the item accordingly…
You don’t have to ‘mess with the underlying system’ to run a cron job… not sure where you got that from. You should see a ‘cron’ icon or link down at the bottom of your cPanel admin panel. Click on it and you should be able to setup a cron job to run whenever you want it to.
Again, you WILL be using php to run the query to update the database…etc, the cron job is just calling the php page. That’s ALL the cron job is doing, basically like hiring a person to visit that page once a day, except it’s free! It’s CRON!!
Here’s an example of how simple a cron job should look
Also, how would I express “<= current_date-3months” inside the mySQL query? I’m sure mySQL won’t accept that syntax? Thanks
This is the PHP script for updating the table, as it stands:
<?php
include ('inc/dbconnect_customers.php');
$query = "UPDATE itemsforsale SET Published = N WHERE DateAdded <= current_date-3months AND published = Y";
?>
how does this application know to connect to the db and table? And how does it know what credentials to use? (the db has a user and password)
You get cron to call a php script which contains all of that, and sends the query to mysql.
The script outputs nothing, or can log the event or send you an email if it fails.
I’m also going to be adding in a DateAdded field which will contain the date and time the item was added (by sending the PHP date() value with the form).
If you set your table up correctly the first timestamp field in your database will automatically add the date, else if you want to do it manually you use the mysql function now().
The queries I wrote above were just for logic reference only. You will have to use php (or mysql) to get the current date, then subtract 3 months from it. Then you will compare this figure to the creation dates in your table. If any date is less than your calculated date AND it’s published is set to Y, then you want to set it to N.
I was under the impression that you would know how to assemble the SQL query but wasn’t sure of the logic you needed to use. Sorry if I mis-understood.
