getting an autoincr. field from a mysql table

[glennn1137]

i'm writing from a form to a mysql db table, Subscribers, that has an autoincrementing number field, called SubscriberID, in it. the form is going to pass the user to a second form/page, which will write to a table called Cars, and i need to know how to get this autoincrementing value, SubscriberID to the second form/page and into a field so it will be submitted to the second table. make sense?

thanks for your help,

glenn nall

[ahundiak]

http://www.php.net/manual/en/function.mysql-insert-id.php

[kevinsequeira]

http://ca.php.net/manual/en/function.mysql-insert-id.php

reg
kevin

[tsinka]

Hi,

after you inserted the values into the table you can use mysql_insert_id() to get the value of the autoincrement field.

$dbconn=mysql_connect(...) or die("Error: ".mysql_error());
mysql_select_db("thedb",$dbconn) or die("Error: ".mysql_error());

$res = mysql_query("INSERT INTO ...",$dbconn) or die("Error: ".mysql_error());
$theID = mysql_insert_id($res);

Thomas

[glennn1137]

cool, thanks so far, but it returned a "0" to my second table SubscriberID field...

the article says "mysql_insert_id() returns 0 if the previous query does not generate an AUTO_INCREMENT value. If you need to save the value for later, be sure to call mysql_insert_id() immediately after the query that generates the value. "

but the previous query IS generating a value...

help?

thanks fellas

[kevinsequeira]

can u post the code uve written

reg
kevin

[tsinka]

... and the table layout ?

[glennn1137]

this writes to the first table:

$link = mysql_connect("localhost","gregj_public","private");
mysql_select_db("gregj_subscribers",$link);
$query="insert into subscribers (name,street,city,state,zip,phone1,phone2,email1,email2,rec1name,rec1Street,rec1City,rec1State,rec1Zip,rec1HomeTel,rec1CellTel,rec1Email1,rec1Email2,rec1Fax,rec1Pager,rec1OCI,rec2Name,rec2Street,rec2City,rec2State,rec2Zip,rec2HomeTel,rec2CellTel,rec2Email1,rec2Email2,rec2Fax,rec2Pager,rec2OCI,rec3Name,rec3Street,rec3City,rec3State,rec3Zip,rec3HomeTel,rec3CellTel,rec3Email1,rec3Email2,rec3Fax,rec3Pager,rec3OCI,prefContact1,prefContact2,prefBegTime,prefEndTime,noPrefTime,directContact,password,amountSent,agree) values ('".$Name."','".$Street."','".$City."','".$State."','".$Zip."','".$Phone1."','".$Phone2."','".$Email1."','".$Email2."','".$rec1Name."','".$rec1Street."','".$rec1City."','".$rec1State."','".$rec1Zip."','".$rec1HomeTel."','".$rec1CellTel."','".$rec1Email1."','".$rec1Email2."','".$rec1Fax."','".$rec1Pager."','".$rec1OCI."','".$rec2Name."','".$rec2Street."','".$rec2City."','".$rec2State."','".$rec2Zip."','".$rec2HomeTel."','".$rec2CellTel."','".$rec2Email1."','".$rec2Email2."','".$rec2Fax."','".$rec2Pager."','".$rec2OCI."','".$rec3Name."','".$rec3Street."','".$rec3City."','".$rec3State."','".$rec3Zip."','".$rec3HomeTel."','".$rec3CellTel."','".$rec3Email1."','".$rec3Email2."','".$rec3Fax."','".$rec3Pager."','".$rec3OCI."','".$preferredContactMethod1."','".$preferredContactMethod2."','".$peferredBegTime."','".$peferredEndTime."','".$noPreferredTime."','".$directContact."','".$password."','".$amount."','".$agree."')";
mysql_query($query);
$make=fopen("form/forms/admin/data.dat","a");
$to_put="";
$to_put .= $Name."|".$Street."|".$City."|".$State."|".$Zip."|".$Phone1."|".$Phone2."|".$Email1."|".$Email2."|".$rec1Name."|".$rec1Street."|".$rec1City."|".$rec1State."|".$rec1Zip."|".$rec1HomeTel."|".$rec1CellTel."|".$rec1Email1."|".$rec1Email2."|".$rec1Fax."|".$rec1Pager."|".$rec1OCI."|".$rec2Name."|".$rec2Street."|".$rec2City."|".$rec2State."|".$rec2Zip."|".$rec2HomeTel."|".$rec2CellTel."|".$rec2Email1."|".$rec2Email2."|".$rec2Fax."|".$rec2Pager."|".$rec2OCI."|".$rec3Name."|".$rec3Street."|".$rec3City."|".$rec3State."|".$rec3Zip."|".$rec3HomeTel."|".$rec3CellTel."|".$rec3Email1."|".$rec3Email2."|".$rec3Fax."|".$rec3Pager."|".$rec3OCI."|".$PreferredContactMethod1."|".$PreferredContactMethod2."|".$peferredBegTime."|".$peferredEndTime."|".$noPreferredTime."|".$directContact."|".$password."|".$amount."|".$agree."
";
header("Refresh: 0;url=http://www.safe-teen.net/autoreg.htm");
}
?>

