I’ve got stuck on insert stored procedure for php since I have no idea of how to do that. Please note that I’m new to php and mysql. Here’s my php code:
<!DOCTYPE html>
<html>
<body>
<form method="POST">
<label for= "FirstName">First name:<br> </label>
<input id="FirstName" name="FirstName" type="text">
<br>
<label for="LastName">Last name:<br> </label>
<input id="LastName" name="LastName" type="text">
<label for="Password"><br>Password:<br> </label>
<input id ="Password" name="Password" type="text">
<br>
<label for="EmailAddress">Email Address:<br></label>
<input id="EmailAddress" name="EmailAddress" type="text" />
<br>
<label for="RoleName"> Role Name:<br> </label>
<!-- creating the dropdownlist called rolename and I got stuck
stuck on how to get roleid -->
<select name='RoleName'>
<?php
//connect to database
$connection = mysqli_connect("localhost", "user", "password", "construction", "3306");
//run the store procedure for get all roles for the dropdownlist
$sql = mysqli_query($connection,
"CALL getallroles") or die("Query fail: " . mysqli_error()); //run your query
$select = $_POST['RoleName'];
// echo "<select name='RoleName'>"; // list box select command
//looping for all of the role names
while($row=mysqli_fetch_array($sql))
{ $select.='<option value="'.$row[0].'">'.$row[0].'</option>';
}
echo $select;// Closing of list box
?>
</select>
<br><br>
<!-- <input type="submit" -->
<button type ="submit" value="Submit">Insert
</button>
<!-- I will be calling the insert stored procedure where I will be passing the parameters unfortunately it didn't work at all and I wasn't able to figure it out of how to get roleid from dropdownlist without showing it -->
<?php
//connect to database
$connection = mysqli_connect("localhost", "user", "password", "construction", "3306");
//checking to make sure the textbox are not empty
if (!empty($_GET))
$FirstName = $_POST['FirstName'];
//$FirstName = $_POST['FirstName'];
// $LastName = $_POST['LastName'];
if (!empty($_GET))
$LastName = $_POST['LastName'];
// $Password = $_POST['Password'];
if (!empty($_GET))
$Password = $_POST['Password'];
// $select = $_POST['RoleName'];
if (!empty($_GET))
$select = $_POST['RoleName'];
//this one is for creating hashing passwords
$options = ['cost' => 12,];
$iSalt = password_hash("rasmuslerdorf", PASSWORD_BCRYPT, $options);
//hashing the passwords
$Password2 =password_hash($Password, PASSWORD_BCRYPT, $options);
$EmailAddress = $_POST['EmailAddress'];
//Having trouble with insert stored procedure in which i've never done that part before
//I'm trying to get the mysql_insert_id to work but it didn't work at all.
$sql2 = mysqli_query($connection,
"CALL registerusers(mysql_insert_id()),$FirstName,$LastName,$Password2,$EmailAddress,$iSalt,$select)") or die("Query fail: " . mysqli_error()); //run your query
?>
</form>
</body>
</html>
Here’s my mysql code for getallroles stored procedure
DELIMITER @@
DROP PROCEDURE getallroles @@
CREATE PROCEDURE construction.getallroles
()
BEGIN
select RoleName,RoleID from roles;
End @@
DELIMITER ;
Also, here’s the insert stored procedure in mysql for registerusers 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 ;
Also, here’s the picture of what the actual result looks like.
