stucked on insert stored procedure mysql php

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
ajamesc21
Forum Newbie
Posts: 1
Joined: Wed Feb 01, 2017 3:43 pm

stucked on insert stored procedure mysql php

Post by ajamesc21 »

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:

Code: Select all

<!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 ;
Attachments
Here's the picture of what the website looks like
Here's the picture of what the website looks like
php4blog
Forum Newbie
Posts: 2
Joined: Thu Feb 16, 2017 1:45 am

Re: stucked on insert stored procedure mysql php

Post by php4blog »

You are going wrong for the inserting data for inserting data just one simple Query Like insert into table name and it's field name and all things. for Insert Update Delete with source codeTry it Every time I get help from this example I think you need give try once.
Thanks
Post Reply