this to the second:

$link = mysql_connect("localhost","gregj_public","private");
mysql_select_db("gregj_subscribers",$link);
$query="insert into Cars (SubscriberID,veh1Owner,veh1Make,veh1Model,veh1Color,veh1Style,veh1License,veh1ODC,veh1DecalID,veh2Owner,veh2DecalID,veh2Make,veh2Model,veh2Color,veh2Style,veh2License,veh2ODC,veh3Owner,veh3DecalID,veh3Make,veh3Model,veh3Color,veh3Style,veh3License,veh3ODC,field) values (LAST_INSERT_ID(),'".$RegisteredOwner."','".$Make."','".$YearandModel."','".$Color."','".$Style."','".$License."','".$OtherDistinguishingCharacterics."','".$DecalID."','".$Car2Owner."','".$Car2DecalID."','".$Car2Make."','".$Car2Model."','".$Car2Color."','".$Car2Style."','".$Car2License."','".$Car2Other."','".$Car3Owner."','".$Car3DecalID."','".$Car3Make."','".$Car3Model."','".$Car3Color."','".$Car3Style."','".$Car3License."','".$Car3Other."','".$field1."')";
mysql_query($query);
header("Refresh: 0;url=http://www.safe-teen.net/thanks.htm");

table layout?

tell me what you'd like to see...?

[glennn1137]

sorry bout the long string...

it also worked only once with the "0" and now it won't post any further records to the second table...

darnit!!!

[tsinka]

Sorry,

I wasn't clear enough, I meant the table creation (overview of the fields and the types) 🙂

[glennn1137]

you were plenty clear enough - i'm a newbie :o)

CREATE TABLE subscribers (
SubscriberID int(150) unsigned NOT NULL auto_increment,
name text NOT NULL,
street text NOT NULL,
city text NOT NULL,
state text NOT NULL,
zip text NOT NULL,
phone1 text NOT NULL,
phone2 text NOT NULL,
email1 text NOT NULL,
email2 text NOT NULL,
rec1name text NOT NULL,
rec1Street text NOT NULL,
rec1City text NOT NULL,
rec1State text NOT NULL,
rec1Zip text NOT NULL,
rec1HomeTel text NOT NULL,
rec1CellTel text NOT NULL,
rec1Email1 text NOT NULL,
rec1Email2 text NOT NULL,
rec1Fax text NOT NULL,
rec1Pager text NOT NULL,
rec1OCI text NOT NULL,
rec2Name text NOT NULL,
rec2Street text NOT NULL,
rec2City text NOT NULL,
rec2State text NOT NULL,
rec2Zip text NOT NULL,
rec2HomeTel text NOT NULL,
rec2CellTel text NOT NULL,
rec2Email1 text NOT NULL,
rec2Email2 text NOT NULL,
rec2Fax text NOT NULL,
rec2Pager text NOT NULL,
rec2OCI text NOT NULL,
rec3Name text NOT NULL,
rec3Street text NOT NULL,
rec3City text NOT NULL,
rec3State text NOT NULL,
rec3Zip text NOT NULL,
rec3HomeTel text NOT NULL,
rec3CellTel text NOT NULL,
rec3Email1 text NOT NULL,
rec3Email2 text NOT NULL,
rec3Fax text NOT NULL,
rec3Pager text NOT NULL,
rec3OCI text NOT NULL,
prefContact1 text NOT NULL,
prefContact2 text NOT NULL,
prefBegTime text NOT NULL,
prefEndTime text NOT NULL,
noPrefTime text NOT NULL,
directContact text NOT NULL,
password text NOT NULL,
amountSent text NOT NULL,
agree text NOT NULL,
PRIMARY KEY (SubscriberID),
UNIQUE KEY SubscriberID (SubscriberID),
UNIQUE KEY id (SubscriberID)
) TYPE=MyISAM COMMENT=''

CREATE TABLE Cars (
SubscriberID int(10) unsigned NOT NULL default '0',
veh1Owner text NOT NULL,
veh1Make text NOT NULL,
veh1Model text NOT NULL,
veh1Color text NOT NULL,
veh1Style text NOT NULL,
veh1License text NOT NULL,
veh1ODC text NOT NULL,
veh1DecalID text NOT NULL,
veh2Owner text NOT NULL,
veh2DecalID text NOT NULL,
veh2Make text NOT NULL,
veh2Model text NOT NULL,
veh2Color text NOT NULL,
veh2Style text NOT NULL,
veh2License text NOT NULL,
veh2ODC text NOT NULL,
veh3Owner text NOT NULL,
veh3DecalID text NOT NULL,
veh3Make text NOT NULL,
veh3Model text NOT NULL,
veh3Color text NOT NULL,
veh3Style text NOT NULL,
veh3License text NOT NULL,
veh3ODC text NOT NULL,
field text NOT NULL,
PRIMARY KEY (SubscriberID),
UNIQUE KEY SubscriberID (SubscriberID),
UNIQUE KEY id (SubscriberID)
) TYPE=MyISAM COMMENT=''

