Trouble returning id from auto increment

[gerrybytes]

Could someone please help. I've searched alot of forums and website but none seem to give me the answer.

I've posted info to my database and i'm looking to return the id using the mysqli_insert_id() function. I'm posting it to php. Please someone help, going a bit mad as no integer is returned.

$query ="INSERT INTO New_Fault(Title,Forename,Surname,Email,Message)
VALUES('$Title','$Forename','$Surname','$Email','$Message')";
 
$result = mysqli_query($cxn,$query) or die ('error making query');
if($result){
echo "Your unique Reference Number is = " mysqli_insert_id;
echo "<BR>";
echo "<a href=' put link here'>Back to main page</a>";
    }
else {echo "ERROR";
}    
mysqli_close();
?>
 

Thanks G

[debasisdas]

The mysqli_insert_id() function returns the ID generated by a query on a table with a column having the AUTO_INCREMENT attribute. If the last query wasn't an INSERT or UPDATE statement or if the modified table does not have a column with the AUTO_INCREMENT attribute, this function will return zero.

Note: Performing an INSERT or UPDATE statement using the LAST_INSERT_ID() function will also modify the value returned by the mysqli_insert_id() function.

Return Values
The value of the AUTO_INCREMENT field that was updated by the previous query. Returns zero if there was no previous query on the connection or if the query did not update an AUTO_INCREMENT value.

Note: If the number is greater than maximal int value, mysqli_insert_id() will return a string.

[ronverdonk]

The mysqli statement for this is mysqli_stmt_insert_id. However, there is no further description (yet) in the documenation, but it should be something like[php]mysqli_stmt_insert_id($result);[/php]If you are not sure you can always use the LAST_INSERT_ID() function of MySqli and your code would then be something like:[php]if($result){
$res=mysqli_query("SELECT LAST_INSERT_ID() FROM New_Fault AS last LIMIT 1");
$row=mysqli_fetch_row($res);
$last=$row[0];
echo "Your unique Reference Number is = $last <br>";
echo "<a href=' put link here'>Back to main page</a>";
}[/php]Ronald

[gerrybytes]

The mysqli statement for this is mysqli_stmt_insert_id. However, there is no further description (yet) in the documenation, but it should be something like[php]mysqli_stmt_insert_id($result);[/php]If you are not sure you can always use the LAST_INSERT_ID() function of MySqli and your code would then be something like:[php]if($result){
$res=mysqli_query("SELECT LAST_INSERT_ID() FROM New_Fault AS last LIMIT 1");
$row=mysqli_fetch_row($res);
$last=$row[0];
echo "Your unique Reference Number is = $last <br>";
echo "<a href=' put link here'>Back to main page</a>";
}[/php]Ronald

Hi Ronald,

Thanks for your help but i must be doing something silly, when i implement your solution nothing is returned.

Any idea why?

G

[ronverdonk]

Hi Ronald,

Thanks for your help but i must be doing something silly, when i implement your solution nothing is returned.

Any idea why?
G

You mean that not even the ERROR message is returned? When not you must be doing something okay.

So: what is returned?

Ronald

[gerrybytes]

You mean that not even the ERROR message is returned? When not you must be doing something okay.

So: what is returned?

Ronald

Hi Ronald,

No error message is returned because the information is sent correctly to the database, i check this manually.
The page displays;

Your unique Reference Number is =
Back to main page

No value is inserted after equals sign. It seems like its something small but I have no idea what.

G

[ronverdonk]

Theh let's have a look at the row. Do this and see what is displayed[php]if($result){
$res=mysqli_query("SELECT LAST_INSERT_ID() FROM New_Fault AS last LIMIT 1")
or die("Query error ".mysqli_error());
$row=mysqli_fetch_row($res);
echo '<pre>'; print_r($row);
$last=$row[0];
echo "Your unique Reference Number is = $last <br>";
echo "<a href=' put link here'>Back to main page</a>";
}[/php]Ronald

[gerrybytes]

Theh let's have a look at the row. Do this and see what is displayed[php]if($result){
$res=mysqli_query("SELECT LAST_INSERT_ID() FROM New_Fault AS last LIMIT 1")
or die("Query error ".mysqli_error());
$row=mysqli_fetch_row($res);
echo '<pre>'; print_r($row);
$last=$row[0];
echo "Your unique Reference Number is = $last <br>";
echo "<a href=' put link here'>Back to main page</a>";
}[/php]Ronald

It returns Query error but no error message. This information is also still being posted to the database.

G

[ronverdonk]

I cannot see the problem. Last resort is to do this[php]$res=mysqli_query("SELECT LAST_INSERT_ID()");[/php] but that will only work when your ID is not a bigint.

Ronald

[gerrybytes]

I cannot see the problem. Last resort is to do this[php]$res=mysqli_query("SELECT LAST_INSERT_ID()");[/php] but that will only work when your ID is not a bigint.

Ronald

It seems like a very trivial problem. Thanks for all your help in trying to get this sorted.

My table uses auto increment, is value for this is BIGINT?

Can this be changed so ur last suggestion can be tried?

G

[ronverdonk]

When your auto_increment field is a bigint you can change that to an int via

ALTER TABLE CHANGE COLUMN col_name int

When it is not or no longer a bigint, you can try that last suggestion.

Ronald