Reading OUT parameter from MySQL Stored Procedure with PHP and mysqli

Hello,

I am having a problem reading and OUT parameter from an MySQL Stored Procedure with PHP and mysqli.

Below is the Stored Procedure:
CREATE PROCEDURE `sp_session_information`(IN _session_id CHAR(20), OUT _session_title CHAR(255))
BEGIN

	SELECT CONCAT(c.client_name, ' <em>', m.session_time, '</em>') INTO _session_title
	FROM session_main m
	INNER JOIN session_planning p ON m.session_id = p.session_id
	LEFT JOIN client_records c ON c.client_id = p.client_id
	WHERE m.session_id = _session_id;

END

Open in new window


I have tested within MySQL and it works using the following:

CALL sp_session_information('d41d8cd53c847eaa3d4e',@title);
SELECT @title;

Open in new window


Then in PHP I have the following:

$conn = dbconn();
    
$query = $conn->query("CALL sp_session_information ('" . $event_id . "'@out);");
    
$results = $conn->query('select @out') or die("Error in the consult.." . mysqli_error($conn));
	
$row = $results->fetch_assoc();
	
$title = $row['@out'];
	
echo $title;

Open in new window


I know the dbconn() works as I have used it in other places, it is just getting the OUT parameter value to return into PHP that I am stuck on.

Cheers,

Lee
Lee RedheadManaging DirectorAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Ray PaseurCommented:
$conn = dbconn();

Open in new window

dbconn() is not a native PHP function.   Please post the function source code, thanks.
0
Lee RedheadManaging DirectorAuthor Commented:
Sure thing, it is just my connection string that is in a separate file that I use across the site.

function dbconn() {
	//MySql Connection String

	// DEV and LOCAL
	$conn = mysqli_connect("localhost","dbuser","dbpass","db") or die("Error " . mysqli_error($conn));
	
	return $conn;
}

Open in new window


I know this works as it is used quite extensively across the site.
0
Ray PaseurCommented:
Have a read of the man page here.  You can do this, but it might be easier to omit the OUT parameters and just use the results set!
http://php.net/manual/en/mysqli.quickstart.stored-procedures.php

See especially lines 83-97,
<?php // demo/temp_leeredhead.php
ini_set('display_errors', TRUE);
error_reporting(E_ALL);
echo '<pre>';


// THIS SCRIPT DEMONSTRATES STORED PROCEDURE BASICS OF MySQLi


// THE ABSOLUTE MINIMUM YOU MUST UNDERSTAND TO USE PHP AND MYSQLI
// MAN PAGE: http://php.net/manual/en/mysqli.overview.php
// MAN PAGE: http://php.net/manual/en/class.mysqli.php
// MAN PAGE: http://php.net/manual/en/class.mysqli-stmt.php
// MAN PAGE: http://php.net/manual/en/class.mysqli-result.php
// MAN PAGE: http://php.net/manual/en/class.mysqli-warning.php
// MAN PAGE: http://php.net/manual/en/class.mysqli-sql-exception.php <-- DID NOT WORK PHP 5.3+, MySQL 5.1+
// MAN PAGE: http://php.net/manual/en/mysqli.construct.php
// MAN PAGE: http://php.net/manual/en/mysqli.real-escape-string.php
// MAN PAGE: http://php.net/manual/en/mysqli.query.php
// MAN PAGE: http://php.net/manual/en/mysqli.errno.php
// MAN PAGE: http://php.net/manual/en/mysqli.error.php
// MAN PAGE: http://php.net/manual/en/mysqli.insert-id.php
// MAN PAGE: http://php.net/manual/en/mysqli-result.num-rows.php
// MAN PAGE: http://php.net/manual/en/mysqli-result.fetch-object.php
// MAN PAGE: http://php.net/manual/en/mysqli.quickstart.stored-procedures.php

// MAN PAGE http://dev.mysql.com/doc/refman/5.7/en/create-procedure.html
// MAN PAGE http://dev.mysql.com/doc/refman/5.7/en/drop-procedure.html