[kevinsequeira]

in the first page are u using this to redirect to the 2nd page ?

header("Refresh: 0;url=http://www.safe-teen.net/autoreg.htm");

if thats the case u need to pass the id in the link,
als uve used last_insert_id() , u have to use mysql_insert_id() function !!

reg
kevin

[glennn1137]

like this?

http://www.safe-teen.net/autoreg.htm?SubscriberID=mysql_insert_id() ?

if yes, then do i have to GET it at the next page before i run

" $query="insert into Cars (SubscriberID,veH1... " ?

thanks very much
glenn

[glennn1137]

that ain't gonna work (my option there)...

how does the link get the variable if it's in the table and not the form? wouldn't i have to call the variable in the first form, too?

somehow?

[tsinka]

Hi,

if you need the last inserted ID on the next page I would suggest the following (shortened)

mysql_query(theinsertquey);
$theid=mysql_last_insert_id();
header("Location: thenextscript?theid=".$theid);

On the next page you can then use it:

$theid=$_GET['theid'];
mysql_query("INSERT INTO ... VALUES ($theid,....)");

Hope this helps you a little bit 🙂

[tsinka]

About that "long line thing",

You can use heredoc style to improve readabiliby of your code and to reduce the lines to a certain length 🙂

$strSQL = <<< EOSQL
INSERT INTO thetable
(field1,field2,...,fieldn)
VALUES ('value1','value2',...,'valuen')
EOSQL;

$res = mysql_query($strSQL);

Thomas

[glennn1137]

i'm assuming "$theid" is my field name, "$SubscriberID" ?

i did this:

mysql_query($query);
$SubscriberID=mysql_last_insert_id();

$make=fopen("form/forms/admin/data.dat","a");
$to_put="";
$to_put .= $Name."|".$Street."|".$City...$agree."
";

header("Refresh: 0;url=http://www.safe-teen.net/autoreg.htm?SubscriberID=".$SubscriberID);

just as it's written:

mysql_query(theinsertquey);
$theid=mysql_last_insert_id();
header("Location: thenextscript?theid=".$theid);

i got " Call to undefined function: mysql_last_insert_id() in /home/gregj/public_html/process.php on line 131 "

please forgive my obtuseness - this seems to make sense, i just don't know the syntax very well...

glenn

[tsinka]

Oooops,

sorry, seems like I can't write anymore 🙂

It's mysql_insert_id() not mysql_last_insert_id()

Sorry 🙁

[glennn1137]

that's quite all right - that got me to the next page, it passed the variable, but ever since my first attempt i've been unable to write to the second database (after it put a 0 in the new SubscriberID field)...

here's my second piece of code, and i'll not bother you any more, thanks:

$message = stripslashes($message);
mail("[e-mail]","Form Submitted at your website",$message,"From: Safe Teen, Inc.");
$link = mysql_connect("localhost","gregj_public","private");
mysql_select_db("gregj_subscribers",$link);
$query="insert into Cars (SubscriberID,veh1Owner,veh1Make,veh1Model,veh1Color,veh1Style,veh1License,veh1ODC,veh1DecalID,veh2Owner,veh2DecalID,veh2Make,veh2Model,veh2Color,veh2Style,veh2License,veh2ODC,veh3Owner,veh3DecalID,veh3Make,veh3Model,veh3Color,veh3Style,veh3License,veh3ODC,field) values ('".SubscriberID."','".$RegisteredOwner."','...eld1."')";

mysql_query($query);
$SubscriberID=$_GET['SubscriberID'];
header("Refresh: 0;url=http://www.safe-teen.net/thanks.htm");
}
?>

thanks for all your help.
g

[tsinka]

Hi,

no problem, programming can sometimes be very hard 🙂

$message = stripslashes($message); 
mail("[e-mail]","Form Submitted at your website",$message,"From: Safe Teen, Inc."); 
$link = mysql_connect("localhost","gregj_public","private"); 
mysql_select_db("gregj_subscribers",$link); 
$SubscriberID=$_GET['SubscriberID']; 
$query="insert into Cars (SubscriberID,veh1Owner,veh1Make,veh1Model,veh1Col
or,veh1Style,veh1License,veh1ODC,veh1DecalID,veh2O
wner,veh2DecalID,veh2Make,veh2Model,veh2Color,veh2
Style,veh2License,veh2ODC,veh3Owner,veh3DecalID,ve
h3Make,veh3Model,veh3Color,veh3Style,veh3License,v
eh3ODC,field) values ('".$SubscriberID."','".$RegisteredOwner."','...eld1."')"; 

mysql_query($query); 
header("Refresh: 0;url=http://www.safe-teen.net/thanks.htm"); 
} 
?>