I couldn't get the mysqli_insert_id to work in php from mysql stored procedure on call stored prcoedure insert

[jamesgo43]

I couldn’t get the insert stored procedure to work with auto increment in php from mysql.

<?php

             //connect to database
  $connection = mysqli_connect("localhost", "user", "password", "buildings", "3306");

  //Having problems with inserting stored procedure because
  // the mysql_insert_id()) would not work because it is auto increment 
  //calling local variables
 $Firstname = "jamie";
 $Lastname = "jones";
 $Password = "1234";
 $EmailAddress = "jones@gmail.com";
 $Salt = "23";
 $RoleID = 1;


    //calling stored procedure called registerusers that pass all of parmeters      
 $sql = mysqli_query($connection, "CALL registerusers(NULL,'$Firstname,$Lastname,$Password,$EmailAddress,$Salt,$RoleID')");
           //suppose to print out the id number but it didn't work at all
            printf("Last inserted record has id %d\n", mysqli_insert_id());

            ?>

Here’s the picture of what the error looks like:

Here’s my stored procedure in register users mysql:

registerusers.txt

Here’s my mysql table for the table users

create table users.txt

[Dormilich]

the issue has nothing to do with stored procedures. you simply failed to call the function correctly – as is plainly stated in the error messge.

[jamesgo43]

I have no idea of how to call insert stored procedure function in php for mysql since I don’t know how to do it in php.

[jamesgo43]

I’ve tried
$sql = $connection->multi_query( “CALL registerusers(@new_id,$Firstname,$Lastname,$Password,$EmailAddress,$Salt,$RoleID);SELECT @new_id” );

// mysqli_query($connection,$sql);
echo “new record has id:” . mysqli_insert_id($connection);

I still got the message saying “new record has id:0”

[droopsnoot]

Are the error messages still appearing?

[jamesgo43]

no error message but I kept getting
“new record has id:0”

that looks like this:

[droopsnoot]

That suggests an error in the way you call the stored procedure, or something in the procedure itself. In the first code you posted, you seem to have surrounded all the parameters in single quotes, which would cause them to be passed as a single string. Have you changed that now? Can you post the current code exactly as it is?

[jamesgo43]

[droopsnoot]

From the documentation:

" 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. "

Your last query in the call was “SELECT @new_id”, so the insert_id returns zero. Looking at the stored procedure, you should retrieve the value from that SELECT to get the insert id.

[jamesgo43]

I’ve tried:

$sql = $connection->multi_query( “CALL registerusers(@UserID,$Firstname,$Lastname,$Password,$EmailAddress,$Salt,$RoleID);SELECT @UserID” );

the result is I still get the same result

[droopsnoot]

Yes, but if you read the documentation, you won’t get a value in mysqli_insert_id because of that final select. From what I read, and I don’t know stored procedures so it’s only based on the doc, but you need to do something like:

$sql = $connection->multi_query( "CALL registerusers(@UserID,$Firstname,$Lastname,$Password,$EmailAddress,$Salt,$RoleID);SELECT @UserID as _lastinsert" ); 
$row = $connection->fetch_assoc();
echo $row['_lastinsert'];

[jamesgo43]

could you post the link of that document also I’ve that method that you posted and I still got an error.

$sql = $connection->multi_query( “CALL registerusers(@UserID,$Firstname,$Lastname,$Password,$EmailAddress,$Salt,$RoleID);SELECT @UserID as last_insert_id()” );

// mysqli_query($connection,$sql);
// echo “new record has id:” . mysqli_insert_id($sql);

$row = $connection->fetch_assoc();
echo $row[‘last_insert_id()’];

[droopsnoot]

That’s strange, but this is the page I was reading from: http://php.net/manual/en/mysqli.quickstart.stored-procedures.php

I’m not sure you can use last_insert_id() as the alias to return the figure from the stored procedure, I don’t think brackets are allowed in the name.

ETA: Stupid me, it should be

$row = $sql->fetch_assoc();

You fetch the results from the query result, not the connection.