SitePoint Sponsor

User Tag List

Results 1 to 15 of 15
  1. #1
    SitePoint Zealot
    Join Date
    Oct 2009
    Posts
    140
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Automatic archiving of records in mySQL using PHP

    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:

    PHP Code:
    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".

    Any ideas?

  2. #2
    SitePoint Evangelist
    Join Date
    Sep 2006
    Posts
    428
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.

    *queries above are for logic reference only.

  3. #3
    SitePoint Zealot
    Join Date
    Oct 2009
    Posts
    140
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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?

    Thanks

  4. #4
    SitePoint Evangelist
    Join Date
    Sep 2006
    Posts
    428
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    mmmm.... are you using a hosting control panel? cPanel? Webmin? Plesk?

  5. #5
    SitePoint Zealot
    Join Date
    Oct 2009
    Posts
    140
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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...

  6. #6
    SitePoint Evangelist
    Join Date
    Sep 2006
    Posts
    428
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by trufflepig View Post
    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

    Code:
    0 0 * * * * wget http://www.yoursite.com/path/to/php/script/Update-Published-Status.php
    The 0 0 * * * *...etc. tells cron when and how often to run the script. The above format should run it at midnight every night.

    wget, just tells cron to fetch the following page.

    Just change the link to the link to your php page that executes the query to update your database.

  7. #7
    SitePoint Zealot
    Join Date
    Oct 2009
    Posts
    140
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Ok, well there is a Cron application in the panel, but looking at the code:

    UPDATE published = N WHERE publish date <= 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)

    The db name is customers and the table is called itemsforsale, so should the UPDATE actually be something like:

    UPDATE customers.itemsforsale.published = N WHERE publish date <= current_date-3months AND published = Y

    Does the cron application understand "3months" or would it need to be written differently?

    Sorry for the continued questions but the control panel gives virtually no help about what form the script should take...

  8. #8
    SitePoint Zealot
    Join Date
    Oct 2009
    Posts
    140
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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 Code:
    <?php

    include ('inc/dbconnect_customers.php');

    $query "UPDATE itemsforsale SET Published = N WHERE DateAdded <= current_date-3months AND published = Y";

    ?>

  9. #9
    SitePoint Wizard silver trophybronze trophy Cups's Avatar
    Join Date
    Oct 2006
    Location
    France, deep rural.
    Posts
    6,869
    Mentioned
    17 Post(s)
    Tagged
    1 Thread(s)
    - 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().

    http://dev.mysql.com/doc/refman/5.0/en/timestamp.html

  10. #10
    SitePoint Evangelist
    Join Date
    Sep 2006
    Posts
    428
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.

  11. #11
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,341
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    you could also do the date calculation in mysql

    ... WHERE DateAdded < CURRENT_TIMESTAMP - INTERVAL '3' MONTH
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  12. #12
    SitePoint Zealot
    Join Date
    Oct 2009
    Posts
    140
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    That sounds like a plan, will try that. Thanks for the help all. I'll see how I get on.

  13. #13
    SitePoint Zealot
    Join Date
    Oct 2009
    Posts
    140
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Sorted - although I changed the less than to greater than as I wanted the items more recent than 3 months to be showing.

    Works a treat though- many thanks.

  14. #14
    SitePoint Wizard silver trophybronze trophy Cups's Avatar
    Join Date
    Oct 2006
    Location
    France, deep rural.
    Posts
    6,869
    Mentioned
    17 Post(s)
    Tagged
    1 Thread(s)
    ... WHERE DateAdded > CURRENT_TIMESTAMP - INTERVAL '3' MONTH

    ** wild guess

  15. #15
    SitePoint Zealot
    Join Date
    Oct 2009
    Posts
    140
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Yes, exactly like that.


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •