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

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
pbrissett
Forum Newbie
Posts: 2
Joined: Wed Dec 03, 2003 4:24 pm

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

Post 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());

?>
User avatar
Weirdan
Moderator
Posts: 5978
Joined: Mon Nov 03, 2003 6:13 pm
Location: Odessa, Ukraine

Post by Weirdan »

[php_man]mysql_insert_id[/php_man]
User avatar
Pyrite
Forum Regular
Posts: 769
Joined: Tue Sep 23, 2003 11:07 pm
Location: The Republic of Texas
Contact:

Post by Pyrite »

What about for SELECT, UPDATE, DELETE queries? Is there a simular function for those too?
User avatar
twigletmac
Her Royal Site Adminness
Posts: 5371
Joined: Tue Apr 23, 2002 2:21 am
Location: Essex, UK

Post 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
User avatar
Pyrite
Forum Regular
Posts: 769
Joined: Tue Sep 23, 2003 11:07 pm
Location: The Republic of Texas
Contact:

Post 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.
pbrissett
Forum Newbie
Posts: 2
Joined: Wed Dec 03, 2003 4:24 pm

Post by pbrissett »

Wierdan,

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

Paul
Post Reply