Re: Last ID Problem - Mailing list pgsql-novice

On Mon, 31 Jan 2005 15:33:02 -0800 (PST),
operationsengineer1@yahoo.com <operationsengineer1@yahoo.com> wrote:
> thanks mitch...
>
> i ahve the following code...
>
> $cust = $_POST['cust'];
> $cust = addslashes($cust);
> $db = &ADONewConnection('postgres');
> $db -> Connect($db_string,$db_owner,$db_pw,$db_name);
> $sql = "INSERT INTO customer (customer_name) VALUES
> ('$cust')";
> $result = $db->Execute($sql);
> $insert_id = $db->getone("select currval('cust_id')");
>
> if ($result === false)
> {
> print $db->ErrorMsg();
> exit();
> }
> else
> {
> $dbreturn = 'Passed';
> print $dbreturn;
> print $insert_id;
> exit();
> }
>
> it prints $dbreturn as "Passed", but it does not print
> any value for insert_id.  i'm trying to see this value
> and verify it is working correctly before trying
> anything more complex.

That is because you are doing it out of order.  First, you get the
sequence id, and THEN you use that number for your INSERT statement:

$cust = $_POST['cust'];
$cust = addslashes($cust);
$db = &ADONewConnection('postgres');
$db -> Connect($db_string,$db_owner,$db_pw,$db_name);
// get the insert id FIRST
$insert_id = $db->getone("select currval('cust_id')");
// THEN issue the INSERT statement
$sql = 'INSERT INTO customer (id, customer_name) VALUES
(' . $id . ', ' . $db->qstr( $cust ) . ')';

if ( $db->Execute( $sql ) === false ){
    print $db->ErrorMsg();
} else {
    $dbreturn = 'Passed';
    print $dbreturn;
    print $insert_id;
}

I also changed around the format of your SQL statement, as it makes
sense to quote your $cust before adding to the database. So so you see
the difference?  You need to get the sequence number first, and then
use it in your queries. The exit() statements were not needed, and I
wanted to show a different way of nesting your IF statement.

Note that an INSERT statement doesn't return a resultset, just a
success or fail. John's way of doing it (at least for the
documentation) are found here:

    http://phplens.com/lens/adodb/docs-adodb.htm#ex3

It is a good example, as it quotes strings and uses time() as well.

-- Mitch