Stored Procedures, mySQL and PHP5

Question

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 do have a small amount of experience with PHP4 and mySQL, and previously when working with these was limited to in-line SQL.

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 & PHP5?

Yes my version of mySQL supports Stored Procedures so lets skip that trap!

Answer 1

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/

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

Answer 2

I see this post tagged as PHP5, I'll try to answer as if you were using PHP5 (don't see a reason for not to).

Forget about mysqli, it's buggy and should have been already removed.
Rather 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";

Answer 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.

Answer 4

I totally agree with "michal kralik" about using the new PDO class, unfortunately I can't comment on his post yet. Remove any chance of sql injection by using the prepared statements.

Answer 5

I have been using ADODB, which is a great thing for abstracting actual commands to make it portable between different SQL Servers (ie mysql to mssql). However, Stored procedures do not appear to be directly supported. What this means, is that I have run a SQL query as if it is a normal one, but to "call" the SP. An example query:

$query = "Call HeatMatchInsert('$mMatch', '$mOpponent', '$mDate', $mPlayers, $mRound,  '$mMap', '$mServer', '$mPassword', '$mGame', $mSeason, $mMatchType)";

This isn't accounting for returned data,which is important. I'm guessing that this would be done by setting a @Var , that you can select yourself as the return @Variable .

To be Abstract though, although making a first php stored procedure based web app was very difficult to work around (mssql is very well documented, this is not), It's great after its done - changes are very easy to make due to the seperation.

Answer 6

how to set up a stored procedure in wamp server ?