pass $_post array elements of form as a string to mysql stored routines

162 views Asked by At

I have to insert data into tblusers for which I have writen stored routine in mysql which has following insert statement:

CREATE DEFINER=`root`@`localhost` PROCEDURE `insert_user`(IN `user_name` VARCHAR(255), IN `user_age` INT, IN `user_desgn` VARCHAR(255), OUT `return_message` VARCHAR(255))
BEGIN
  INSERT INTO tblusers
  VALUES (user_name,user_age,user_desgn);

  SET return_message = 'Congratulations Inserted Successfully';

END

I have a form in php which is used to insert the data into the table. Once I submit the form I would like to pass the form element stored in $_POST array into my stored routine insert user.

Presently I am using php pdo to call procedures ( prepare and bind ) as following:

$stmt = $handler->prepare("CALL insert_user(?,?,?,@return_msg)");

$name = $_POST['username'];
$age = $_POST['userage'];
$desgn = $_POST['userdesgn'];

$stmt->bindParam(1, $name, PDO::PARAM_STR, 50);
$stmt->bindParam(2, $age, PDO::PARAM_INT); 
$stmt->bindParam(3, $desgn, PDO::PARAM_STR, 50);

// call the stored procedure
$stmt->execute();

This is working perfectly. But when I have more number of columns, the code becomes lengthy, hence I want to change my way doing things. I want to pass $_POST array to stored routine and insert the data. Is there any way I can achieve that ? Please guide me what changes I have to do in my stored procedure and in my php pdo code respectively. I am new to php, it would be great if you can explain with an example.

0

There are 0 answers