Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2046
  • Last Modified:

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
0
Lee Redhead
Asked:
Lee Redhead
  • 4
  • 3
1 Solution
 
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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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

Featured Post

Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now