little help with mysql+cron

Discussion of programming on Linux, including shell scripting, perl, python, c/c++, mono, java. Whatever tickles your fancy.

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
mrgenius
Lance Naik
 
Posts: 22
Joined: Wed Mar 23, 2005 5:14 pm

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
Kdaemon
Naib Subedar
 
Posts: 346
Joined: Sat Nov 30, 2002 12:22 pm
Location: Islamabad. GPS: LHR

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
LinuxFreaK
Site Admin
 
Posts: 5132
Joined: Fri May 02, 2003 10:24 am
ICQ: 82075802
Website: http://www.linuxpakistan.net/wiki/index.php?pagename=LinuxFreak
WLM: f4fahmed@hotmail.com
Yahoo Messenger: f4fahmed@yahoo.com
AOL: linuxpakistan@aol.com
Location: Karachi

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
atif_majid10
Cadet
 
Posts: 13
Joined: Mon May 31, 2004 3:56 pm
WLM: atif_majid@hotmail.com
Location: Islamabad


Return to “%s” Programming

Who is online

Users browsing this forum: No registered users and 1 guest

cron