a script that queries database periodically

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:

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

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.

Why not use perl with DBI? If you know php, perl is just as easy.

Bernie Huang 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

Bernie Huang wrote:

"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

Bernie Huang writes:

My boss wants me to write a script that will send a email report to him
monthly with the result of database queries.

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?

Conceptually, what you want is something like this:

#! /bin/sh

(
echo "Here's your report:"
psql -c 'SELECT ...'
) | mail -s Subject foo@bar

Reading man pages on each of these is advisable.

--
Peter Eisentraut peter_e@gmx.net http://yi.org/peter-e/

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 :)

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

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

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

I'm sure the convience of having the reports emailed on a specific day is
what you're looking for. But why not just code your PHP to do your
mysql_query() and mail() and place it on a webserver. Obviously have a
simple hardcoded login so just not anyone can request the reports. That way
your boss can get the goods whenever, maybe he might need them mid-month or
something comes up. He'll be able to go to the URL, enter his name and pw
and hit submit and he has them.

Not like this answered your question, just a suggestion to make it easier if
a web gateway is an option.

----- Original Message -----
From: Wojciech Milek <wojciech.milek@demo.pl>
To: Bernie Huang <bernie.huang@ec.gc.ca>; PHP_DB <php-db@lists.php.net>;
PHP_General <php-general@lists.php.net>; PGSQL-SQL
<pgsql-sql@postgresql.org>; PGSQL-GENERAL <pgsql-general@postgresql.org>
Sent: Thursday, November 30, 2000 6:16 PM
Subject: [PHP] RE: [SQL] a script that queries database periodically

Hi!

You can compile PHP without HTTP server support then you get php exec
'shell'

and:

#!/usr/local/bin/php
<?

echo "hello!";

?>

Jason's definitiely on the right track.

I'd add that if you don'tknow Perl, Python might be easier. Check
http://sourceforge.net/forum/forum.php?forum_id=39069, starship.net might also
have a PostreSQL module.

Python also has a very easy to use SMTP module for mailing of reports.

There's nothing anti-Perl here, I just don't know that language and found
Python quite natural.

Miles Thompson

RE
Jason 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.

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

I seem to remember someone saying that v7.1 was supposed to go Beta last
weekend. Are there binary RPMs available? If so, where?

Regards.

Gordan

Gordan Bobic wrote:

I seem to remember someone saying that v7.1 was supposed to go Beta last
weekend. Are there binary RPMs available? If so, where?

Not yet. I usually take a week or less to get the first set of beta
RPM's out -- I like to test things out thoroughly before any release,
even a beta one.

And, right now is a very hectic time for me anyway. But, rest assured,
you will see RPM's of 7.1 beta shortly after the tarball is released --
I expect to do so within a week of the tarball's release.

Watch this list for the announcement.
--
Lamar Owen
WGCR Internet Radio
1 Peter 4:11