Getting back id after adding a record.

[padders]

I have ID set to auto increment but after adding a row of data i want to get back into the variable the id of that row which i can then use. How do i do that?

I can see how it is done in the tutorial on mysql and php but that is after going through each record, i only want it for the one i have just added.

Thanks.

[aspen]

Run two queries on the page.

You're passing the variables to be added right?

so do this

Insert Into Table(Value1, Value2, Value3)
Values($Value1, $Value2, $Value3)

then do another query

Select ID
From Table
Where value1 = $value1 and value2 = $value2 and value3 = $value3

And then you have your ID.

Now of course this will only work if the combination of values that make up that record are unique. If not you'll get a few rows returned.

Another option would be to count all the records in your DB using the Count() function. Then Dynamically creating a LIMIT that selects the last record in your DB (the last record will be the most recently added)

Limits and the Count function are covered in Kevin's tutorial.

Chris

[padders]

arr i see makes sense.

what i am doing is adding some info from a form input but then want to send an email containing the id. The info will be unique so that is not a problem.

I have been looking how vbulletin does this sort of thing, bascially when you start a new thread (because it then has to redirect you to that thread getting the id number) but they are using pre-built mysql sub routines i think which i don't want to use as this is just a simple script.

Thanks.

[freddydoesphp]

Forget the two query idea, mysql has a built-in function this very thing
if your inert query was:
$result = mysql_query("INSERT into tablename VALUES ('$val1', '$val2')");
then mysql_insert_id($result) will give you what you are after
so
$lastid = mysql_insert_id($result) will hold the value of the last inserted id number

[padders]

Arr. i hoped there would be something like that. I am new to php and the book i have been trying to learn from recommended this:

	$sql =  "INSERT INTO $set_table SET " .         
		"refer_name='$refer_name', " .
		"refer_email='$refer_email', " .
		"refer_note='$note', " .
		"name='$name[$count]', " .
		"email='$email[$count]', " .
		"clicked='N', " .
		"reminders='0', " .
		"last_email_date=CURDATE(), " .
		"no_reminders='N', " .
		"redirect_url='$redirect2', " .
		"homepage='N', " .
		"date=CURDATE()";  
  if (mysql_query($sql)) {    
    echo("<P>Your details have been added</P>");  
  } else {    
    echo("<P>Error adding details: " .         
	mysql_error() . "</P>");  
  }

which is a different format from your mysql_query statement. I like it in that you don't have to have the fields in order and can leave some out as well. How do i convert the $lastid into this format.

I tried with an intermediary like:

$inter = mysql_query($sql);
$lastid = mysql_insert_id($inter);

but that didn't work either, so i am stumped.

Thanks a lot for any help, i really appreciate it.

[freddydoesphp]

Sorry my mistake, actually you need to use

$lastid = mysql_insert_id($db);

Where $db is the varaible you assigned to your connection like
$db = mysql_connect("host", "user", "pass");

[padders]

works a treat. Thanks a lot that is brillaint.