CODEWITHSUNDEEP
phpmysqlsqlpdo
leo
leo

Reputation: 121

How to call a stored procedure with multiple parameters?

How would I go about calling a stored procedure in PHP with several parameters?

let's say I have a stored procedures like this in mysql:

delimiter //
create procedure createInvoice (inProduct varchar(20), inCost float, inAmount integer)
begin 
.....
.....
.....
end//
delimiter ;

Now in PHP I want to call this procedure and set the inProduct, inCost and inAmount variables to values that comes from a form. How would I go about doing this? I've tried setting it up like this but it's not working out.

.....
.....
.....
<?php
if(isset($_POST['invoiceform']) {
 $sp = 'call createInvoice("' . $_POST['product'] . ',' . $_POST['cost'] . ',' . $_POST['amount'] . '")'; 
.....
.....
.....
}
?>

So I just wanna know how to formulate the "call"-code in PHP. The rest I know (forms, what to write after the call etc).

UPDATE: So I have it like this now:

if(isset($_POST['invoiceform']) {
 $sp = 'call createInvoice(?,?,?);

$spParameter([$_POST['product], $_POST['cost'], $_POST['amount']; 

$stmt = $pdo->prepare($call);
$stmt->bindParam('inProduct', $_POST['product']);
$stmt->bindParam('inCost', $_POST['cost']);
$stmt->bindParam('inAmount', $_POST['amounnt']);
$stmt->execute();

I get this error: Warning: PDOStatement::execute(): SQLSTATE[HY093]: Invalid parameter number: parameter was not defined in x/x/x/x/x/invoice.php on line x PDOStatement Object ( [queryString] => CALL createInvoice(?,?,?) )

UPDATE 2: SOLVED IT:

if(isset($_POST['invoiceform']) {
 $sp = 'call createInvoice(?,?,?);

$spParameter([$_POST['product], $_POST['cost'], $_POST['amount']; 

$stmt = $pdo->prepare($call);
$stmt->bindParam('inProduct', $_POST['product']);
$stmt->bindParam('inCost', $_POST['cost']);
$stmt->bindParam('inAmount', $_POST['amounnt']);
$stmt->execute($spParameters);

Upvotes: 1

Views: 1891

Answers (1)

ArSeN
ArSeN

Reputation: 5248

By what you are currently doing you get a concatenated string like this:

call createInvoice("product, cost, amount")

Which means you are only passing one parameter, namely the first one.

But what you actually want is this to pass all three individually:

call createInvoice("product", "cost", "amount")

So you have to add in the quotes accordingly when doing the string concatenation, like so:

$sp = "call createInvoice(?, ?, ?)"; 
$spParameter = [$_POST["product"], $_POST["cost"], $_POST["amount"]];

Prepare $sp then execute with parameters in $spParameter.

Side note: you are wide open to SQLinjection.

Upvotes: 1

Related Questions