MySQL Update via Cron job

Discussion in 'MySQL' started by shadragon, Jan 6, 2009.

0
  1. #1
    Howdy,

    I have a MySQL database and I need to set up a cron job to update a single field once a day. I can do the cron job, but need to know how to structure the script.

    Here are the details:

    Database name: _spforum
    Table: ibf_members
    Field: hide_email

    The value of hide_email needs to be updated to 0 if it is a 1.

    I have the following MySQL command that does the trick:

    UPDATE `shamus_spforum`.`ibf_members` SET `hide_email` = '0' WHERE `ibf_members`.`hide_email` = '1' LIMIT 10

    ...and running that through a cron job every 24 hours would be perfect. Problem is I don't know how to get a MySQL command to run via a command line (from a cron job) on the server.

    So what do I need to do to make that work? I would rather not write a PHP script if at all possible. My skills in that area are very weak, but will have too if that is the only choice. Also, where do I put the script on the server?

    Thanks in advance.
     
    shadragon, Jan 6, 2009 IP
  2. mwasif

    mwasif Active Member

    Messages:
    816
    Likes Received:
    23
    Best Answers:
    1
    Trophy Points:
    70
    #2
    You need to create a shell/php script which will run via cronjob.
     
    mwasif, Jan 7, 2009 IP
  3. crivion

    crivion Notable Member

    Messages:
    1,669
    Likes Received:
    45
    Best Answers:
    0
    Trophy Points:
    210
    Digital Goods:
    3
    #3
    build a php page with the query and set crontab to access it?
    <?php
    $sql = "UPDATE `shamus_spforum`.`ibf_members` SET `hide_email` = '0' WHERE `ibf_members`.`hide_email` = '1' LIMIT 10";
    $rs = mysql_query($sql);
    ?>
     
    crivion, Jan 7, 2009 IP
  4. shadragon

    shadragon Peon

    Messages:
    12
    Likes Received:
    0
    Best Answers:
    0
    Trophy Points:
    0
    #4
    OK. Lets assume I put the above into a file called fix.php.

    What is the format of the command line entry on the cron job? The only other cron job on the system has:

    cd '/home/shamus/public_html/maillist/admin/' ; php -q index.php -p processqueue > /dev/null ;

    Second, does it need to go anywhere specific on the system?

    Thanks all!



    (Added later)

    How does this look:

    cd '/home/shamus/public_html/maillist/admin/' ; php -q fix.php > /dev/null ;
     
    shadragon, Jan 7, 2009 IP
  5. robuk21

    robuk21 Peon

    Messages:
    24
    Likes Received:
    0
    Best Answers:
    0
    Trophy Points:
    0
    #5
    I did this a few days ago on my server, to get a php file to execute as a cron job on linux do the following, this is assuming your php file works by the way.

    In the shell interface browse to cd /etc/cron.daily

    Next create a new file by doing pico filename - this will open the lightweight text editor pico and a blank file.

    type into here /usr/bin/wget -q http://<URLTOFILE>/fix.php && rm -rf fix.php

    Press ctrl + x to exit and type Y to save

    You must now make this file executable so enter this chmod 755 filename

    Now if everything is set up correctly your update script should run by default every day at 0:01, you can change this by editing the crontab file.

    Hope this helps.
     
    robuk21, Feb 19, 2009 IP