MySQL Stored Procedure - parsing using PHP

Question

I have a stored procedure using MySQL which spits out two tables. For example:

DELIMITER //
CREATE PROCEDURE MyStoredProcedure(IN input VARCHAR(4))
SELECT * FROM BLAH;
SELECT * FROM MAH;
END //

How do I process this in PHP? Normally, I've just dealt with one table, so it would be something like this:

$INPUT = mysql_real_escape_string($_POST['input']);
$sql = "CALL MyStoredProcedure('{$INPUT}')";
$res = $db->query($sql);
foreach ($res as $row) { 
    echo "<td>{$row->Column1}</td>";
}

But now there are two tables. So how can I get these two tables via PHP?

possible duplicate of [Retrieving Multiple Result sets with stored procedure in php/mysqli](http://stackoverflow.com/questions/1683794/retrieving-multiple-result-sets-with-stored-procedure-in-php-mysqli) — eggyal, Nov 19 '12 at 15:09
why dont you use these seperately SELECT * FROM BLAH; SELECT * FROM MAH; — Shamis Shukoor, Nov 19 '12 at 15:11
Possible answer here: http://www.php.net/manual/en/pdostatement.bindparam.php#109791 — StasGrin, Nov 19 '12 at 15:14
To see what the output is, type print_r($res); (prefereably with
tags either side to make it more readable). I am suspecting that you have 2 nested arrays, so you may need 2 foreaches, one for $res[0] and one for $res[1]. — Lucas, Nov 19 '12 at 15:30
@CodeGuy: The accepted answer of the question to which I linked also explains how it can be done in PDO. — eggyal, Nov 19 '12 at 15:30

score 0 · Accepted Answer · answered Nov 19 '12 at 16:16

i can get two table with this method:

stored procedure

DELIMITER $$
DROP PROCEDURE IF EXISTS `get_procedure`$$

    CREATE PROCEDURE `get_procedure`()
    BEGIN
    SELECT group_concat('<tr><td>',`blah_id`,'</td><td>',`blah_title`,'</td><tr>')  as tbl1 ,
 group_concat('<tr><td>',`mah_id`,'</td><td>',`mah_title`,'</td><td>',`mah_info`,'</td><tr>')  as tbl2
    FROM `blah`,`mah`;
    END $$

    DELIMITER ;

for example tables :

CREATE TABLE IF NOT EXISTS `blah` (
  `blah_id` int(11) NOT NULL AUTO_INCREMENT,
  `blah_title` varchar(300) NOT NULL,
  PRIMARY KEY (`blah_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=5 ;

CREATE TABLE IF NOT EXISTS `mah` (
  `mah_id` int(11) NOT NULL AUTO_INCREMENT,
  `mah_title` varchar(300) NOT NULL,
  `mah_info` varchar(32) NOT NULL,
  PRIMARY KEY (`mah_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=3 ;

and after select concat you can explode with , rows .

i hope help to you

score 0 · Answer 2 · answered Nov 22 '12 at 08:14

If you want the result of both queries you cannot make them this way. What you are doing is one query, and after that, another one. The result of the procedure is the second query. If you need both of them in only one result you have to combine the SELECT's into only one, maybe using an union, so that you will have only one query result in your procedure having all the registers form both tables. Try something like this:

CREATE PROCEDURE MyStoredProcedure(IN input VARCHAR(4)) SELECT * FROM BLAH UNION SELECT * FROM MAH; END

Using union statement you have some restrictions like you can see in the following link: http://dev.mysql.com/doc/refman/4.1/en/union.html

You can use this if both tables has columns of the same data types. If not, you can do the following:

CREATE PROCEDURE MyStoredProcedure(IN input VARCHAR(4)) SELECT b.column1, b.column2, ... , m.column1, m.column2, ... FROM BLAH as b, MAH as m; END

Hope this helps!

MySQL Stored Procedure - parsing using PHP

2 Answers2