Horton

Thread

  1. a script that queries database periodically

    Bernie Huang <bernie.huang@ec.gc.ca> — 2000-11-27T19:22:45Z

    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

  2. Re: [PHP] a script that queries database periodically

    Folke Ashberg <folke@fanstage.de> — 2000-11-27T19:38:40Z

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

  3. Re: [SQL] a script that queries database periodically

    Brett W. McCoy <bmccoy@chapelperilous.net> — 2000-11-27T19:44:25Z

    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.

  4. Re: [SQL] a script that queries database periodically

    Mathijs Brands <mathijs@ilse.nl> — 2000-11-27T19:44:44Z

    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

  5. Re: [PHP] a script that queries database periodically

    Jason <jason@jnj.org> — 2000-11-27T19:54:38Z

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

  6. Re: a script that queries database periodically

    Serge Canizares <serge@ephilosopher.com> — 2000-11-27T20:31:55Z

    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

  7. Re: [SQL] a script that queries database periodically

    Poul L. Christiansen <poulc@cs.auc.dk> — 2000-11-27T21:44:39Z

    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
>

  8. Re: [SQL] a script that queries database periodically

    Roberto Mello <rmello@cc.usu.edu> — 2000-11-27T21:56:59Z

    "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

  9. Re: [SQL] a script that queries database periodically

    Peter Eisentraut <peter_e@gmx.net> — 2000-11-27T22:24:55Z

    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/

  10. Re: [PHP-DB] Re: [SQL] a script that queries database periodically

    Jason <jason@jnj.org> — 2000-11-27T22:36:40Z

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

  11. Re: [SQL] a script that queries database periodically

    Mathijs Brands <mathijs@ilse.nl> — 2000-11-27T23:06:26Z

    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)

  12. Re: [PHP-DB] Re: [SQL] a script that queries database periodically

    Roberto Mello <rmello@cc.usu.edu> — 2000-11-28T00:10:55Z

    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

  13. Re: [PHP-DB] Re: [SQL] a script that queries database periodically

    clayton cottingham <clayton@marketingchallenge.com> — 2000-11-28T00:30:24Z

    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

  14. Re: [PHP] Re: [PHP-DB] Re: [SQL] a script that queries database periodically

    Joe Stump <joe@earth.care2.com> — 2000-11-28T00:45:39Z

    > 	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

  15. Re: [PHP] RE: [SQL] a script that queries database periodically

    JB <jbeebe@aimsmarketing.com> — 2000-11-30T11:41:50Z

    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!";
>
> ?>
>
> > -----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
> >
>
>
> --
> 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
>
>

  16. RE: [SQL] a script that queries database periodically

    Wojciech Milek <wojciech.milek@demo.pl> — 2000-11-30T18:16:02Z

    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
>

  17. Re: Re: [PHP] a script that queries database periodically

    Miles Thompson <milesthompson@sprint.ca> — 2000-12-01T12:22:47Z

    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:

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

  18. Re: Re: [PHP-DB] Re: [SQL] a script that queries database periodically

    Dan Wilson <phppgadmin@acucore.com> — 2000-12-04T07:56:56Z

    > 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

  19. v7.1 Beta?

    Gordan Bobic <gordan@freeuk.com> — 2000-12-04T09:15:51Z

    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

  20. Re: v7.1 Beta?

    Lamar Owen <lamar.owen@wgcr.org> — 2000-12-04T16:52:22Z

    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

  21. Re: v7.1 Beta?

    Marc G. Fournier <scrappy@hub.org> — 2000-12-04T17:24:19Z

    being tag'd and tar'd up as source sometime today ...

On Mon, 4 Dec 2000, 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?
> 
> Regards.
> 
> Gordan
> 
> 

Marc G. Fournier                   ICQ#7615664               IRC Nick: Scrappy
Systems Administrator @ hub.org 
primary: scrappy@hub.org           secondary: scrappy@{freebsd|postgresql}.org