Welcome to WebmasterWorld Guest from

Forum Moderators: open

Message Too Old, No Replies

Cron job for automatically reseting a mysql field every week

9:22 pm on Jan 31, 2010 (gmt 0)

New User

5+ Year Member

joined:Jan 24, 2010
posts: 5
votes: 0

Hi there,
I want to reset a field with all entries to 0 every week at midnight, how might I do this? I asked on yahoo answers initially, and they answered that I needed a cron job script. On my host's cpanel, there is indeed a section for cron jobs, however it says that it requires a knowledge of *nix commands. There are selectors for how often the job is done, and a textarea for the command. Can someone lead me into a good direction as to how to do this? Thanks!
10:38 pm on Jan 31, 2010 (gmt 0)

Senior Member

WebmasterWorld Senior Member rocknbil is a WebmasterWorld Top Contributor of All Time 10+ Year Member

joined:Nov 28, 2004
votes: 0

Arg, glad you made your way over here from the kiddie pool, they always have an answer but it's seldom close to correct. :-)

A cron job executes a program. The settings in your CP set the time to execute the program. When indicating the program to execute, you have to use the full server path to the program - but most CP cron setups I've seen do part of this for you. For example, it might have a drop-down list for "domain root" or "other path." So if you load your program into some folder on your domain, you would select domain root and add whatever folder it's in, like:


Where this [] is the selection for "domain root."

What program you might ask . . . well you have to write it. You can use any program supported by your server, PHP, Perl, whatever. If you have any experience in either, someone can coach you through the setup. Here's two examples, you might even be able to fill in the blanks and use these.

The 'Nix wizards would probably use something even more simple in a command line utility like bash . . . don't know it well enough myself.

NOTE: Neither of these are working code. They could be, I don't see any immediate errors, but I just typed them on the fly.


# Except for the previous line, comments are marked by # and ignored.
# set up the database login variables
$db_name = 'your_db'; # the name of your database
$db_user = 'your_user'; # the valid database user name
$user_pass = 'Y0uRP@$$'; # the database pass for this user
$db_host = ''; # leave blank for localhost, or enter mysql server location if remote
$table = 'your_table'; # name of the table you want to update
$field = 'pageviews'; # name of the FIELD in that table you want to update
$value = '0'; # value you want to set it to
use DBI; # Perl database module, becoming standard
# Create the database connection string
$conn_string = "DBI:mysql:$db_name";
if ($db_host) { $conn_string .= ":$db_host"; }
# make the db connection
$dbh = DBI->connect("$conn_string",$db_user,$user_pass);
# This is your select statement.
$select = qq|update $table set $field='$value';|;
# Execute it, done.
$sth = $dbh->prepare("$select");
$rv = $sth->execute;

The same thing in PHP:

# PHP supports both // and # for comments. Another thing inherited from Perl.
# set up the database login variables
$db_name = 'your_db'; // All same as Perl example
$db_user = 'your_user';
$user_pass = 'Y0uRP@$$';
$db_host = 'localhost'; // not sure, in PHP you may have to specify localhost
$table = 'your_table';
$field = 'pageviews';
$value = '0';
// Make the database connection
$link = mysql_pconnect("$db_host","$db_user","$user_pass");
// Select statement, execute
$select = "update $table set $field='$value'";

For those wondering, there is no error trap in either of these,

$rv = $sth->execute or die("Could not execute query");

Being a cron, no one would see it. To take that to the next level you would log errors, and log successful runs, or even have it email you. But these should get the job done.


Join The Conversation

Moderators and Top Contributors

Hot Threads This Week

Featured Threads

Free SEO Tools

Hire Expert Members