I'm stuck on insert stored procedure mysql in php and I couldn't get to work


#1

I've got stuck on insert stored procedure for php since I have no idea of how to do that. I'm actually new to php since I just finished the php tutorial on code academy. 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
  $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 ;

Here's the stored procedure of mysql 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 ;

Also, here's the actual picture of what it looks like: