Get value from newly inserted row through form?

[xwishmasterx]

I have a small form, then after submit it inserts a new row with a few values (time, email)

 

This form also sends email to the email from the form with a link.

 

Problem, the link needs to contain an autoincrement value from the newly inserted row. How can I do this?

 

(the form that obvioulsy doesn't work, for obviously reasons but to show what I try to accomplish:P)

 

<? if (isset($_REQUEST['Submit'])) { 
$time= time();
# THIS CODE TELL MYSQL TO INSERT THE DATA FROM THE FORM INTO YOUR MYSQL TABLE 
$sql = "INSERT INTO phpfox_invite (user_id,email,time_stamp,is_used) values ('".$b."','".mysql_real_escape_string(stripslashes($_REQUEST['email']))."','".$time."','1')";

$sql = mysql_query("SELECT invite_id FROM phpfox_invite WHERE email='".mysql_real_escape_string(stripslashes($_REQUEST['email']))."'");
$result = mysql_fetch_array( $sql );
$inviteid = $result['invite_id'];

if($result = mysql_query($sql)) { 
$modtager = "".mysql_real_escape_string(stripslashes($_REQUEST['email']))."";
        $emne = "Please complete Your registration";
        $besked = "Click the link below to complete your registration: \n 
                            http://domain.com/invite/id_".$inviteid." ";
                           
        $header = "from:system@domain.com";

        mail($modtager, $emne, $besked, $header);
       echo 'Check Your Email!'; 
} else { 
echo "ERROR: ".mysql_error(); 
} 

?>

 

Everything works ok, but ofcourse I cannot get the 'invite_id' before the form is submitted and therefor cannot send the value

 

[batwimp]

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

 

$sql = mysql_query("SELECT invite_id FROM phpfox_invite WHERE email='".mysql_real_escape_string(stripslashes($_REQUEST['email']))."'");
$result = mysql_fetch_array( $sql );
$inviteid = mysql_insert_id();

[xwishmasterx]

thanks for the reply.

It only returns the value "0", it should return the newly created value for "invite_id" in the newly insterted row.

[Jessica]

The mysql_insert_id() needs to be after your insert statement, not a select.

[xwishmasterx]

That just confuses me.lol

 

So after I do my insert, how do I specify what field I want to select?

 

$sql = "INSERT INTO phpfox_invite (user_id,email,time_stamp,is_used) values ('".$b."','".mysql_real_escape_string(stripslashes($_REQUEST['email']))."','".$time."','1')";

 

This will ofc create a new row, and the column "invite_id" with be generated with autoincrement. So how do I get that value?

[Jessica]

using mysql_insert_id

[KevinM1]

mysql_insert_id

 

$result = mysql_query("INSERT INTO blah blah blah....");
$id = mysql_insert_id();

 

It's pretty straightforward.

[xwishmasterx]

Sorry for being sarcastic, but to my knowlegde these codes ussually do what you tell them too, not want you wish them to do

 

What if I had two columns that did some autoincrement, how would you specify which of those values you need?

[xwishmasterx]

I think I am not making myself clear:

 

I am NOT looking for the value of the new row. I am looking for a specific column value in the new row.

 

eg. I insert: name, email to the table.

the new row now has name, email, 24 , where 24 is the autoincrement value. How can I get the 24 value?

[Jessica]

Did you bother looking it up before asking these questions? We are not google.

http://us.php.net/mysql_insert_id

http://dev.mysql.com/doc/refman/5.5/en/example-auto-increment.html

 

Did you even TRY it before telling us we're wrong??

[KevinM1]

Sorry for being sarcastic, but to my knowlegde these codes ussually do what you tell them too, not want you wish them to do

 

Indeed, which is why you're having trouble.  You do realize that you never actually execute your INSERT query in the code above?

 

As far as two+ auto-increment values, you'd have to manually SELECT the row you want.  The function only returns one id.

 

I think I am not making myself clear:

 

I am NOT looking for the value of the new row. I am looking for a specific column value in the new row.

 

eg. I insert: name, email to the table.

the new row now has name, email, 24 , where 24 is the autoincrement value. How can I get the 24 value?

 

You've been given the answer by three people, and have been shown the official documentation at least twice.  The onus is now on you to get it to work.

[xwishmasterx]

I have been looking around and tried google

 

Now, you say I need to use the function after my insert statement, but also I need to SELECT which row?

 

I believe my poor explanation is why I keep asking

 

The problem in my original code is that the INSERT hasn't really taken place when I do my SELECT. (refreshing the page will get me a value, but not a solution)

 

Was just wondering if you could query a table immediately after an insert looking for the newly created row, using a simple form, but might have to re-think it.

[KevinM1]

Now, you say I need to use the function after my insert statement, but also I need to SELECT which row?

 

NO.  Re-read what I said.  You asked about having TWO auto-incremented ids in the same row.  I replied that in that case, mysql_insert_id wouldn't work because it only returns ONE id.  In that kind of case you'd need to SELECT that row after INSERTING it.

 

I believe my poor explanation is why I keep asking

 

No, the problem is a complete lack of reading comprehension.  Here's a breakdown of the process:

 

INSERTED row only has one auto-incremented column: Use mysql_insert_id after you execute the INSERT query

 

INSERTED row has two or more auto-incremented columns: Figure out a way to SELECT the appropriate row after you execute the INSERT query

 

It cannot be broken down or written more clearly or explicitly.

[xwishmasterx]

Kevin, please understand that not everyone has English as their native language, and coding skills might not be very good.

I am sorry for keep asking, but was trying to understand what was really happening when using the code.

 

Also, a few of the example answers might have been correct use of the code, but not working in my case, which only confused me even more

 

I have gotten it too work, simple adding your suggestion in the right place

 

if($result = mysql_query($sql)) { 

$inviteid = mysql_insert_id();

$modtager = "".mysql_real_escape_string(stripslashes($_REQUEST['email'])).""; .....

[Adam]

You can only have one auto incrementing column in a table, so mysql_insert_id will always return the right value. If it's not the auto incremented column you want, then you should already have the data available from when you built up the insert SQL.