Solved

shell script to access MySQL DB and send email

Posted on 2004-10-12
6
895 Views
Last Modified: 2008-02-26
Hi All,

First of all I am not sure if this question belongs to this category or if it doesnt, anyways, do point me out if wrong.

I have bought web hosting at a linux based server. I have created my personal events calendar in php (events.php). The events.php uses a MySQL database on the backend which stores all data for events.

Now what I want to be able to do is that whenever any of my events are coming up, I should be sent an email as a reminder about that event.

I figured that my hosting allows me to run unix commands at a particular time using CRON (The web hosting provides a web-based interface to CRON (CPanelX)).

Now .... the question!
1) How do i write a "unix command" that check out the upcoming events from the database and send an email with data from the MySQL database. Obviously its going to be a shell script. My initial research tells me that
I would need a code to access MySQL DB from shell account &&
I would need a code to send email from the shell account

Is that all I would be needing? If yes then how?
Preferably a code will be appreciated, links will work too.

I can raise the points, if requested.

Thanks
0
Comment
Question by:dosdemon
  • 3
  • 2
6 Comments
 
LVL 48

Accepted Solution

by:
hernst42 earned 300 total points
Comment Utility
You can write a php-script that will do that for you and then you have two options available:

if there is a command-line php (e.g /usr/bin/php) you can call that script by setting the following as your command
/usr/bin/php /path/to/script.php
or if there is no command-line php you can try this:
/usr/bin/(wget|lynx|w3m) http://www.example.com/script.php
wget lynx w3m are commands that call a given url and retrieve that output
0
 
LVL 12

Expert Comment

by:minichicken
Comment Utility
Hi dosdemon

You will need:
1.) Write a script in PHP or equivalent, to retrieve data from MySQL and echo the output in your desired format, all the echo will be send to the email account that you specified in your CpanelX cronjob. (I don't think cronjob can send HTML formated emails, because mine doen't).
2.) Login to your CpanelX and go to Cronjob
3.) you should see 2 options "Standard" and "Advanced (Unix Style), so select standard it easier.
4.) Enter the email address where you want cronjob to send the script output to.
5.) For "Command to run" : type "GET http://www.your_domain.com/your_script.php" (without the quotes)
6.) Set the time or schedule of when you want to run the script.
7.) Save Cronjob!!

That's it..... if that what you asking for... the keyword to execute the script in cronjob is "GET"

regards-
0
 

Author Comment

by:dosdemon
Comment Utility
Hi ppl, thanks for reply

Mini chicken:
I tried, but this is what I got emailed over and over again:
/bin/sh: line 1: /usr/bin/GET: Permission denied

:S I guess I cant use your solution? right? or is there some way out?
0
Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 

Author Comment

by:dosdemon
Comment Utility
Hernst42:
I tried all of your commands which failed with this error:
/bin/sh: line 1: /usr/bin/wget: No such file or directory
/bin/sh: line 1: /usr/bin/lynx: No such file or directory

I figured that the commands u told are not there, is there any other alternative?

Here is a "ls /usr/bin" :

