Page 1 of 1

[SOLVED] How do I access a just-created record?

Posted: Wed Dec 03, 2003 4:24 pm
by pbrissett
I have an individuals table and an address table. The latter has a
primary key that is a foreign key in the individuals table. If, when I
create a new record in individuals and that person has an address not
already in the address table, my script creates a new record in the
address table, which is assigned a key by autoincrement. But I can't
figure out how to get that new address ID into the new individuals
record.

I'm running MySQL 4.0.15 and PHP 4.3.3 on a Macintosh G4. I use Safari to access the pages.

Here's the code:

<?php

mysql_connect ("localhost" ,"root" ,"XXXXXXXXX" );

mysql_select_db("myexp" );

//This is preparation for calculating current age later on

$year=$_POST[year]; $month=$_POST[month]; $day=$_POST[day];
$molen=strlen($month); $dalen=strlen($day);

if ($molen<2) { $month = "0".$month; } else { $month=$month; }

if ($dalen<2) { $day="0".$day; } else { $day=$day; }





$var1=$_POST['FirstName'];
$var2=$_POST['MidName'];
$var3=$_POST['LastName'];
$var4="$_POST[Address]";
$var5=$_POST['City'];
$var6=$_POST['State'];
$var7=$_POST['ZIP'];
$var8=$_POST['DayPhone'];
$var9=$_POST['NitePhone'];
$var10=$year . '-'.$month.'-'.$day;


//Following section assigns street, city, state and zip references


$query = "SELECT XStID from xstreet where XStreet LIKE '$var4'";
$resultStreet = mysql_query ($query);

if ($row = mysql_fetch_array($resultStreet))
{do{
$var104=$row["XStID"];
}
while ($row = mysql_fetch_array($resultStreet));
} else {

// Following two lines DON'T do what I need done

$inquery="INSERT INTO xstreet (XStID, XStreet) VALUES (NULL, '$var4')";
$result=mysql_query ($inquery);
}


//irrelevent code omitted


$result=mysql_query ("insert into people (FName, MName, LName,
AddrStreet, AddrCity, AddrState, AddrZIP, DayPh, NightPh, DOB)
values ('$var1', '$var2', '$var3', '$var104', '$var105', '$var106',
'$var107', '$var8', '$var9','$var10')") or die (mysql_error());

?>

Posted: Wed Dec 03, 2003 4:33 pm
by Weirdan
[php_man]mysql_insert_id[/php_man]

Posted: Thu Dec 04, 2003 1:58 am
by Pyrite
What about for SELECT, UPDATE, DELETE queries? Is there a simular function for those too?

Posted: Thu Dec 04, 2003 2:36 am
by twigletmac
Pyrite wrote:What about for SELECT, UPDATE, DELETE queries? Is there a simular function for those too?
For SELECT - you can get the field value via the SELECT statement.
For UPDATE and DELETE - you really should know what records you're updating or deleting before you do it.

Mac

Posted: Thu Dec 04, 2003 3:49 am
by Pyrite
Yea I don't know where my mind is tonight. But last night I was coding and using the last insert id function and I can't remember for what, but I was wishing it worked for select too, but I can't remember why. Nevermind.

Posted: Thu Dec 04, 2003 9:02 am
by pbrissett
Wierdan,

Thanks. Exactly what I need. Can't believe I spent two days scouring the manual and didn't find this.

Paul