homepage Welcome to WebmasterWorld Guest from 54.196.69.189
register, free tools, login, search, subscribe, help, library, announcements, recent posts, open posts,
Pubcon Website
Home / Forums Index / Code, Content, and Presentation / Databases
Forum Library, Charter, Moderators: physics

Databases Forum

    
Cron job for automatically reseting a mysql field every week
uplius




msg:4071594
 9:22 pm on Jan 31, 2010 (gmt 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!

 

rocknbil




msg:4071608
 10:38 pm on Jan 31, 2010 (gmt 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:

[/var/www/domain.com/public_html]crons/scriptname.cgi

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.

Perl:


#!/usr/bin/perl
# 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;
$sth->finish;

The same thing in PHP:

<?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");
mysql_select_db("$db_name",$link);
//
// Select statement, execute
//
$select = "update $table set $field='$value'";
mysql_query($select);
?>

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.

Global Options:
 top home search open messages active posts  
 

Home / Forums Index / Code, Content, and Presentation / Databases
rss feed

All trademarks and copyrights held by respective owners. Member comments are owned by the poster.
Terms of Service ¦ Privacy Policy ¦ Report Problem ¦ About
© Webmaster World 1996-2014 all rights reserved