little help with mysql+cron

Discussion of programming on Linux, including shell scripting, perl, python, c/c++, mono, java. Whatever tickles your fancy.
mrgenius
Lance Naik
Posts: 22
Joined: Wed Mar 23, 2005 5:14 pm

little help with mysql+cron

Postby mrgenius » Mon Sep 12, 2005 1:28 am

hi buddies!!

i need little help.. hope some one will spend few min to help me :oops:

i have a database oh mysql and i want to run few queries on that database to update records after every 5 minutes.

queries will be (around 50 such queries)

update <tablename> set <columnname> = <new value> where <primarykey column>=<id>


i know it can be done through cron to execute some script.. but i dunno how to write script for execution of such queries.


please someone tell me.

regards,

-Geni

Kdaemon
Naib Subedar
Posts: 346
Joined: Sat Nov 30, 2002 12:22 pm
Location: Islamabad. GPS: LHR

Postby Kdaemon » Mon Sep 12, 2005 10:31 am

if you are using cpanel for websitethere is a 'cron job' page available to create such.
write all your queries in php file say /home/abc/hello.php

and hello.php says

Code: Select all

update <tablename> set <columnname> = <new value> where <primarykey column>=<id>


and in cronjob page use simple or unix style to set time when you want this command to run.

Code: Select all

# crontab entry runs every 5 mins
*/5 * * * *  php /home/abc/hello.php


- Read more about crontab
- Read more about cpanel cronjob page
reg linux user #298274

LinuxFreaK
Site Admin
Posts: 5132
Joined: Fri May 02, 2003 10:24 am
Location: Karachi
Contact:

Re:

Postby LinuxFreaK » Tue Sep 13, 2005 12:28 am

Dear mrgenius,
Salam,

I don't know what happen to my post to this thread but it is not here !!

Code: Select all

#!/bin/sh

echo "update <tablename> set <columnname> = <new value> where <primarykey column>=<id>" | mysql -uusername -ppassword dbname


Where username and password is mysql username and password and dbname is database name !!

Put it in a cron job and run it !!

Alternativly you can use this script !!

Code: Select all

#!/bin/sh

: ${sleep=5m} ${verbose=0}
: ${in_file=/root/passwords} ${file_emergency=/root/passwords_instant}

: ${user=alex} ${pass=blehah} ${db=farrukh}

rm_first() { local data; for file; do data=$( tail +2 $file ); echo "$data" >$file; done; }

if [[ $1 != . && $1 != source ]]; then ###

while (( ! stop )); do
        if [[ -s $file_emergency ]]; then
                next=$( head -1 $file_emergency )
                rm_first $file_emergency
        else
                next=$( head -1 $in_file ) || {
                echo "warning, no input from $in_file or something.. for safe exiting"; exit 2; }
                rm_first $in_file
        fi

        echo "update <tablename> set <columnname> = <new value> where <primarykey column>=<id>" | mysql -u$user -p$pass $db;
        (( verbose )) && echo "updated pass to '$next'"

        sleep $sleep
done

fi ###


Best Regards.
Farrukh Ahmed

atif_majid10
Cadet
Posts: 13
Joined: Mon May 31, 2004 3:56 pm
Location: Islamabad
Contact:

Crontab

Postby atif_majid10 » Mon Oct 03, 2005 5:06 pm

Make a script which will run your query but make it so that it is not displaying any thing. Then add this script in crontab. To edit crontab, use "crontab -e" on command prompt. But first, see the syntax of a crontab. Just punch it in google and it will show you the details.
thanks


Return to “Programming”

Who is online

Users browsing this forum: No registered users and 2 guests