Solved

Reading OUT parameter from MySQL Stored Procedure with PHP and mysqli

Posted on 2014-07-17
8
1,187 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
  • 4
  • 3
8 Comments
 
LVL 108

Expert Comment

by:Ray Paseur
Comment Utility
$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
Comment Utility
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 108

Accepted Solution

by:
Ray Paseur earned 500 total points
Comment Utility
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
 
LVL 58

Expert Comment

by:Gary
Comment Utility
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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 

Author Closing Comment

by:Lee Redhead
Comment Utility
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 108

Expert Comment

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

Author Comment

by:Lee Redhead
Comment Utility
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 108

Expert Comment

by:Ray Paseur
Comment Utility
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

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

Author Note: Since this E-E article was originally written, years ago, formal testing has come into common use in the world of PHP.  PHPUnit (http://en.wikipedia.org/wiki/PHPUnit) and similar technologies have enjoyed wide adoption, making it possib…
Developers of all skill levels should learn to use current best practices when developing websites. However many developers, new and old, fall into the trap of using deprecated features because this is what so many tutorials and books tell them to u…
Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions. Consider email address RFCs: Look at HTML5 form input element (with type=email) regex pattern: T…
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.

772 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now