4odb
4rdf
4xslt
4xupdate
GET
HEAD
Mail
POST
SOAPsh.pl
X11
XMLRPCsh.pl
[
a2p
ab
ac
aclocal
aclocal-1.4
aclocal-1.5
aclocal-1.6
activation-client
addftinfo
addr2line
addr2name.awk
addr2name.awk-ssa
annotate
ant
apm
apmsleep
apropos
ar
ar86
artscat
artsd
artsdsp
artsplay
artsrec
artsshell
artswrapper
as
as86
as86_encap
ascii-xfr
aspell
at
atq
atrm
attr
authconfig
authconfig-gtk
autoconf
autoconf-2.13
autoexpect
autoheader
autoheader-2.13
autom4te
automake
automake-1.4
automake-1.5
automake-1.6
autopasswd
autopoint
autoreconf
autoreconf-2.13
autoscan
autoscan-2.13
autoupdate
autoupdate-2.13
autovbox
awk
b2m
b58diag
b58stats
bashbug
batch
bc
bcc
bdfindex
bdftogd
bdftops
berkeley_db31_svc
berkeley_db32_svc
berkeley_db33_svc
berkeley_db40_svc
berkeley_db_svc
bindconf
bison
bonobo-activation-empty-server
bonobo-activation-run-query
bonobo-slay
bunzip2
byacc
bzcat
bzcmp
bzdiff
bzgrep
bzip2
bzip2recover
bzless
bzmore
c++
c++decl
c++filt
c2ph
c89
c99
cal
calibrate_ppa
cancel
cancel.cups
capifax
capifaxrcvd
capiinfo
captoinfo
catchsegv
cc
cdecl
cert2ldap
certfind
chacl
chage
chartex
chattr
chfn
chrt
chsh
chvt
ci
cjpeg
cksum
classic-ant
clear
cmp
co
col
colcrt
colrm
column
combinediff
comm
comp_err
compile_encoding
compile_et
compress
consolehelper
consolehelper-gtk
cpan
cpp
cproto
crash
crc32
crontab
csplit
ctags
cupsconfig
curl
curl-config
cut
cvs
cvsbug
cytune
dateconfig
db2_archive
db2_checkpoint
db2_deadlock
db2_dump
db2_load
db2_printlog
db2_recover
db2_stat
db31_archive
db31_checkpoint
db31_deadlock
db31_dump
db31_load
db31_printlog
db31_recover
db31_stat
db31_upgrade
db31_verify
db32_archive
db32_checkpoint
db32_deadlock
db32_dump
db32_load
db32_printlog
db32_recover
db32_stat
db32_upgrade
db32_verify
db33_archive
db33_checkpoint
db33_deadlock
db33_dump
db33_load
db33_printlog
db33_recover
db33_stat
db33_upgrade
db33_verify
db40_archive
db40_checkpoint
db40_deadlock
db40_dump
db40_load
db40_printlog
db40_recover
db40_stat
db40_upgrade
db40_verify
db_archive
db_checkpoint
db_deadlock
db_dump
db_dump185
db_load
db_printlog
db_recover
db_stat
db_upgrade
db_verify
dbiprof
dbiproxy
dbish
dc
dc_client
dc_server
dc_snoop
dc_test
ddate
ddd
deallocvt
decode-base64
decode-qp
dehtmldiff
desktop-file-install
desktop-file-validate
desktop-menu-tool
detect_ppa
dialog
diff
diff3
diffstat
dig
dir
dircolors
directomatic
dirname
disable
dislocate
disol
djpeg
dos2unix
doxygen
doxytag
dprofpp
du
dvipdf
ebrowse
ecpg
editdiff
ef
eject
elinks
elksemu
emacs
emacs-21.3
emacsclient
enable
enc2xs
encode-base64
encode-qp
env
eps2eps
eqn
eqn2graph
esd
esdcat
esdctl
esddsp
esdfilt
esdloop
esdmon
esdplay
esdrec
esdsample
espdiff
etags
eu-elflint
eu-nm
eu-readelf
eu-size
eu-strip
ex
expand
expect
expr
f5
f77
factor
faillog
fam
fax2ps
fax2tiff
fc-cache
fc-list
fdformat
fetchmail
fgconsole
file
filterdiff
find
find2perl
findsmb
finger
fixcvsdiff
fixmswrd.pl
flea
flex
flex++
flipdiff
floppy
fmt
fmtest
fold
font2c
foomatic-combo-xml
foomatic-compiledb
foomatic-configure
foomatic-datafile
foomatic-gswrapper
foomatic-perl-data
foomatic-printjob
formail
free
freetype-config
ftp-rfc
ftpcount
ftpwho
funzip
fwindex
g++
g++-ssa
g++296
g77
g77-ssa
gawk
gcc
gcc-ssa
gcc296
gcj
gcj-ssa
gcjh
gcjh-ssa
gconftool-2
gcore
gcov
gcov-ssa
gctags
gd2copypal
gd2topng
gdb
gdbserver
gdbtui
gdk-pixbuf-query-loaders
gdlib-config
gdparttopng
gdtopng
gen5820traps
gencat
gendiff
genkey
geqn
get5820stats
getconf
getent
getfacl
getfattr
gethostip
getkeycodes
getopt
gettext
gettextize
ghostscript
gif2tiff
gij
gij-ssa
gindxbib
gjar
glibcbug
glookbib
gmake
gnat
gnatbind
gnatbl
gnatchop
gnatfind
gnatgcc
gnatkr
gnatlink
gnatls
gnatmake
gnatprep
gnatpsta
gnatpsys
gnatxref
gneqn
gnroff
go-rhn.sh
gpasswd
gperf
gpg
gpgsplit
gpgv
gpic
gpm-root
gprof
grefer
grep-changelog
grepdiff
grepjar
grepjar-ssa
grmic
grmiregistry
grn
grodvi
groff
groffer
grolbp
grolj4
grops
grotty
groups
gs
gsbj
gsdj
gsdj500
gslj
gslp
gsnd
gsoelim
gtbl
gtk-demo
gtk-query-immodules-2.0
gtroff
gunzip
gzexe
gzip
h2ph
h2xs
halt
head
hesinfo
hexdump
hltest
host
hostid
hpftodit
htdbm
htdigest
htmlclean
htmlview
htpasswd
i386
i386-redhat-linux-c++
i386-redhat-linux-g++
i386-redhat-linux-gcc
i386-redhat-linux7-c++
i386-redhat-linux7-g++
i386-redhat-linux7-gcc
iconv
id
ident
idle
ifnames
ifnames-2.13
ijsgimpprint
imgsize
indent
indxbib
info
infocmp
infokey
infotocap
install
install-catalog
instmodsh
interdiff
internet-druid
ipcrm
ipcs
irdadump
irkbd
irpsion5
isamchk
isamlog
isc-config.sh
isdnconf
isdnrate
isdnrep
isosize
ispell
jar
jar-ssa
java
java_cup
java_cup-10.11
javac
jcf-dump
jcf-dump-ssa
join
jpegtran
jv-convert
jv-convert-ssa
jv-scan
jv-scan-ssa
jwhois
kban
kbdrate
keyrand
keytab-lilo
kibitz
kill
killall
ksh
ktest
last
lastb
lastcomm
lastlog
lchfn
lchsh
ld
ld86
ldapadd
ldapdelete
ldapmodify
ldapmodrdn
ldappasswd
ldapsearch
ldd
lddlibc4
less
lessecho
lesskey
lesspipe.sh
lex
lftp
lftpget
lha
libmcrypt-config
libnetcfg
libst-config
libtool
libtoolize
libusb-config
links
linux32
lkbib
loadunimap
locale
localedef
locate
lockfile
logger
logname
logresolve
look
lookbib
lp
lp.cups
lpoptions
lppasswd
lpq
lpq.cups
lpr
lpr.cups
lprm
lprm.cups
lprsetup.sh
lpstat
lpstat.cups
lpunlock
lsattr
lsb_release
lsdiff
lspgpot
lss16toppm
ltrace
lwp-download
lwp-mirror
lwp-request
lwp-rget
lx5
lz
m4
mac2unix
magicfilter-t
mail-files
mailshar
mailstat
make
make_encmap
makeinfo
man
man2html
manpath
mapscrn
mattrib
mbadblocks
mbchk
mcat
mcd
mcheck
mcomp
mcookie
mcopy
md5sum
mdel
mdeltree
mdir
mdu
memprof
memusage
memusagestat
merge
mesg
mev
mformat
minfo
minicom
mk_cmds
mkcramfs
mkfifo
mkmanifest
mkpasswd
mksmbpasswd.sh
mlabel
mmd
mmount
mmove
modulator
mpage
mpartition
mrd
mren
msgattrib
msgcat
msgcmp
msgcomm
msgconv
msgen
msgexec
msgfilter
msgfmt
msgfmt.py
msggrep
msghack
msginit
msgmerge
msgunfmt
msguniq
mshowfat
msql2mysql
mtools
mtoolstest
mtrace
mtype
mutt
mxtar
my_print_defaults
myisam_ftdump
myisamchk
myisamlog
myisampack
mysql
mysql_config
mysql_convert_table_format
mysql_explain_log
mysql_find_rows
mysql_fix_extensions
mysql_fix_privilege_tables
mysql_install_db
mysql_secure_installation
mysql_setpermission
mysql_tableinfo
mysql_waitpid
mysql_zap
mysqlaccess
mysqladmin
mysqlbinlog
mysqlbug
mysqlcheck
mysqld_multi
mysqld_safe
mysqldump
mysqldumpslow
mysqlhotcopy
mysqlimport
mysqlmanager
mysqlmanager-pwgen
mysqlmanagerc
mysqlshow
mysqltest
mzip
namei
nano
nc
neat
neat-control
neqn
net
net-snmp-config
newgrp
ngettext
nl
nm
nm86
nmap
nmblookup
nohup
nroff
nslookup
nsupdate
ntlm_auth
ntpstat
objcopy
objdump
objdump86
od
oldrdist
on_ac_power
op_help
op_import
op_merge
op_time
op_to_source
op_visualise
opcontrol
open
openssl
openvt
oprof_start
oprofiled
oprofpp
pack_isam
pal2rgb
pam-panel-icon
pango-querymodules
partition_uuid
passmass
passwd
paste
patch
pathchk
pax
pbm2ppa
pbmtpg
pcprofiledump
pcregrep
pcretest
pdbedit
pdf2dsc
pdf2ps
pdfopt
pdksh
pear
perl
perl5.8.0
perl5.8.4
perlbug
perlcc
perldoc
perlivp
perror
pf2afm
pfbtopfa
pfbtops
pftp
pg_config
pgpewrap
pgpring
pgrep
php
php-config
phpextdist
phpize
pic
pic2graph
pico
piconv
pinfo
pinky
pj-gs.sh
pkg-config
pkill
pl2pm
play
pman
pmap
pngtogd
pngtogd2
pnm2ppa
pod2html
pod2latex
pod2man
pod2text
pod2usage
podchecker
podselect
post-grohtml
poweroff
ppm2tiff
ppmtolss16
pr
pre-grohtml
printafm
printconf
printconf-gui
printconf-tui
printenv
printf
printtool
procmail
profiles
protoize
prove
ps2ascii
ps2epsi
ps2pdf
ps2pdf12
ps2pdf13
ps2pdf14
ps2pdfwr
ps2ps
psed
psfaddtable
psfgettable
psfstriptable
psfxtable
pspell-config
pstack
pstree
pstruct
ptar
ptx
pv.sh
pydb
pydb.py
pydbcmd.py
pydbsupt.py
pydoc
pygettext.py
pynche
python
python2
python2.2
qtconfig
qtconfig3
quota
ranlib
ras2tiff
raw
rcp
rcs
rcs-checkin
rcs2log
rcsclean
rcsdiff
rcsmerge
rdate
rdist
rdistd
rdjpgcom
readelf
readlink
reboot
rec
recompile
recompile-1.2
recountdiff
redhat-cdinstall-helper
redhat-config-authentication
redhat-config-bind
redhat-config-date
redhat-config-httpd
redhat-config-keyboard
redhat-config-language
redhat-config-mouse
redhat-config-network
redhat-config-network-cmd
redhat-config-network-druid
redhat-config-nfs
redhat-config-packages
redhat-config-printer
redhat-config-printer-gui
redhat-config-printer-tui
redhat-config-proc
redhat-config-rootpassword
redhat-config-samba
redhat-config-securitylevel
redhat-config-securitylevel-tui
redhat-config-services
redhat-config-soundcard
redhat-config-time
redhat-config-users
redhat-control-network
redhat-install-packages
redhat-logviewer
redhat-switch-mail
redhat-switch-mail-nox
rediff
refer
remsync
rename
renice
replace
reset
resetall
resizecons
resolve_stack_dump
resolveip
rev
rexec
rftp
rgb2ycbcr
rhn_register
rlog
rlogin
rlogin-cwd
rmdtovbox
rmic
rmic-ssa
rmiregistry
rmiregistry-ssa
rpcclient
rpcgen
rpm2cpio
rpmbuild
rpmdb
rpmgraph
rpmquery
rpmsign
rpmverify
rsh
rsync
run-parts
run-with-aspell
runscript
runtest
rvim
s2p
sa-learn
safe_mysqld
sasl-sample-client
sasl-sample-server
sasl2-sample-client
sasl2-sample-server
scp
screen
script
scsi_unique_id
sdiff
seq
serviceconf
setarch
setfacl
setfattr
setfdprm
setkeycodes
setleds
setmetamode
setsid
setterm
setup
sfconvert
sfinfo
sftp
sg
sgmlwhich
sha1sum
shar
showconsolefont
showkey
showtable
shred
size
size86
skill
slocate
slogin
smbcacls
smbclient
smbcontrol
smbcquotas
smbmnt
smbmount
smbpasswd
smbprint
smbspool
smbstatus
smbtar
smbtree
smbumount
snice
soelim
sox
soxmix
soxplay
spamassassin
spamassassin-cpanel
spamc
spamd
spell
splain
splint
split
splitdiff
sprof
ssh
ssh-add
ssh-agent
ssh-keygen
ssh-keyscan
star
stat
strace
strings
strip
stubmaker.pl
stunnel-4.04local
sudo
sum
swig
switchto
symlinks
syslinux
tac
tack
tail
talk
taskset
tbl
tcl
tclhelp
tclsh
tclsh8.3
tdbbackup
tdbdump
tee
telnet
test
test_ppa
testdhandle
testgtk
testparm
testprns
testtext
texi2dvi
texindex
tfmtodit
tftp
tgz
thumbnail
tic
tiff2bw
tiff2ps
tiff2rgba
tiffcmp
tiffcp
tiffdither
tiffdump
tiffinfo
tiffmedian
tiffset
tiffsplit
time
timed-read
timed-run
tixindex
tixwish8.1.8.3
tixwish8.1.8.3.1
tload
toe
top
tput
tr
troff
tset
tsort
ttindex
ttmkfdir
tty
tzselect
ucd5820stat
ud
ul
unbuffer
uncompress
unexpand
uniq
unix-lpr.sh
unix2dos
unprotoize
unshar
unwrapdiff
unzip
unzipsfx
up2date
up2date-config
up2date-nox
updatedb
uptime
url_handler.sh
urlview
userinfo
usermount
userpasswd
users
ustar
utmpdump
uudecode
uuencode
uuidgen
uz
vbox
vboxbeep
vboxcnvt
vboxctrl
vboxmail
vboxmode
vboxplay
vboxtoau
vdir
vfperf
vftest
vim
vimdiff
vimtutor
vmstat
volname
w
wall
watch
wbinfo
wc
weather
webpng
wftopfa
wg5
whatis
whereis
which
whiptail
who
whoami
whois
wish
wish8.3
wishx
word-list-compress
write
wrjpgcom
wvdial
wvdialconf
x86_64
xargs
xdelta
xgettext
xkibitz
xminicom
xml2-config
xmlcatalog
xmllint
xmlproc_parse
xmlproc_val
xmlwf
xql.pl
xslt-parser
xsltp
xsltp-2.4.1
xsltproc
xsubpp
xtrace
xxd
yacc
yapp
yes
ypcat
ypchfn
ypchsh
ypmatch
yppasswd
ypwhich
zcmp
zdiff
zegrep
zfgrep
zforce
zgrep
zip
zipcloak
zipgrep
zipinfo
zipnote
zipsplit
zless
zmore
znew
zsoelim

0
 

Author Comment

by:dosdemon
Comment Utility
Hi Hernst42!
Your first solution works!!
thanks alot.

Here is what I got emailed.

I was just wondering if there is some way that I can remove the first two lines from this output, so that the only thing i recieve is the last line and no other crap with it.

start -------------------------------------------------
Content-type: text/html
X-Powered-By: PHP/4.3.9

this is dynamic text

end---------------------------------------------------

Thanks, here are your points!
0
 
LVL 48

Expert Comment

by:hernst42
Comment Utility
You might try to run php with the option -q
 -q    Quiet-mode.  Suppress HTTP Header output.
0

Featured Post

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

Deprecated and Headed for the Dustbin By now, you have probably heard that some PHP features, while convenient, can also cause PHP security problems.  This article discusses one of those, called register_globals.  It is a thing you do not want.  …
Nothing in an HTTP request can be trusted, including HTTP headers and form data.  A form token is a tool that can be used to guard against request forgeries (CSRF).  This article shows an improved approach to form tokens, making it more difficult to…
Learn how to match and substitute tagged data using PHP regular expressions. Demonstrated on Windows 7, but also applies to other operating systems. Demonstrated technique applies to PHP (all versions) and Firefox, but very similar techniques will w…
The viewer will learn how to create and use a small PHP class to apply a watermark to an image. This video shows the viewer the setup for the PHP watermark as well as important coding language. Continue to Part 2 to learn the core code used in creat…

771 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now