0

I am calling a mySQL Stored Procedure with OUT parameters using PDO and PHP. I cannot get the bindParam() function to execute at all. After much research, I have put together a remedial php script to try to figure this out. The stored procedure works perfectly in mySQL Workbench.

Thanks in advance for your attention.

FROM phpInfo() PHP Version 7.1.11-0ubuntu0.17.10.1 Apache/2.4.27 (Ubuntu) PDO support enabled PDO drivers mysql PDO Driver for MySQL enabled Client API version mysqlnd 5.0.12-dev - 20150407 MySQL Server 5.7

The Stored Procedure

CREATE DEFINER=`root`@`localhost` PROCEDURE `getScoreRecordsByEventId`(
    eventId int,
    OUT teamId int,
    OUT teamName varchar(30),
    OUT oppId int,
    OUT oppName varchar(30)
)
BEGIN
    set teamId = getTeamIdByEventId(eventId);
    set teamName = getTeamNameByEventId(eventId);
    set oppId = getOppIdByEventId(eventId);
    set oppName = getOppNameByEventId(eventId);

    SELECT act_time, act, `quarter`, 
    team_id FROM floor_stats WHERE event_id = eventId AND act LIKE 
    '%made' ORDER BY quarter, act_time; 
END

This pulls from two tables:
  events:
    id        int PK
    team_id    int
    team_name    varchar(30)
    opponent_id    int
    opp_name    varchar(30)
floor_stats:
    act_time    int
    act    varchar(10)
    quarter    int

I call several functions inside the stored procedure to provide the OUT values. Again, the SP works fine in mySQL Workbench.

The program for testing/learning:

  <?php
    echo "<b>begin</b><br>";
    $dsn = 'mysql:dbname=stats;host=127.0.0.1';
    $user = 'root';
    $password = 'Jeuchems1';

    echo "<b>ready to connect</b><br>";  
    try {
        $dbh = new PDO($dsn, $user, $password);
        $dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
        echo "  <b>var_dump of dbh (PDO Connection)</b><br>";
        var_dump($dbh);

      } catch (PDOException $e) {
        echo 'Connection failed: ' . $e->getMessage();
    }
    echo "<b>connected  :)</b><br>";

    echo "<br><b>initialize variables to receive output from stored procedure</b><br>";  
    $eventId = 5;
    echo "event_id: $event_id<br>";
    $team_id = 0;
    echo "team_id: $team_id<br>";
    $team_name = 'xxx';
    echo "team_name: $team_name<br>";
    $opp_id = 0;
    echo "opp_id: $opp_id<br>";
    $opp_name = 'xxx';
    echo "opp_name: $opp_name<br>";
    echo "<br>";

    echo "<b>ready to prepare the statement with named parameters</b><br>";  
    $sth = $dbh->prepare('CALL getScoreRecordsByEventId(:eventId, :teamId, :teamName, :oppId, :oppName)');
    echo "  <b>var_dump of sth (PDO statement)</b><br>";
    var_dump($sth);
    echo "completed prepare statement<br>";

    echo "<br><b>ready to bindParam </b><br>";  
    echo "  binding eventId: ".(($sth->bindValue(":eventId", $event_id, PDO::PARAM_INT)) ? "success<br>" : "failure<br>");
    echo "  binding teamId: ".(($sth->bindParam(":teamId", $team_id, PDO::PARAM_INT|PDO::PARAM_OUTPUT)) ? "success<br>" : "failure<br>");
    echo "  binding teamName: ".(($sth->bindParam(":teamName", $team_name, PDO::PARAM_STR|PDO::PARAM_OUTPUT, 30)) ? "success<br>" : "failure<br>");
    echo "  binding oppId: ".(($sth->bindParam(":oppId", $opp_id, PDO::PARAM_INT|PDO::PARAM_OUTPUT)) ? "success<br>" : "failure<br>");
    echo "  binding oppName: ".(($sth->bindParam(":oppName", $opp_name, PDO::PARAM_STR|PDO::PARAM_OUTPUT, 30)) ? "success<br>" : "failure<br>");
    echo "completed bindParam<br>";  

    echo "<br><b>ready to execute</b><br>";  
    $sth->execute();
    echo "completed execute<br>";  

    echo "<br><b>ready to fetch</b><br>";  
    $results = $sth->fetch(PDO::FETCH_ASSOC);
    echo "completed fetch.<br>";  

    echo "<br>var_dump of results<br>";  
    var_dump($results);
    echo "count(results):". count($results);
    echo $results[0];
    echo "<BR>";

    echo "<b>ready to echo the result keys.<br>";
    $keys = array_keys($results);  
    foreach($keys as $key) {
      echo "key:$key<br>";
    }
  ?>

This is the output I get. It should at least give me a 'failed' or something. :(

begin
ready to connect
var_dump of dbh (PDO Connection)
object(PDO)#1 (0) { } connected :)
initialize variables to receive output from stored procedure
event_id:
team_id: 0
team_name: xxx
opp_id: 0
opp_name: xxx
ready to prepare the statement with named parameters
var_dump of sth (PDO statement)
object(PDOStatement)#2 (1) { ["queryString"]=> string(72) "getScoreRecordsByEventId(:eventId, :teamId, :teamName, :oppId, :oppName)" }
completed prepare statement
ready to bindParam
binding eventId: success

After all this work, I will be both exhilarated and frustrated if it is a simple fix.

Thanks for your help.

Darrell Oller
  • 1
  • 1

0 Answers0