php + mySQL stored procedures question

terry_s

just wondering has anyone successfully managed to call a mysql stored procedure using php ?

im testing this on winxp using IIS .

using the code below and getting nothing back just blank page .

when i call the stored procedure in mysql works fine.

did some googling and found suggestions about using the mysqli extn so uncommented it in the php.ini file .
and used mysqli_connect etc. still no joy.

if i replace the stored procedure with SQL
ie"select * from blah "

it works fine




<?php
$con = mysql_connect("localhost","username","password");
if (!$con)
{
die('Could not connect: ' . mysqli_error());
}

mysql_select_db("database", $con);

$result = mysql_query("CALL spReport_getCustomers");


while($row = mysql_fetch_array($result))
{
echo $row . " " . $row;
echo "<br />";
}

mysql_close($con);

?>

CoNfOuNd

What version of MySQL have you installed?

terry_s

mySQL 5 and php 5

CoNfOuNd

If it's version 5.1.12 or under you should use empty brackets. If it's 5.1.13 or more recent you don't need them.

e.g. CALL spReport_getCustomers()

terry_s

Sorry am using 5.1.12 and tried it with and with the brackets .

still no luck

The Corinthian

PHP has problems getting back recordsets from MySQL procs. From what I have ascertained in the past this is down to the connection type (so that it supports CLIENT_MULTI_RESULTS) and one solution I found was by connecting via:
[PHP]mysql_connect(server, username, password, true, 131074);[/PHP]
TBH though, MySQL procs are very ropey and until either Zend or MySQL are able to integrate the two technologies better, you're better off using them sparingly and for single variable or even void return type operations, if at all.

terry_s

Cheers that worked a treat.

usually develope through asp or .net using sql server so when i heard mysql5 supported stored procedures i said i'll give it a go with php.
I the past the sql for certain things i work on could be 50+ lines so would not like to have that in the page.

Once again thanks for teh help