PHP + MySQL + Stored Procedures, How to Get Access an "Out" Value

PHP + MySql + Stored Procedures, how do I get access an out value?

it looks like it's answered in this post:

http://forums.mysql.com/read.php?52,198596,198717#msg-198717

With mysqli PHP API:

Assume sproc myproc( IN i int, OUT j int ):

$mysqli = new mysqli(  "HOST", "USR", "PWD", "DBNAME" );
$ivalue=1;
$res = $mysqli->multi_query( "CALL myproc($ivalue,@x);SELECT @x" );
if( $res ) {
  $results = 0;
  do {
    if ($result = $mysqli->store_result()) {
      printf( "<b>Result #%u</b>:<br/>", ++$results );
      while( $row = $result->fetch_row() ) {
        foreach( $row as $cell ) echo $cell, " ";
      }
      $result->close();
      if( $mysqli->more_results() ) echo "<br/>";
    }
  } while( $mysqli->next_result() );
}
$mysqli->close();

PHP - MySQL gets value of out parameter from a stored procedure

Or even just do a "SELECT @id AS id" then $row->id will work fine. I always rename select columns to keep the name meaningful when necessary :-)

BTW, you can simply concatenate the call and select @... (with a ; statement delimiter) and the RS will be the returned value. Unfortunately this returns a mutli-resultset and you need to flush the full set otherwise the subsequent queries will stall. See following examples:

$db->multi_query( "CALL addNewUser($name,$age,@id);SELECT @id as id" );
$db->next_result();            // flush the null RS from the call
$rs=$db->store_result();       // get the RS containing the id
echo $rs->fetch_object()->id, "\n";
$rs->free();

Alternatively add the select into the addNewUser and return a RS instead of out param

$rs = $db->query( "CALL addNewUser($name,$age)" );
echo $rs->fetch_object()->id, "\n";
$rs->close();
$db->next_result();            // flush the null RS from the call

The first returns a multiquery (NULL, RS) set and the second a (RS, NULL) set, hence you can use a simple query() call which embeds the first fetch_object(), but you still need to flush the RS stack.

Accessing Returned value from MySQL Stored Procedure in PHP

Have been able to solve it. And this is how I did it.

CREATE PROCEDURE `shopping_cart_create_order2`(IN `inCartId` INT(11), OUT `newOrderId` INT(11)) BEGIN
    DECLARE newOrder int;
    -- Insert a new record into orders and obtain the new order ID
    INSERT INTO orders (created_on) VALUES (NOW());

    -- Obtain the new Order ID
    SELECT LAST_INSERT_ID() INTO newOrder;

    -- Insert order details in order_detail table
    INSERT INTO order_detail (
        order_id, product_id, attributes, product_name, quantity, unit_cost
    )
    SELECT
        newOrder,
        p.id,
        sc.attributes,
        p.name,
        sc.quantity,
        COALESCE( NULLIF( p.discounted_price, 0 ), p.price ) AS unit_cost
    FROM
        shopping_cart sc
        INNER JOIN products p ON sc.product_id = p.id
    WHERE
        sc.cart_id = inCartId
        AND
        sc.buy_now;

    -- Save the order's total amount
    UPDATE
     orders
    SET
        total_amount = (
            SELECT
                SUM( unit_cost * quantity )
            FROM
                order_detail
            WHERE
                order_id = newOrder
        )
    WHERE
        id = newOrder;

    -- Clear the shopping cart
    CALL shopping_cart_empty(inCartId);
    SET newOrder = newOrderId;
END

At PHP level// Probably at the Model/Entity level
First, we need to execute the

shopping_cart_create_order2()

stored procedure. Which might probably be in a function.

Second, to get the last order id, we need to query it from the variable

@oid

. It is important that we must call the method

closeCursor()

of the PDOStatement object in order to execute the next SQL statement.

function query($pdo, $sql, $parameters = []){
    $query = $pdo->prepare($sql);
    $query->execute($parameters);
    return $query;
}

function create_order($pdo, $cart_id){
    // Binding the parameters
    $parameters = [':cart_id' => $cart_id];

    // calling stored procedure command
    $sql = 'CALL shopping_cart_create_order2(:cart_id)';

    // prepare for execution of the stored procedure, pass value to the command 
    and execute the Stored Procedure
    $query = query($pdo, $sql, $parameters);

   // Then close Cursor. It is important for you to close it.
    $query->closeCursor();

   // execute the second query to get last insert id
    $row = $pdo->query("SELECT @oid AS oid")->fetch();
    return $row;    
}

For more info, see enter link description here

I hope am able to help someone out there who might probably fall into what I passed through.

Calling stored procedures with an OUT parameter in PHP

As an example to illustrate it, I will a real world practical example(inserting data into an order table and returning the lastInsertId).

CREATE PROCEDURE `shopping_cart_create_order`(IN `inCartId` INT(11), OUT `newOrderId` INT(11)) BEGIN
    DECLARE newOrder int;
    -- Insert a new record into orders and obtain the new order ID
    INSERT INTO orders (created_on) VALUES (NOW());

    -- Obtain the new Order ID
    SELECT LAST_INSERT_ID() INTO newOrder;

    SET newOrder = newOrderId;
END

At PHP level// Probably at the Model/Entity level First, we need to execute the

shopping_cart_create_order()

stored procedure. Which might probably be in a function.

Second, to get the last order id, we need to query it from the variable

@oid

. It is important that we must call the method

closeCursor()

of the PDOStatement object in order to execute the next SQL statement.

function query($pdo, $sql, $parameters = []){
    $query = $pdo->prepare($sql);
    $query->execute($parameters);
    return $query;
}

function create_order($pdo, $cart_id){
    // Binding the parameters
    $parameters = [':cart_id' => $cart_id];

    // calling stored procedure command
    $sql = 'CALL shopping_cart_create_order(:cart_id)';

    // prepare for execution of the stored procedure, pass value to the command 
    and execute the Stored Procedure
    $query = query($pdo, $sql, $parameters);

   // Then close Cursor. It is important for you to close it.
    $query->closeCursor();

   // execute the second query to get last insert id
    $row = $pdo->query("SELECT @oid AS oid")->fetch();
    return $row;    
}

How to retrieve out parameter from mysqli stored procedure in php?

You do not use your output parameter. You can set its value, like this:

set @_rply = 'foobar';

You can use it, like this:

$db->multi_query( "CALL insertUser($name,$username,$password,$email,$cellNumber,$estateName,@_rply);SELECT @_rply as _rply" );
$db->next_result();            // flush the null RS from the call
$rs=$db->store_result();       // get the RS containing the id
echo $rs->fetch_object()->_rply, "\n";
$rs->free();

Calling stored procedure with Out parameter using PDO

It would seem that there is a bug at work here, best solution I've found is this:

http://www.php.net/manual/en/pdo.prepared-statements.php#101993

From the comment at the link above:

$dbh->query("CALL SomeStoredProcedure($someInParameter1, $someInParameter2, @someOutParameter)"); 
$dbh->query("SELECT @someOutParameter");

// OR, if you want very much to use PDO.Prepare(),
// insert "SELECT @someOutParameter" in your stored procedure and then use:

$stmt = $dbh->prepare("CALL SomeStoredProcedure(?, ?)"); 
$stmt ->execute(array($someInParameter1, $someInParameter2));

See also this: https://stackoverflow.com/a/4502524/815386

---

Related Topics