ok, i have this interface.
interface i_db
{
/*
* make a database connection
*/
public function __construct($dbhost,$dbuser,$dbpass,$dbname);
/*
* close database connection.
*/
public function __deconstruct();
/*
* query the database.
* returns true or false for insert / update.
* or
* returns result resource for select.
*/
public function query($sql);
/*
* fetch a database row.
* returnsa row from the database as an object.
*/
public function fetch();
/*
* retrieve number of rows returned by last query.
*/
public function numrows();
/*
* return number of rows effected by last query.
*/
public function affected();
/*
* retieve the id of the last inserted row.
*/
public function lastid();
}
for which i am trying to build both a mysql and a postgres database object. the problem i am having is that postgres doesn't seem to have an equivelent to mysql's mysql_insert_id() function, so im having trouble creating my lastid() method.
for the mysql (which i havent yet built) i could easily use something like.
public function lastid()
{
return mysql_last_id($this->result);
}
and be done. with postgres, it appears i have to run a query something like...
"SELECT CURRVAL('$seq_name') AS seq"
the problem is, i have no way of passing in the $seq_name variable. as you can see by my interface, there are no arguments to this method, and really, i dont want to have to add one, because then i would have to pass an argument even when im using mysql. get what i meen?
is there a better way of getting the last inserted records id in postgres? ive only just started using postgres, so im still learning stuff. im using 8.0
ps: at this point in time i can still make changes to the interface, i just dont really want to have to pass a useless argument to mysql's implimenation of my object. i suppose i could make it optional, but, surely there is a better way.My brain's shutting down for the night, but say you based the sequence name on the table name so that you could construct the one given the other...?yeah, i was thinking that. i could also create a regex or something to strip this table name straight out of the sql statement passed to the query() method, thus eliminating the need to pass it to the lastid() method as an argument.
maybe this is the way to go? maybe its the only way to go.Have you looked at pg-last-oid (<!-- m --><a class="postlink" href="http://uk2.php.net/manual/en/function.pg-last-oid.php">http://uk2.php.net/manual/en/function.pg-last-oid.php</a><!-- m -->)OID field became an optional field from PostgreSQL 7.2 and will not be present by default in PostgreSQL 8.1.
dont want to get stuck with it.Something else mentioned on that page is To get the value of a SERIAL field in an inserted row, it is necessary to use the PostgreSQL CURRVAL function, naming the sequence whose last value is required. If the name of the sequence is unknown, the pg_get_serial_sequence PostgreSQL 8.0 function is necessary.
PostgreSQL's documentation on the function sez
g_get_serial_sequence(table_name, column_name) fetches the name of the sequence associated with a serial or bigserial column. The name is suitably formatted for passing to the sequence functions. NULL is returned if the column does not have a sequence attached.Soooo.... calling "SELECT pg_get_serial_sequence(table_name, column_name)" should tell you which sequence to look at for the CURRVAL if you don't already know it.
Note (for further implementations) that using sequences rather than an AUTO_INCREMENT type is the more common approach among DBMSs; it has to be noted that sequences are a lot more versatile than MySQL's AUTO_INCREMENT type.its dirty, but ive got a workaround in place. thanks.
interface i_db
{
/*
* make a database connection
*/
public function __construct($dbhost,$dbuser,$dbpass,$dbname);
/*
* close database connection.
*/
public function __deconstruct();
/*
* query the database.
* returns true or false for insert / update.
* or
* returns result resource for select.
*/
public function query($sql);
/*
* fetch a database row.
* returnsa row from the database as an object.
*/
public function fetch();
/*
* retrieve number of rows returned by last query.
*/
public function numrows();
/*
* return number of rows effected by last query.
*/
public function affected();
/*
* retieve the id of the last inserted row.
*/
public function lastid();
}
for which i am trying to build both a mysql and a postgres database object. the problem i am having is that postgres doesn't seem to have an equivelent to mysql's mysql_insert_id() function, so im having trouble creating my lastid() method.
for the mysql (which i havent yet built) i could easily use something like.
public function lastid()
{
return mysql_last_id($this->result);
}
and be done. with postgres, it appears i have to run a query something like...
"SELECT CURRVAL('$seq_name') AS seq"
the problem is, i have no way of passing in the $seq_name variable. as you can see by my interface, there are no arguments to this method, and really, i dont want to have to add one, because then i would have to pass an argument even when im using mysql. get what i meen?
is there a better way of getting the last inserted records id in postgres? ive only just started using postgres, so im still learning stuff. im using 8.0
ps: at this point in time i can still make changes to the interface, i just dont really want to have to pass a useless argument to mysql's implimenation of my object. i suppose i could make it optional, but, surely there is a better way.My brain's shutting down for the night, but say you based the sequence name on the table name so that you could construct the one given the other...?yeah, i was thinking that. i could also create a regex or something to strip this table name straight out of the sql statement passed to the query() method, thus eliminating the need to pass it to the lastid() method as an argument.
maybe this is the way to go? maybe its the only way to go.Have you looked at pg-last-oid (<!-- m --><a class="postlink" href="http://uk2.php.net/manual/en/function.pg-last-oid.php">http://uk2.php.net/manual/en/function.pg-last-oid.php</a><!-- m -->)OID field became an optional field from PostgreSQL 7.2 and will not be present by default in PostgreSQL 8.1.
dont want to get stuck with it.Something else mentioned on that page is To get the value of a SERIAL field in an inserted row, it is necessary to use the PostgreSQL CURRVAL function, naming the sequence whose last value is required. If the name of the sequence is unknown, the pg_get_serial_sequence PostgreSQL 8.0 function is necessary.
PostgreSQL's documentation on the function sez
g_get_serial_sequence(table_name, column_name) fetches the name of the sequence associated with a serial or bigserial column. The name is suitably formatted for passing to the sequence functions. NULL is returned if the column does not have a sequence attached.Soooo.... calling "SELECT pg_get_serial_sequence(table_name, column_name)" should tell you which sequence to look at for the CURRVAL if you don't already know it.Note (for further implementations) that using sequences rather than an AUTO_INCREMENT type is the more common approach among DBMSs; it has to be noted that sequences are a lot more versatile than MySQL's AUTO_INCREMENT type.its dirty, but ive got a workaround in place. thanks.