// CREATE AN ARRAY OF NAMES TO USE FOR TEST DATA
$test_names_arrays = array
( array( "fname" => "Walter" , "lname" => "Williams" )
, array( "fname" => "Ray"    , "lname" => "Paseur"   )
, array( "fname" => "Bill"   , "lname" => "O'Reilly" )
, array( "fname" => "Ray"    , "lname" => "Capece"   )
, array( "fname" => "John"   , "lname" => "Paseur"   )
)
;


// DATABASE CONNECTION AND SELECTION VARIABLES - GET THESE FROM YOUR HOSTING COMPANY
$db_host = "localhost"; // PROBABLY THIS IS OK
$db_name = "??";
$db_user = "??";
$db_word = "??";

// OPEN A CONNECTION TO THE DATA BASE SERVER AND SELECT THE DB
$mysqli = new mysqli($db_host, $db_user, $db_word, $db_name);

// DID THE CONNECT/SELECT WORK OR FAIL?
if ($mysqli->connect_errno)
{
    $err
    = "CONNECT FAIL: "
    . $mysqli->connect_errno
    . ' '
    . $mysqli->connect_error
    ;
    trigger_error($err, E_USER_ERROR);
}

// ACTIVATE THIS TO SHOW WHAT THE DB CONNECTION OBJECT LOOKS LIKE
// var_dump($mysqli);


// REMOVING AN OLD VERSION OF THE PROCEDURE
$sql = "DROP PROCEDURE find_by_first_name";
if (!$res = $mysqli->query($sql))
{
    $err
    = 'QUERY FAILURE:'
    . ' ERRNO: '
    . $mysqli->errno
    . ' ERROR: '
    . $mysqli->error
    . ' QUERY: '
    . $sql
    ;
    trigger_error($err, E_USER_NOTICE);
}

// CREATING A NEW PROCEDURE
$sql
=
"
CREATE
DEFINER = CURRENT_USER
PROCEDURE find_by_first_name
( IN  p_first_name CHAR(24) )
BEGIN
SELECT id, xwhen, CONCAT(fname, ' ', lname) AS full_name
FROM my_table
WHERE fname = p_first_name;
END
"
;
if (!$res = $mysqli->query($sql))
{
    $err
    = 'QUERY FAILURE:'
    . ' ERRNO: '
    . $mysqli->errno
    . ' ERROR: '
    . $mysqli->error
    . ' QUERY: '
    . $sql
    ;
    trigger_error($err, E_USER_ERROR);
}


// CREATING A TABLE FOR OUR TEST DATA
$sql
=
"
CREATE TEMPORARY TABLE my_table
( id    INT         NOT NULL AUTO_INCREMENT PRIMARY KEY
, fname VARCHAR(24) NOT NULL DEFAULT ''
, lname VARCHAR(24) NOT NULL DEFAULT ''
, xwhen TIMESTAMP   NOT NULL
)
"
;
if (!$res = $mysqli->query($sql))
{
    $err
    = 'QUERY FAILURE:'
    . ' ERRNO: '
    . $mysqli->errno
    . ' ERROR: '
    . $mysqli->error
    . ' QUERY: '
    . $sql
    ;
    trigger_error($err, E_USER_ERROR);
}

// SHOW THE RESULTS OF THE QUERY
var_dump($res);


// LOADING OUR DATA INTO THE TABLE
foreach ($test_names_arrays as $person)
{
    // ESCAPE THE DATA FOR SAFE USE IN A QUERY
    $safe_fn  = $mysqli->real_escape_string($person['fname']);
    $safe_ln  = $mysqli->real_escape_string($person['lname']);

    // CONSTRUCT THE QUERY USING THE ESCAPED VARIABLES
    $sql = "INSERT INTO my_table ( fname, lname ) VALUES ( '$safe_fn', '$safe_ln' )";

    // RUN THE QUERY TO INSERT THE ROW
    $res = $mysqli->query($sql);

    // IF mysqli::query() RETURNS FALSE, LOG AND SHOW THE ERROR
    if (!$res)
    {
        $err
        = 'QUERY FAILURE:'
        . ' ERRNO: '
        . $mysqli->errno
        . ' ERROR: '
        . $mysqli->error
        . ' QUERY: '
        . $sql
        ;
        trigger_error($err, E_USER_ERROR);
    }


    // GET THE AUTO_INCREMENT ID OF THE RECORD JUST INSERTED
    $id  = $mysqli->insert_id;
    echo "MySQLI INSERTED A ROW CONTAINING <b>$safe_fn $safe_ln</b> WITH AUTO_INCREMENT ID = $id" . PHP_EOL;
}
echo PHP_EOL;


