retrive id from previous page. 1

JJJ777 · Oct 23, 2002

hi all

if in mysql i wrote this...

$query=mysql_query("insert into table1 (name,gender) values ('$name','$gender')&quot

or die ("could not select&quot

;

$profileid=mysql_insert_id();

but now i'm using postgresql so what is the similar code in postgresql especially the mysql_insert_id().

Leland123 · Oct 23, 2002

Hi JJJ777,

If you have your sequence in place the number will automatically be place in the postgres table when you execute the insert. After creating the transaction, I immediately select the transaction from the table and display it complete with the new auto incremented number for the user that filled out the form, just to let him/her know the update was successful. The primary unique key in this example is the cno field.

You might try something like:

############################### Connect to the database.
##############################

$dbh = DBI->connect
("DBI

g:dbname=$the_db;host=$the_host", $the_user,
$the_pass_wd, {'RaiseError' => 1});

$dbh->do("INSERT INTO smorders
(date_it,first,last,company,address,city,state,postal,phone,
email,pass_key,payby,cardtype,cardholder,cardnumber,cardmont
h,cardyear,amount,processed) VALUES
('now',$lfirst,$llast,$lcompany,$laddress,$lcity,$lstate,$lp
ostal,$lphone,$lemail,$lpass_key,$lpayby,$lcardtype,$lcardho
lder,$lcardnumber,$lcardmonth,$lcardyear,$lamount,$lprocesse
d)&quot

;

############################################################
$sth = $dbh->prepare("SELECT currval
('smorders_cno_seq')&quot

;

$sth->execute;

$array_ref = $sth->fetchall_arrayref();

foreach my $row (@$array_ref) {
@temp = @$row;
}

$leland=$temp[0];
##########################################################

$sth = $dbh->prepare("SELECT cno,first,last,company,email
FROM smorders WHERE cno=$leland AND first=$lfirst AND
last=$llast AND company=$lcompany AND email=$lemail&quot

;

$sth->execute;

############################################################
#####################

print "<html>";

print "<head>";

print "<title>Software - Master (TM)
Metacharacter</title>\n";

print "</head>";

print "<body TEXT='black' bgcolor='white'>";

print "<h3><B>";

print "<TABLE bgcolor=$the_color ALIGN='center'
cellpadding='0' cellspacing='0'
bordercolorlight=$the_borderlight
bordercolordark=$the_borderdark BORDER='7' WIDTH='100%'>\n";

print "<CAPTION><H1>Sales Order Update</H1></caption>";

print "<TR bgcolor=$headcolor> $thetitle</TR>";

$array_ref = $sth->fetchall_arrayref();

foreach my $row (@$array_ref) {

@$therow = @$row;

print sprintf "$thesprintf", @$therow;
}

$dbh->disconnect();

print "</TABLE>";
if ($leland>0) {

print "<h3><b>Enter the registration number below
into your program.</b></h3>\n";

print "<H1>$factor</H1>";

print "<h3><b>Thank You\! Your Card Has Passed
Validation. It will now be submitted for Charge
Authorization.</b></h3>\n";

print "</body>\n</html>\n";

# Remember mail to purchaser

&send_mail;

# Remember mail to Seller

&send_mailx;

} else {

print "<h3><b>Your Submission did not update our
database. Please go <FONT COLOR='\#FF0000\'>Back</font>
and resubmit your order. Thanks.</b></h3>\n";

print "</body>\n</html>\n";

}

exit;

Leland F. Jackson, CPA
Software - Master (TM)

https://www.smvfp.com

Nothing Runs Like the Fox

JJJ777 · Oct 23, 2002

first of all...tq very much

mmm yes u are rite that is one way how to do it..

i'm still currious..about the similar function of mysql_insert_id()[return the id generated by an auto increment field in the previous insert query];

is it similar to pg_getlastoid in progresql and how to use it ?

Code:

:

tq

Leland123 · Oct 24, 2002

Hi JJJ777,

The key to auto incrementing in postgres is to create a "SERIAL" type field in your table. I use phpPgAdmin to set up tables in postgres. You are probably familiar with phpMyAdmin. These tools make it vary easy to administer a database and create new tables, because all the work is done through a graphical user interface.

The following command will create a serial field in a table named lelo:

CREATE TABLE "lelo" (
"the_unique" SERIAL,
"firstname" varchar (21) NOT NULL,
"lastname" varchar (15) NOT NULL );

Creating table lelo with the SERIAL field result in a sequence table also being create. The sequence table is named "lelo_the_unique_seq". Also a default value is automatically created for the SERIAL field as follows:

nextval('"lelo_the_unique_seq"'::text)

Whenever a new row is inserted into table lelo, the default value places the nextval() obtained from the "lelo_the_unique_seq" table into the the_unique field of table lelo. It is interesting to know that the name of the sequence table is the "table name_" + "field name_" + "seq" of the table to which it relates.

The "lelo_the_unique_seq" table has the following fields:

squence_name
last_value
increment_by
max_value
min_value
cache_value
log_cnt
is_cycled
is_called

You can display these values just like in any other postgres table like:

select * from lelo_the_unique_seq;

In the perl script above I grab the current sequence number form the just inserted row. Postgres will hold a reference to the current sequence for you, even when others have already incremented the sequence. If the current value is greater than zero, then the record was inserted correctly.

############################################################
$sth = $dbh->prepare("SELECT currval('smorders_cno_seq')&quot

$sth->execute;

$array_ref = $sth->fetchall_arrayref();

foreach my $row (@$array_ref) {
@temp = @$row;
}

$leland=$temp[0];
###################################################

When a SERIAL field is part of a newly created table, the SERIAL field will automatically be indexed and unique. If the SERIAL filed is also to be the primary key for the table, you must specify this.
Leland F. Jackson, CPA
Software - Master (TM)

https://www.smvfp.com

Nothing Runs Like the Fox

Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

retrive id from previous page. 1

JJJ777

Programmer

Leland123

Programmer

JJJ777

Programmer

Leland123

Programmer

Similar threads

Part and Inventory Search

Sponsor