Returning the output parameter of a mysql stored procedure using PHP

Question

I'm planning on developing a CMS using PHP and MySQL that utilises MySQL stored procedures to perform prepared statement queries to my MySQL database. It's been a long time since I developed in PHP (back in the procedural days) so I'm going to try and implement this system using the new OOP structure of PHP. Before I do that, I need to get to grips with the simple issue of returning the output parameter of my MySQL stored procedure to a simple php page. This is simply a test so that I can get the syntax correct before developing my first php class so the initial code posted here is procedural.

First, there is my stored procedure:

    DELIMITER $$
    DROP PROCEDURE IF EXISTS `text_development`.`get_user`$$
    CREATE PROCEDURE  `text_development`.`get_user`
    (
    IN userId INT,
    OUT user_name VARCHAR(100)
    )
    BEGIN
    SELECT username
    INTO user_name
    FROM user
    WHERE user_id = userId;
    END $$
    DELIMITER ;

Then there is my two php files:

    <?php
    //db_config.php
    $mysqli_host = "localhost";
    $mysqli_user = "root"; //I'm not stupid enough to use this in development
    $mysqli_pass = "root"; //before anyone comments about the root usage
    $mysqli_db = "text_development";
    ?>


    <?php
    //index.php
    require('incl/db_config.php');
    $dbConnection = new Mysqli($mysqli_host, $mysqli_user, $mysqli_pass, $mysqli_db) or die(mysql_error());
    print '<h1>Stored Procedure Retrieval Test</h1>';
    $id = 1;
    $return = '';
    $result = $dbConnection->query( 'CALL get_user($id,$return)');
    print $result;
    ?>

My problem arises with the index.php page. It doesn't actually seem to return anything. When executing the stored procedure in PHPMyAdmin the username test is returned when I pass in the user id of 1 however nothing is returned to the screen when calling the function in php. I've also tried printing $return as well but this just returns an empty string (as I define in the code above). I've tried using this online tutorial but the solutions there do not seem to work :

http://www.joeyrivera.com/2009/using-mysql-stored-procedures-with-php-mysqlmysqlipdo/

Does anyone know what I'm doing wrong here and why the return parameter of this stored procedure is not being printed to the screen?

maybe just convert it to function and simply do the query SELECT get_user($id);? — Peter
– Peter, Commented Jan 27, 2013 at 21:41
How would I do that? I'm aware of how to do in PL/SQL but this is my first time using stored procedures in MySQL. — jezzipin
– jezzipin, Commented Jan 27, 2013 at 21:43

Itay Moav -Malimovka · Accepted Answer · 2013-01-27 22:49:06Z

2

Firstly, you refer to a tutorial on PDO, but you use Mysqli.

Second: convert:

$result = $dbConnection->query( 'CALL get_user($id,$return)');

to (notice the double quotes, and the @return`):

$result = $dbConnection->query( "CALL get_user({$id},@return)");

and then, later, do:

$result = $dbConnection->query( 'SELECT @return');

If the SP is a SELECT, then you do it they you wrote it.

Reason is, the SP does not return to PHP anything, it returns the value into the MySQL variable (@return)(scop is in MySQL), so you need to query this variable in a separate call. If it was a call to a simple select SP, then it would return values as any other select statement.

edited Jan 27, 2013 at 22:49

answered Jan 27, 2013 at 21:44

Itay Moav -Malimovka

53.8k68 gold badges198 silver badges292 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

jezzipin Over a year ago

This produces the following error: Catchable fatal error: Object of class mysqli_result could not be converted to string in C:\xampp\htdocs\phptest\index.php on line 8

Itay Moav -Malimovka Over a year ago

Well, I do not know the exact implementation of your libs. What is line 8 and what is the vardump of the problematic variable. Man, u need to debug it...

jezzipin Over a year ago

You can see the implementation in my code above. It's also fairly obvious that it's the print statement where I print $result

Itay Moav -Malimovka Over a year ago

:-D well, than u should fix that too, the erro message seems clear to me than, and it is your error (Hint, the $result is not something u print, u need to extract the result from it). Try following a tutorial

jezzipin Over a year ago

Also I think you'll find that the tutorial I referred to looks at all means of connection from the depreciated mysql interface to mysqli to PDO. I have tried extracting the variable in a similar way as I would have done with a query in the old dats using mysqli_fetch_assoc but this does not work.

Collectives™ on Stack Overflow

Returning the output parameter of a mysql stored procedure using PHP

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related