Asked  2 Years ago    Answers:  5   Viewed   191 times

I've seen this question asked a load of times, but they're all really long, and I just can't get my head around what they're doing ... So, could someone tell me how to get the LAST_INSERT_ID() from this procedure into 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've 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, probably obvious to someone who has more brain cells than me, this doesn't work. Any help appreciated.

Reference as to why I believe this SHOULD work:

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

 Answers

2

It turns out that this is a bug that has been going on for a long time... since 2005!

Here is the original bug report: 2005 through to 2013. And here is the new bug report: From 2013 to the present.

There are various approaches to getting the answer returned, I found one of them and demonstrate it...

The 'trick' is that to get the output from a 'mysql' procedure. It is a 'two stage' process.

  • The first part is to run the procedure with your inputs, and also tell it what MYSQL variables to store the result in.

  • Then, you run a separate query to 'select' those 'mysql' variables.

It is described quite clearly here: php-calling-mysql-stored-procedures

Update (Jan 2017):

Here is an example showing the use of variables for 'IN', 'INOUT' and 'OUT' Mysql procedure parameters.

Before we start here are some tips:

  • When developing: Run PDO in 'emulates mode' as it is more reliable at determining errors in the procedure call.
  • Only bind PHP variables to the procedure 'IN' parameters.

You will get some really odd runtime errors when you try binding variables to INOUT and OUT parameters.

As usual I tend to provide rather more comments than are required ;-/

Runtime Environment (XAMPP):

  • PHP: 5.4.4
  • Mysql: 5.5.16

Source Code:

  • SQL Procedure
  • PHP with output

SQL Code:

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 Code:

DB Connection:

$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);

Note: The output is the same with EMULATE_PREPARES = false.

Set all PHP Variables that will be used:

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

Define and Prepare the SQL procedure call:

$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);

Bind PHP Variables and Set SQL Variables:

  • 1) bind the PHP variables

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

  • 2) Set the SQL User INOUT variables

    $db->exec("SET @varInOutParam = $phpInOutParam"); // This is safe as it just sets the value into the MySql variable.

Execute the procedure:

$allOk = $stmt->execute();

Get the SQL Variables into the PHP variables:

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

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

Note: maybe not the best way ;-/

Display the PHP variables

"$phpInParam:"     => "5"
"$phpInOutParam:"  => "409"
"$phpOutParam:"    => "-15"
Sunday, October 16, 2022
4

At the basic level the mysql, mysqli and PDO extensions all answer the question how do I talk to the database? They all provide functions and functionality to connect to a database and send and retrieve data from it. You can use them all at the same time establishing several connections to the database at once, but that's typically nonsense.

mysql* is a very simple extension that basically allows you to connect to the database, send it SQL queries and not much else.
mysqli improves this (as the name suggests) by adding parameterized queries and a few other things into the mix.
PDO is an extension that abstracts several database drivers into one package, i.e. it allows you to use the same code to connect to MySQL, Oracle, MS SQL Server and a number of other databases without needing to use database specific extensions or rewrite your code when you switch databases (in theory at least). It also supports parameterized queries.

If you know you're going to be using MySQL exclusively, mysqli is a good choice. Especially since you can use it in a procedural way, what you're already used to from the mysql extension. If you're not familiar with OOP, that's helpful. Otherwise, PDO is a nice object oriented, flexible database connector.


* Note that the mysql extension is now deprecated and will be removed sometime in the future. That's because it is ancient, full of bad practices and lacks some modern features. Don't use it to write new code.

Tuesday, November 1, 2022
 
kennyc
 
2

It seems like there's no good simple way to do this, without a hack or a major paradigm shift. It looks like the best way is to just split out the original procs and end up with one more proc than before:

Old way:

create procedure dbo.GetSomething
as
begin
    select * from dbo.Person;
    select * from dbo.Car;
end;

New way:

create procedure dbo.GetPeople
as
begin
    select * from dbo.Person;
end;

create procedure dbo.GetCars
as
begin
    select * from dbo.Car;
end;

-- This gives the same result as before
create procedure dbo.GetSomething
as
begin
    exec dbo.GetPeople;
    exec dbo.GetCars;
end;

Then when I'm in a different proc and need both result sets, I'd just have to call them one at a time.

Saturday, August 20, 2022
 
krm
 
krm
1

The way stored procedures work with prepared statements is a bit more complicated. PHP manual states that you've got to use session variables (MySQL sessions, not PHP)

INOUT/OUT parameter

The values of INOUT/OUT parameters are accessed using session variables.

So you could do it with

$connect=&ConnectDB();
// bind the first parameter to the session variable @uid
$stmt = $connect->prepare('SET @uid := ?');
$stmt->bind_param('s', $uid);
$stmt->execute();

// bind the second parameter to the session variable @userCount
$stmt = $connect->prepare('SET @userCount := ?');
$stmt->bind_param('i', $userCount);
$stmt->execute();

// execute the stored Procedure
$result = $connect->query('call IsUserPresent(@uid, @userCount)');

// getting the value of the OUT parameter
$r = $connect->query('SELECT @userCount as userCount');
$row = $r->fetch_assoc();               

$toRet = ($row['userCount'] != 0);

Remark:

I recommend to rewrite this procedure as a function with one IN parameter that returns INT.

Friday, November 18, 2022
 
gunan
 
5

Your PDO is configured to emulate prepared queries, whereas mysqli is using true prepared queries.

The prepared query binds the string ''1'' as an integer parameter value. PHP coerces it to an integer using something like intval(). Any string with non-numeric leading characters is interpreted as 0 by PHP, so the parameter value sent after prepare is the value 0.

The fake prepared query uses string interpolation (instead of binding) to add the string ''1'' into the SQL query before MySQL parses it. But the result is similar, because SQL also treats a string with non-numeric leading characters in an integer context as the value 0.

The only difference is what ends up in the general query log when the parameter is bound before prepare versus after prepare.

You can also make PDO use real prepared queries, so it should act just like mysqli in this case:

$dbh->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);

PS: This may demonstrate a good reason why it's customary to start id values at 1 instead of 0.

Monday, October 10, 2022
Only authorized users can answer the search term. Please sign in first, or register a free account.
Not the answer you're looking for? Browse other questions tagged :
 

Browse Other Code Languages