MySQL Forums
Forum List  »  PHP

Having trouble with mysqli_insert_id mysql stored procedure in php
Posted by: Andrew James
Date: February 02, 2017 03:26PM

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 ;

Options: ReplyQuote




Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.