MySQL extracts a variable from a stored procedure in PHP PDO

Question

I saw that this question has been asked many times, but they are all very long, and I just can’t understand what they are doing ... So can someone tell me how to get LAST_INSERT_ID()from this procedure in php using PDO:

Table:

CREATE TABLE names (
    ID INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    name varchar(50) NOT NULL
)

Procedure:

CREATE DEFINER=`root`@`localhost` PROCEDURE `simpleProcedure`(newname varchar(50), OUT returnid INT(11))
BEGIN
    INSERT INTO names (name) VALUES (newname);
    SET returnid = LAST_INSERT_ID();
END

PHP code I tried:

$stmt=$db->prepare("CALL simpleProcedure(:name,:returnid)");
$stmt->bindValue(':name',$name,PDO::PARAM_STR);
$stmt->bindParam(':returnid',$returnid,PDO::PARAM_INT,11);
$stmt->execute();
echo $returnid;

But it is probably obvious for someone who has more brain cells than mine, this does not work. Any help was appreciated.

Link to the question why I believe that SHOULD work:

http://www.php.net/pdo.prepared-statements (example # 4)

Answer 1

, , ... 2005 !

: 2005 2013 . : 2013 .

, ...

"" , "mysql". "" .

• - , , MYSQL .

• mysql.

: php-calling-mysql-stored-procedures

( 2017):

, "IN", "INOUT" "OUT" Mysql.

, :

• : PDO , .
• PHP IN.

INOUT OUT .

, , ; -/

(XAMPP):

• PHP: 5.4.4
• Mysql: 5.5.16

:

• SQL
• PHP

SQL:

CREATE PROCEDURE `demoSpInOutSqlVars`(IN     pInput_Param  INT, /* PHP Variable will bind to this*/   
                                      /* --- */  
                                      INOUT  pInOut_Param  INT, /* contains name of the SQL User variable that will be read and set by mysql */
                                      OUT    pOut_Param    INT) /* contains name of the SQL User variable that will be set by mysql */
BEGIN
    /*
     * Pass the full names of SQL User Variable for these parameters. e.g. '@varInOutParam'
     * These 'SQL user variables names' are the variables that Mysql will use for:
     *    1) finding values
     *    2) storing results
     *
     * It is similar to 'variable variables' in PHP.  
     */
     SET pInOut_Param      := ABS(pInput_Param) + ABS(pInOut_Param); /* always positive sum  */
     SET pOut_Param        := ABS(pInput_Param) * -3;                /* always negative * 3  */ 
END$$

PHP:

:

$db = appDIC('getDbConnection', 'default'); // get the default db connection
$db->setAttribute(PDO::ATTR_EMULATE_PREPARES, true);    
$db->setAttribute(PDO::ATTR_DEFAULT_FETCH_MODE, PDO::FETCH_ASSOC);

. EMULATE_PREPARES= false.

PHP, :

$phpInParam     = 5;                  
$phpInOutParam  = 404;          /* PHP InOut variable  ==> read and should be changed  */
$phpOutParam    = null;         /* PHP Out   variable  ==> should be changed           */

SQL:

$sql = "call demoSpInOut(:phpInParam, 
                         @varInOutParam, /* mysql variable name will be read and updated */
                         @varOutParam)"; /* mysql variable name that will be written to  */

$stmt = $db->prepare($sql);

PHP SQL:

• 1) PHP

  $stmt- > bindParam (': phpInParam', $phpInParam, PDO:: PARAM_INT);

• 2) SQL INOUT INOUT

  $db- > exec ( "SET @varInOutParam = $phpInOutParam" );// , .

:

$allOk = $stmt->execute();

SQL PHP:

$sql = "SELECT @varInOutParam AS phpInOutParam,
               @varOutParam   AS phpOutParam
        FROM dual";
$results = current($db->query($sql)->fetchAll());

$phpInOutParam = $results['phpInOutParam'];
$phpOutParam   = $results['phpOutParam'];

: , : -/

PHP

"$phpInParam:"     => "5"
"$phpInOutParam:"  => "409"
"$phpOutParam:"    => "-15"

Answer 2

, , ...

val,

$status = $statement->execute(  );

$resultArray = $statement->fetchAll( );
$statement->closeCursor( );
if (!is_array($resultArray)) {
    return array();
}
return $resultArray[0]['returnid'];

...