Having trouble with mysqli_insert_id mysql stored procedure in php


#1

Hi,

I couldn't get the mysqli_insert_id mysql to work in php

<?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')");

echo "new record has id:" . mysqli_insert_id($connection);

?>

Here's the picture of what it looks like:

Also here's my mysql code for registerusers in stored procedure:

DELIMITER @@
DROP PROCEDURE registerusers @@
CREATE PROCEDURE construction.registerusers
(OUT UserID TINYINT(11), IN iFirstName VARCHAR(30), IN iLastName VARCHAR(30), IN iPassword VARCHAR(30), IN iEmailAddress VARCHAR(30), IN iSalt VARCHAR(40), IN iRoleID VARCHAR(1))
BEGIN
declare checkexistingemailaddress varchar(30);

select emailaddress into checkexistingemailaddress
from users
where emailaddress = iEmailaddress;

If(iFirstName ='') then
SIGNAL SQLSTATE '45000'
SET MESSAGE_TEXT = 'Fill out the First Name ';

Elseif(iLastName='') then
SIGNAL SQLSTATE '45000'
SET MESSAGE_TEXT = 'Fill out the Last Name';

Elseif(iPassword='') then
SIGNAL SQLSTATE '45000'
SET MESSAGE_TEXT = 'Fill out the Password';

Elseif(iEmailAddress='') then
SIGNAL SQLSTATE '45000'
SET MESSAGE_TEXT = 'Fill out the Email Address';
Elseif(iEmailaddress=checkexistingemailaddress ) then
SIGNAL SQLSTATE '45000'
SET MESSAGE_TEXT = 'Email Address already exists';

Elseif(iEmailaddress not RLIKE'^[a-zA-Z0-9][a-zA-Z0-9._-][a-zA-Z0-9._-]@[a-zA-Z0-9][a-zA-Z0-9._-][a-zA-Z0-9].[a-zA-Z]{2,4}$') then
SIGNAL SQLSTATE '45000'
SET MESSAGE_TEXT = 'Please enter the corerct email address';

else

insert into users(

FirstName,
LastName ,
Password ,
EmailAddress ,
Salt ,
RoleID
)
Values
(
iFirstName,
iLastName ,
iPassword ,
iEmailAddress ,
iSalt ,
iRoleID
);
set UserID = last_insert_id();
end if;

End @@
DELIMITER ;