Stored Procedures, MySQL and PHP

The question is a fairly open one. I've been using Stored Procs with MS SQLServer for some time with classic ASP and ASP.net and love them, lots.

I have a small hobby project I'm working on and for various reasons have gone the LAMP route. Any hints/tricks/traps or good starting points to get into using stored procedures with MySQL and PHP5? My version of MySQL supports Stored Procedures.

Solution 1:

@michal kralik - unfortunately there's a bug with the MySQL C API that PDO uses which means that running your code as above with some versions of MySQL results in the error:

"Syntax error or access violation: 1414 OUT or INOUT argument $parameter_number for routine $procedure_name is not a variable or NEW pseudo-variable".

You can see the bug report on bugs.mysql.com. It's been fixed for version 5.5.3+ & 6.0.8+.

To workaround the issue, you would need to separate in & out parameters, and use user variables to store the result like this:

$stmt = $dbh->prepare("CALL sp_takes_string_returns_string(:in_string, @out_string)");
$stmt->bindParam(':in_string', 'hello'); 

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

// fetch the output
$outputArray = $this->dbh->query("select @out_string")->fetch(PDO::FETCH_ASSOC);

print "procedure returned " . $outputArray['@out_string'] . "\n";

Solution 2:

Forget about mysqli, it's much harder to use than PDO and should have been already removed. It is true that it introduced huge improvements over mysql, but to achieve the same effect in mysqli sometimes requires enormous effort over PDO i.e. associative fetchAll.

Instead, take a look at PDO, specifically prepared statements and stored procedures.

$stmt = $dbh->prepare("CALL sp_takes_string_returns_string(?)");
$value = 'hello';
$stmt->bindParam(1, $value, PDO::PARAM_STR|PDO::PARAM_INPUT_OUTPUT, 4000); 

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

print "procedure returned $value\n";

Solution 3:

It isn't actually mandatory to use mysqli or PDO to call stored procedures in MySQL 5. You can call them just fine with the old mysql_ functions. The only thing you can't do is return multiple result sets.

I've found that returning multiple result sets is somewhat error prone anyway; it does work in some cases but only if the application remembers to consume them all, otherwise the connection is left in a broken state.

Solution 4:

You'll need to use MySQLI (MySQL Improved Extension) to call stored procedures. Here's how you would call an SP:

$mysqli = new MySQLI(user,pass,db);

$result = $mysqli->query("CALL sp_mysp()");

When using SPs you'll need close first resultset or you'll receive an error. Here's some more information :

http://blog.rvdavid.net/using-stored-procedures-mysqli-in-php-5/ (broken link)

Alternatively, you can use Prepared Statements, which I find very straight-forward:

  $stmt = $mysqli->prepare("SELECT Phone FROM MyTable WHERE Name=?");

  $stmt->bind_param("s", $myName);

  $stmt->execute();

MySQLI Documentation: http://no.php.net/manual/en/book.mysqli.php

Stored Procedures, MySQL and PHP

Solution 1:

Solution 2:

Solution 3:

Solution 4:

Related

Recent Posts