Solved

Reading OUT parameter from MySQL Stored Procedure with PHP and mysqli

Posted on 2014-07-17
8
1,543 Views
Last Modified: 2014-08-28
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
0
Comment
Question by:Lee Redhead
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
8 Comments
 
LVL 110

Expert Comment

by:Ray Paseur
ID: 40203491
$conn = dbconn();

Open in new window

dbconn() is not a native PHP function.   Please post the function source code, thanks.
0
 

Author Comment

by:Lee Redhead
ID: 40204162
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
 
LVL 110

Accepted Solution

by:
Ray Paseur earned 500 total points
ID: 40204296
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
Why Off-Site Backups Are The Only Way To Go

You are probably backing up your data—but how and where? Ransomware is on the rise and there are variants that specifically target backups. Read on to discover why off-site is the way to go.

 
LVL 58

Expert Comment

by:Gary
ID: 40206622
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
 

Author Closing Comment

by:Lee Redhead
ID: 40290643
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
 
LVL 110

Expert Comment

by:Ray Paseur
ID: 40290683
Thanks for the points - it's a great question, ~Ray
0
 

Author Comment

by:Lee Redhead
ID: 40290688
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
 
LVL 110

Expert Comment

by:Ray Paseur
ID: 40290809
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

Featured Post

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article shows the steps required to install WordPress on Azure. Web Apps, Mobile Apps, API Apps, or Functions, in Azure all these run in an App Service plan. WordPress is no exception and requires an App Service Plan and Database to install
In this series, we will discuss common questions received as a database Solutions Engineer at Percona. In this role, we speak with a wide array of MySQL and MongoDB users responsible for both extremely large and complex environments to smaller singl…
The viewer will learn how to count occurrences of each item in an array.
This tutorial will teach you the core code needed to finalize the addition of a watermark to your image. The viewer will use a small PHP class to learn and create a watermark.

690 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question