Received: from mailrelay2.inwind.it (mailrelay2.inwind.it [212.141.54.102]) by postgresql.org (8.11.3/8.11.4) with ESMTP id f7TKtWf09142 for ; Wed, 29 Aug 2001 16:55:32 -0400 (EDT) (envelope-from _casadei@libero.it) Received: from [62.98.214.216] (62.98.214.216) by mailrelay2.inwind.it (5.5.029) id 3B83ABF0001C990E for pgsql-php@postgresql.org; Wed, 29 Aug 2001 22:55:20 +0200 Received: (qmail 1279 invoked by uid 1000); 29 Aug 2001 20:51:20 -0000 Date: Wed, 29 Aug 2001 22:51:19 +0200 From: Francesco Casadei To: pgsql-php@postgresql.org Subject: Storing PHP sessions into a PostgreSQL database Message-ID: <20010829225119.A1249@goku.kasby> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline User-Agent: Mutt/1.2.5i X-Operating-System: FreeBSD 4.4-RC i386 X-Archive-Number: 200108/26 X-Sequence-Number: 521 Hi all, I'm trying to store PHP sessions into a database, but I'm having problems with sess_write() function. I created the table 'sessions' this way: CREATE TABLE sessions ( sesskey CHAR(32) PRIMARY KEY, expiry NUMERIC(11, 0) NOT NULL, value TEXT ); The function sess_write() looks like: function sess_write($key, $val) { global $SESS_DBH, $SESS_LIFE; $expiry = time() + $SESS_LIFE; $value = addslashes($val); $qry = "INSERT INTO sessions VALUES ('$key', $expiry, '$value')"; $qid = pg_exec($SESS_DBH, $qry); if (! $qid) { $qry = "UPDATE sessions SET expiry = $expiry, value = '$value' WHERE sesskey = '$key'"; $qid = pg_exec($SESS_DBH, $qry); } return $qid; } The first time I browse the test page, PHP calls the session handling function in the right way, i.e.: sess_open() sess_read(...) /* here the select returns 0 rows, i.e. no state variables */ sess_write(...) /* here the initial value is inserted into database with the * INSERT INTO query */ sess_close(...) If I reload the test page with the session variable $count, this is what happens: sess_open() sess_read(...) /* here the select returns 'count|i:0;' */ sess_write(...) /* the INSERT INTO fails because sesskey is a primary key */ boooom! The script ends without doing the UPDATE inside sess_write() at the point marked with the arrow: function sess_write($key, $val) { global $SESS_DBH, $SESS_LIFE; $expiry = time() + $SESS_LIFE; $value = addslashes($val); $qry = "INSERT INTO sessions VALUES ('$key', $expiry, '$value')"; $qid = pg_exec($SESS_DBH, $qry); ------>> if (! $qid) { $qry = "UPDATE sessions SET expiry = $expiry, value = '$value' WHERE sesskey = '$key'"; $qid = pg_exec($SESS_DBH, $qry); } return $qid; } Even an fwrite($fp, "before if\n") doesn't get executed!! This solved the problem: function sess_write($key, $val) { global $SESS_DBH, $SESS_LIFE; $expiry = time() + $SESS_LIFE; $value = addslashes($val); $qry = "SELECT 1 FROM sessions WHERE sesskey = '$key'"; $qid = pg_exec($SESS_DBH, $qry); if (pg_numrows($qid) > 0) { $qry = "UPDATE sessions SET expiry = $expiry, value = '$value' WHERE sesskey = '$key'"; $qid = pg_exec($SESS_DBH, $qry); } else { $qry = "INSERT INTO sessions VALUES ('$key', $expiry, '$value')"; $qid = pg_exec($SESS_DBH, $qry); } return $qid; } This works, but I think it's very ugly. I don't want to do an extra SELECT for each PHP page served. Does anybody have a solution for this problem? Francesco Casadei