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.