MySQL Forums
Forum List  »  PHP

Query return from a Stored Procedure
Posted by: Mike Barlow
Date: September 26, 2012 01:41PM

I've been using MySQL on my dev environment for sometime now, but I'm just getting into using mySql Stored Procedures with PHP and PDO and have come across what is probably a newbe issue, but I can't seem to find a solution.

I've got a simple database single table (SQL statements reduced from my actual system for simplicity):

CREATE TABLE `patients` (
`patName` varchar(50) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1

This table has a single record in it:
patName = "My First Name"

I've created a simple Stored Procedure as a starting point in my efforts:
CREATE DEFINER=`root`@`localhost` PROCEDURE `get_patients`()
BEGIN
SELECT patName from patients;
END

And I have a simple PHP script to call that stored proc via PDO:
$DBH = new PDO("mysql:host=$host;dbname=$dbname", $user, $pass);
$DBH->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
foreach($DBH->query( 'CALL get_patients()' ) as $row) {
var_dump($row);

echo "<br><br>JSON Dump<br>";
echo json_encode($row, JSON_FORCE_OBJECT);
}

The output from this PHP code is:
array
'patName' => string 'My First Name' (length=19)
0 => string 'My First Name' (length=19)

JSON Dump
{"patName":"My First Name","0":"My First Name"}


Ok now here's my question/problem.

Notice that in both the var_dump and the JSON Dump there are 2 elements being returned ("patName" and "0") both of which have the same value.

How do I get only the one element ({"patName":"My First Name"}) to be returned???

Am I doing something wrong (or more likely missing something) in my stored proc or in my PDO processing?

I know I could probably kludge something up in PHP but I'm hoping that I'm missing something simple and obvious as a soltion.

Signed "Puzzled, Puzzled"
Thanks
MikeB

Options: ReplyQuote


Subject
Written By
Posted
Query return from a Stored Procedure
September 26, 2012 01:41PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.