Thread: a script that queries database periodically
Hello, My boss wants me to write a script that will send a email report to him monthly with the result of database queries. For example, an email would be like: Monthly report +----------+---------+---------------+ | Vehicles | Mileage | # of Signouts | +----------+---------+---------------+ | Vehicle1 | 10324 | 5 | +----------+---------+---------------+ | Vehicle2 | 19384 | 6 | +----------+---------+---------------+ ... I was thinking of writing up a PHP script and put into crontab, which is somehow easier than a shell script, but PHP is an apache module, so I cannot execute PHP under crontab (it has to be executed in a browser right?). I guess a shell script is necessary. So, is it possible to call 'psql' and returning its query result and I can use sendmail to email the result? Any other idea? Thanks. - Bernie
Attachment
On Mon, 27 Nov 2000, Bernie Huang wrote:
> I was thinking of writing up a PHP script and put into crontab, which is
> somehow easier than a shell script, but PHP is an apache module, so I
> cannot execute PHP under crontab (it has to be executed in a browser
> right?). I guess a shell script is necessary. So, is it possible to
> call 'psql' and returning its query result and I can use sendmail to
> email the result? Any other idea?
Is there any reason to not use Perl & DBI or the Pg.pm module?
Brett W. McCoy
http://www.chapelperilous.net/~bmccoy/
---------------------------------------------------------------------------
Exhilaration is that feeling you get just after a great idea hits you,
and just before you realize what is wrong with it.
On Mon, Nov 27, 2000 at 11:22:45AM -0800, Bernie Huang allegedly wrote:
> Hello,
>
> My boss wants me to write a script that will send a email report to him
> monthly with the result of database queries.
>
> For example, an email would be like:
>
> Monthly report
> +----------+---------+---------------+
> | Vehicles | Mileage | # of Signouts |
> +----------+---------+---------------+
> | Vehicle1 | 10324 | 5 |
> +----------+---------+---------------+
> | Vehicle2 | 19384 | 6 |
> +----------+---------+---------------+
> ...
>
> I was thinking of writing up a PHP script and put into crontab, which is
> somehow easier than a shell script, but PHP is an apache module, so I
> cannot execute PHP under crontab (it has to be executed in a browser
> right?). I guess a shell script is necessary. So, is it possible to
> call 'psql' and returning its query result and I can use sendmail to
> email the result? Any other idea?
>
> Thanks.
>
> - Bernie
Sure you can. You can configure PHP to run seperately from any webserver.
You would then be able to write PHP scripts that can be used just like any
other UNIX scripts, for instance from a crontab.
I don't know if you've configured and built PHP before, but it is actually
not that difficult. However, be sure to look at the INSTALL file that comes
with PHP. What it comes down to is probably the following:
1 - extract PHP source
2 - change to the PHP source directory and execute the following configure
command:
./configure --prefix=/usr/local/standalone-php --without-mysql --with-pgsql
3 - build the source by giving the make command
4 - do a 'make install' after PHP has been succesfully build
A bit off-topic, but I hope this is helpful.
Cheers,
Mathijs
Ps. I just thought of something else. You could write a PHP script that runs
under apache (like your're used to) and request it with wget, lynx or a
similar tool from a crontab. That way you wouldn't need to configure a new
PHP installation.
--
"A book is a fragile creature. It suffers the wear of time,
it fears rodents, the elements, clumsy hands."
Umberto Eco
Why not use perl with DBI? If you know php, perl is just as easy. Bernie Huang wrote: > I was thinking of writing up a PHP script and put into crontab, which is > somehow easier than a shell script, but PHP is an apache module, so I > cannot execute PHP under crontab (it has to be executed in a browser > right?). > > Thanks. > > - Bernie
I have a bash script in crontab with the following line: "lynx -dump http://127.0.0.1/postgres/myPhpFile.php>${currentDir}php_output.html" You can also just put that line directly into crontab. Poul L. Christiansen Bernie Huang wrote: > Hello, > > My boss wants me to write a script that will send a email report to him > monthly with the result of database queries. > > For example, an email would be like: > > Monthly report > +----------+---------+---------------+ > | Vehicles | Mileage | # of Signouts | > +----------+---------+---------------+ > | Vehicle1 | 10324 | 5 | > +----------+---------+---------------+ > | Vehicle2 | 19384 | 6 | > +----------+---------+---------------+ > ... > > I was thinking of writing up a PHP script and put into crontab, which is > somehow easier than a shell script, but PHP is an apache module, so I > cannot execute PHP under crontab (it has to be executed in a browser > right?). I guess a shell script is necessary. So, is it possible to > call 'psql' and returning its query result and I can use sendmail to > email the result? Any other idea? > > Thanks. > > - Bernie >
"Poul L. Christiansen" wrote:
> > I was thinking of writing up a PHP script and put into crontab, which is
> > somehow easier than a shell script, but PHP is an apache module, so I
> > cannot execute PHP under crontab (it has to be executed in a browser
> > right?). I guess a shell script is necessary. So, is it possible to
> > call 'psql' and returning its query result and I can use sendmail to
> > email the result? Any other idea?
PHP does not have a scheduling facility? AOLserver (the web/application
server that powers AOL) has had such facility (and many many others for
db-backed websites) since 1995. ns_schedule_proc.
http://www.aolserver.com
-Roberto
--
Computer Science Utah State University
Space Dynamics Laboratory Web Developer
USU Free Software & GNU/Linux Club http://fslc.usu.edu
My home page - http://www.brasileiro.net/roberto
On Mon, Nov 27, 2000 at 10:44:39PM +0100, Poul L. Christiansen allegedly wrote: > I have a bash script in crontab with the following line: > "lynx -dump > http://127.0.0.1/postgres/myPhpFile.php>${currentDir}php_output.html" > > You can also just put that line directly into crontab. > > Poul L. Christiansen Be sure to redirect the output of the command in your crontab to /dev/null or something similar. If you don't, the output (HTML) will be emailed to you each time the script is run. Whether this side effect is desirable is of course up to you (or your boss, for instance). Getting even more off-topic, Mathijs -- "Where is human nature so weak as in a bookstore!" Henry Ward Beecher (1813-1887)
Jason wrote:
>
> aolserver is a web/application server. PHP is a server-side scripting
> language. Why exactly *should* it have a job scheduler?
>
> Some (such as myself) might also ask why should a web server have a job
> scheduler, but that's a thread for a different list :)
Because PHP is supposed to solve web development problems. And this is
one of them. It's very useful.
-Roberto
--
Computer Science Utah State University
Space Dynamics Laboratory Web Developer
USU Free Software & GNU/Linux Club http://fslc.usu.edu
My home page - http://www.brasileiro.net/roberto
> Because PHP is supposed to solve web development problems. And this is > one of them. It's very useful. Why solve one that is already solved? PHP isn't here to reinvent the wheel - get crontab and quit crying. --Joe > > -Roberto > -- > Computer Science Utah State University > Space Dynamics Laboratory Web Developer > USU Free Software & GNU/Linux Club http://fslc.usu.edu > My home page - http://www.brasileiro.net/roberto > > -- > PHP General Mailing List (http://www.php.net/) > To unsubscribe, e-mail: php-general-unsubscribe@lists.php.net > For additional commands, e-mail: php-general-help@lists.php.net > To contact the list administrators, e-mail: php-list-admin@lists.php.net ================================================================================ = Joe Stump joestump98@yahoo.com http://www.miester.org = ================================================================================ Need a programmer? http://www.google.com/search?q=joe+stump+resume
Hi! You can compile PHP without HTTP server support then you get php exec 'shell' and: #!/usr/local/bin/php <? echo "hello!"; ?> > -----Original Message----- > From: pgsql-sql-owner@postgresql.org > [mailto:pgsql-sql-owner@postgresql.org]On Behalf Of Bernie Huang > Sent: Monday, November 27, 2000 8:23 PM > To: PHP_DB; PHP_General; PGSQL-SQL; PGSQL-GENERAL > Subject: [SQL] a script that queries database periodically > > > Hello, > > My boss wants me to write a script that will send a email report to him > monthly with the result of database queries. > > For example, an email would be like: > > Monthly report > +----------+---------+---------------+ > | Vehicles | Mileage | # of Signouts | > +----------+---------+---------------+ > | Vehicle1 | 10324 | 5 | > +----------+---------+---------------+ > | Vehicle2 | 19384 | 6 | > +----------+---------+---------------+ > ... > > I was thinking of writing up a PHP script and put into crontab, which is > somehow easier than a shell script, but PHP is an apache module, so I > cannot execute PHP under crontab (it has to be executed in a browser > right?). I guess a shell script is necessary. So, is it possible to > call 'psql' and returning its query result and I can use sendmail to > email the result? Any other idea? > > Thanks. > > - Bernie >
> I was thinking of writing up a PHP script and put into crontab, which is > somehow easier than a shell script, but PHP is an apache module, so I > cannot execute PHP under crontab (it has to be executed in a browser > right?). I guess a shell script is necessary. So, is it possible to > call 'psql' and returning its query result and I can use sendmail to > email the result? Any other idea? if you know perl, you should check out DBI. Read all about it at www.cpan.org. It's pretty similar to the php database interface.
Hi! I think you have installed GET , so use in your cronab GET http://localhost/report_for_my_boss/create.php |mail -s "Hello Boss" boss@chief.net if you miss get try it with lynx: lynx -source -dump http://localhost/.. |mail .... regards Folke -- Folke Ashberg Stage Internet Services GmbH Technical Development folke@fanstage.de folke@fanstage.de | Tel: +49 89 454591-77 | Fax: +49 89 454591-71 Kreillerstr. 21 | 81673 Munich | Germany | www.fanstage.de On Mon, 27 Nov 2000, Bernie Huang wrote: > Hello, > > My boss wants me to write a script that will send a email report to him > monthly with the result of database queries. > > For example, an email would be like: > > Monthly report > +----------+---------+---------------+ > | Vehicles | Mileage | # of Signouts | > +----------+---------+---------------+ > | Vehicle1 | 10324 | 5 | > +----------+---------+---------------+ > | Vehicle2 | 19384 | 6 | > +----------+---------+---------------+ > ... > > I was thinking of writing up a PHP script and put into crontab, which is > somehow easier than a shell script, but PHP is an apache module, so I > cannot execute PHP under crontab (it has to be executed in a browser > right?). I guess a shell script is necessary. So, is it possible to > call 'psql' and returning its query result and I can use sendmail to > email the result? Any other idea? > > Thanks. > > - Bernie > >
Roberto Mello wrote: > > Jason wrote: > > > > aolserver is a web/application server. PHP is a server-side scripting > > language. Why exactly *should* it have a job scheduler? > > > > Some (such as myself) might also ask why should a web server have a job > > scheduler, but that's a thread for a different list :) > > Because PHP is supposed to solve web development problems. And this is > one of them. It's very useful. > > -Roberto > -- > Computer Science Utah State University > Space Dynamics Laboratory Web Developer > USU Free Software & GNU/Linux Club http://fslc.usu.edu > My home page - http://www.brasileiro.net/roberto heya, IMHO it depends on why you are querying the db if you need internet access data such as how fast its querying or what have you then yes php could be useful if you need reports generated and mailed to someone a crond shell script would seem more logical
On Mon, Nov 27, 2000 at 02:56:59PM -0700, Roberto Mello wrote: > > PHP does not have a scheduling facility? AOLserver (the web/application > server that powers AOL) has had such facility (and many many others for > db-backed websites) since 1995. ns_schedule_proc. > http://www.aolserver.com aolserver is a web/application server. PHP is a server-side scripting language. Why exactly *should* it have a job scheduler? Some (such as myself) might also ask why should a web server have a job scheduler, but that's a thread for a different list :)
> Jason wrote: > > > > aolserver is a web/application server. PHP is a server-side scripting > > language. Why exactly *should* it have a job scheduler? > > > > Some (such as myself) might also ask why should a web server have a job > > scheduler, but that's a thread for a different list :) > > Because PHP is supposed to solve web development problems. And this is > one of them. It's very useful. I disagree! Cronjobs are not a web development problem. That is something that should be handled on the server/machine side. PHP is purely designed to dynamically create web pages. It has other things built into it, but each one interfaces with the web page somehow. A cronjob doesn't use the web at all. The web is an interactive thing and the whole purpose of a job scheduler is to avoid interaction. Sure a job scheduler is useful, but it is outside the scope and mission of PHP. -Dan