Hi,
i am new to concepts of SQL and postgresql. For tutorial purpose i wrote a
little quotes database with columns:
name, quote, timestamp
a php html page should show every day another quote of the day. it works fine
and like this:
selecting a row with timestamp = today
if there is no row select one without timestamp (this way new quotes inserted
should be shown before older ones).
if there is still no rows the result then update the oldest quote
and ask again.
Is it possible to put this php programm structure into the database?? I tried
many different things but i dont know how to do it. I just always want to
select autmagicall a new quote of the day!
Is there something like an if, then, else statement in SQL? I knwo that SQL
is not a procedural language, but is there data view way to achieve something
like this?
Your help would be a great step for me in understanding SQL.
thanks in advance
Janning
p.s.: (zitate = quotes, zitat = quote, just the german translation...)
<?php
$conn = pg_pconnect ("dbname=cff user=janning");
$result = pg_Exec ($conn, "
SELECT name, zitat FROM zitate where timestamp = CURRENT_DATE;
");
if (! pg_numrows ($result)) {
pg_Exec ($conn, "
UPDATE zitate SET timestamp = CURRENT_DATE WHERE oid = (SELECT DISTINCT ON
(timestamp) oid from zitate where timestamp = NULL);
");
$result = pg_Exec ($conn, "
SELECT name, zitat FROM zitate where timestamp = CURRENT_DATE;
");
if (! pg_numrows($result)) { pg_Exec ($conn, "
UPDATE zitate SET timestamp = CURRENT_DATE WHERE timestamp = (
select distinct on (timestamp) timestamp from zitate
where timestamp = (select min(timestamp) from zitate));
");
$result = pg_Exec ($conn, "
SELECT name, zitat FROM zitate where timestamp = CURRENT_DATE;
"); }}}
?>
