updating rows while selecting 
Author Message
 updating rows while selecting

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;

"); }}}
?>

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command



Mon, 27 Oct 2003 01:30:06 GMT
 
 [ 1 post ] 

 Relevant Pages 

1. How to UPDATE Header row when selecting by SUM of Detail rows

2. Updating a Table by selecting row by row

3. question regarding select (multiple rows select into one result row)

4. Updating the R first rows of a SELECT containing an ORDER BY statement

5. Problem with selecting/updating rows

6. Updating SELECTED rows

7. Lock a SELECTed row for UPDATE?

8. Simulating serial fields: locking a row with select for update statement

9. Locking a selected row for update with timeout

10. Forms3.0/Post-Query/update rows selected but not displayed

11. Simulating serial fields: locking a row with select for update statement

12. Problem with selecting/updating rows


 
Powered by phpBB® Forum Software