// SHOW THE STORED PROCEDURE WE ARE ABOUT TO USE
$sql = "SHOW CREATE PROCEDURE find_by_First_Name";
$res = $mysqli->query($sql);// IF mysqli_query() RETURNS FALSE, LOG AND SHOW THE ERROR
if (!$res)
{
    $err
    = 'QUERY FAILURE:'
    . ' ERRNO: '
    . $mysqli->errno
    . ' ERROR: '
    . $mysqli->error
    . ' QUERY: '
    . $sql
    ;
    trigger_error($err, E_USER_ERROR);
}
while ($row = $res->fetch_object()) { print_r($row); }


// ESCAPE SOME DATA FOR SAFE USE IN A QUERY
$fname    = "RAY";
$safe_fn  = $mysqli->real_escape_string($fname);


// RUN THE QUERY WITH A STORED PROCEDURE
$sql = "CALL find_by_first_name('$fname')";
$res = $mysqli->query($sql);
if (!$res)
{
    $err
    = 'QUERY FAILURE:'
    . ' ERRNO: '
    . $mysqli->errno
    . ' ERROR: '
    . $mysqli->error
    . ' QUERY: '
    . $sql
    ;
    trigger_error($err, E_USER_ERROR);
}


// DETERMINE HOW MANY ROWS OF RESULTS WE GOT
$num     = $res->num_rows;
$num_fmt = number_format($num);
if (!$num)
{
    echo "<br/>QUERY: $sql ";
    echo "<br/>FOUND NO DATA ";
    echo PHP_EOL;
}
else
{
    echo "<br/>QUERY: $sql ";
    echo "<br/>FOUND $num_fmt ROWS OF DATA ";
    echo PHP_EOL;
}
echo PHP_EOL;

// ITERATE OVER THE RESULTS SET AS AN OBJECT TO SHOW WHAT WE FOUND
echo PHP_EOL . 'USING MySQLi_Result::Fetch_<i>Object</i>(): ';
echo PHP_EOL;
while ($row = $res->fetch_object())
{
    // ROW BY ROW PROCESSING IS DONE HERE
    print_r($row);
    echo PHP_EOL;
}
echo PHP_EOL;

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Cloud Class® Course: Microsoft Office 2010

This course will introduce you to the interfaces and features of Microsoft Office 2010 Word, Excel, PowerPoint, Outlook, and Access. You will learn about the features that are shared between all products in the Office suite, as well as the new features that are product specific.

GaryCommented:
In your php code you are missing a comma before @out

$query = $conn->query("CALL sp_session_information ('" . $event_id . "',@out);");

Open in new window

0
Lee RedheadManaging DirectorAuthor Commented:
It turns out with PHP and MySQL it is far easier to just have a results set and read the individual columns for the data required that to have a parametrised dataset.

This was just a matter of a minor adjustment to the code and creation of a temporary table to put the data on to return the values required.
0
Ray PaseurCommented:
Thanks for the points - it's a great question, ~Ray
0
Lee RedheadManaging DirectorAuthor Commented:
It is another part of the learning from .NET to PHP, some things seem easier others far more complicated. Cheers for the advice again.
0
Ray PaseurCommented:
Yeah, I get it.  If you want a good reference book, this one is very helpful.  Just get the latest version available and when a new one comes out, throw the old one away and get the new one.
http://www.amazon.com/PHP-MySQL-Web-Development-4th/dp/0672329166/
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
PHP

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.