Having trouble with mysqli_insert_id mysql stored procedure in php
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 my stored procedure in registerusers
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 ;