Programing with php7.0 a function to call mysql stored procedures dynamically

Question

Just a few words to explain what I want to do.

I want to extend my lib with a function able to call stored procedures. I found here the steps to make it but I have a hard time to make it generic.

To achieve this, I've read that I have to use the call_user_func_array on mysqli_stmt_bind_param because I know only at execution the number of parameters IN, there types and the number of OUT

Here a few details of what I've done:

• create a connection to the DB
• set the charset
• build the call string for example CALL (?,?,?,@outParam1,@outParam2)
• prepare the call string
• build an array of parameters to use with call_user_func_array that will call mysqli_stmt_bind_param <= this what give's me troubles
• call mysql_stmt_excute
• finally, make a select to get out parameters.

Here the main parts of code :

function executeProc($sProcName, $sInParamTypes, $aInParam, $iNumberParameterOut=0)
{
  $oConnection = @mysqli_connect(DB_HOSTNAME, DB_USERNAME, DB_PASSWORD, DB_DBNAME);

  mysqli_set_charset($oConnection, "utf8");

  /* code to build the $sCallStr */

  $oMysqlCall = mysqli_prepare($oConnection, $sCallStr);

  array_unshift($aFunctionParam, $oMysqlCall, $sInParamTypes);

  $aFunctionParam = array_merge($aFunctionParam, $aInParam);

  call_user_func_array("mysqli_stmt_bind_param", $aFunctionParam);

  mysqli_stmt_execute($oMysqlCall);
}

the return of call_user_func_array is NULL. I've tryed to call the function manualy :

 mysqli_stmt_bind_param($aFunctionParam[0], $aFunctionParam[1], $aFunctionParam[2], $aFunctionParam[3], $aFunctionParam[4], $aFunctionParam[5], $aFunctionParam[6], $aFunctionParam[7]);

And it works. I see the result in the DB and the return of the function is TRUE. Can someone help here? Is there another way to pass the array of parameters? (like by reference ?)

Thx a lot for your help

Answer 1

You could use "Argument unpacking" using ... :

mysqli_stmt_bind_param($oMysqlCall, $sInParamTypes, ...$aInParam);

This will work with variables passed by references, and there is no need to use array_unshift() and array_merge() to build your parameters.

---

Example :

function foo(&$a, &$b, &$c) { var_dump($a,$b,$c); }

This will works :

$arr = [1,2,3];
foo(...$arr); // Works

But this will generate a warning :

$arr = [1,2,3];
call_user_func_array('foo', $arr); 
// Warning:  Parameter 1 to foo() expected to be a reference, value given

Answer 2

Here is a feedback to my problem:

I've managed with your help and this post https://stackoverflow.com/a/48128540/6050817 to make my funtion and it works great.

I wanted to post the complet code I've made to share. This function execute a stored procedure and collectes all the datas selected by this procedure and all the out parameters

function executeProc($sProcName, $sInParamTypes = "", $aInParam = null, $iNumberParameterOut=0)
{
  global $oError;
  $oError = false;
  $aRows = array();
  $sCallStr = "";
  $sSelectOutParam = "";
  $aReturn = array();

  if($aInParam == null)
  {
    $aInParam = array();
  }

  $iLevel = error_reporting(9);

  // open connection to DB
  $oConnection = @mysqli_connect(DB_HOSTNAME, DB_USERNAME, DB_PASSWORD, DB_DBNAME);
  if ($oConnection === false)
  {
    $oError = mysqli_error($oConnection);
    return false;
  }
  // we set the character set
  mysqli_set_charset($oConnection, "utf8");


  // we build the call to the stored procedure
  $sCallStr = "CALL ";

  $sCallStr =  $sCallStr.$sProcName."(";

  // we add the IN parameters
  foreach($aInParam AS $sParam)
  {
    $sCallStr =  $sCallStr."?,";
  }

  // we add the OUT parameters (since we can't access directly the out parameters, we store their value in variables @ on the DB) we will access theme at the end
  for($iIndex=0; $iIndex<$iNumberParameterOut; $iIndex++)
  {
    $sSelectOutParam = $sSelectOutParam."@outParam".$iIndex.",";
  }

  // we remove the unwanted coma
  if($iNumberParameterOut > 0)
  {
    $sSelectOutParam = rtrim($sSelectOutParam, ",");
  }
  else
  {
    $sCallStr =  rtrim($sCallStr, ",");
  }

  // we merge the two parameters string and add the final parenthesis
  $sCallStr = $sCallStr.$sSelectOutParam.")";

  // we prepare the call
  $oMysqlCall = mysqli_prepare($oConnection, $sCallStr);

  // only if we have parameters we bind the call with the types and the list of IN parameters. If no parameters is waited by the stored procedure we don't need to bind
  if(count($aInParam) > 0)
  {
    mysqli_stmt_bind_param($oMysqlCall, $sInParamTypes, ...$aInParam);
  }

  // we execute the stored procedure
  mysqli_stmt_execute($oMysqlCall);

  // we get the data that would be returned by select in the procedure
  while ($oResult = mysqli_stmt_get_result($oMysqlCall)) 
  {
    if (mysqli_num_rows($oResult) > 0) 
    {
      while ($oRow = mysqli_fetch_assoc($oResult)) 
      {
        foreach($oRow as $key => $val) 
        {
          $aRows[$key] = $val;
        }
        $aReturn[] = $aRows;
        $aRows = array();
      }
    }

    // we free the return variable
    mysqli_free_result($oResult);  

    // we get the next return
    mysqli_stmt_next_result($oMysqlCall);
  }

  // we have to close the call to get the out parameters
  mysqli_stmt_close($oMysqlCall);

  if($iNumberParameterOut > 0)
  {
    // to get the out parameters stored in variables we have to make a select on on the DB
    $oResult = mysqli_query($oConnection, "SELECT ".$sSelectOutParam.";");

    // for every variable we create a row and add to an array
    for($iIndex=0; $iIndex<$iNumberParameterOut; $iIndex++)
    {
      $iReturnIndex = 0;
      while($oRow = mysqli_fetch_assoc($oResult))
      {
        if ($oRow===false) $oRow = array();
        $aRows[key($oRow)] = $oRow[key($oRow)];
        $iReturnIndex++;
      }
      if ($oRow===false && $iReturnIndex==0)
      {
        $aRows[0] = array();
      }
    }
    // stored in the out key of our main return variable
    $aReturn["out"] = $aRows;
  }

  // we free the rest 
  mysqli_free_result($oResult);
  mysqli_close($oConnection);

  error_reporting($iLevel);

  // and return the data
  return $aReturn;
}

}

Hope this help someone. Feel free to comment or ask if you don't understand something