Skip site navigation (1) Skip section navigation (2)

Storing PHP sessions into a PostgreSQL database

From: Francesco Casadei <f_casadei(at)libero(dot)it>
To: pgsql-php(at)postgresql(dot)org
Subject: Storing PHP sessions into a PostgreSQL database
Date: 2001-08-29 20:51:19
Message-ID: 20010829225119.A1249@goku.kasby (view raw or flat)
Thread:
Lists: pgsql-php
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


pgsql-php by date

Next:From: Brent R. MatzelleDate: 2001-09-05 17:24:47
Subject: Date calc/comparisions
Previous:From: Manuel LemosDate: 2001-08-28 12:45:00
Subject: Re: Web hosting list

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group