MySQL Stored Procedure - Analysis Using PHP

Question

I have a stored procedure using MySQL that spits out two tables. For instance:

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

How to do it in PHP? Usually, I just parsed 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?

Answer 1

I can get two tables using 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 ; 

e.g. 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 choosing concat you can explode using strings.

I hope to help you.

Answer 2

If you want to get the result of both queries, you cannot make them that way. What you do is one request, and after that another. The result of the procedure is the second request. If you need both of them in only one result, you should join SELECT into only one, possibly using a join, so that you will have only one query result in your procedure, in which all registers form both tables. Try something like this:

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

Using the union statement, you have some limitations, as you can see at the following link: http://dev.mysql.com/doc/refman/4.1/en/union.html

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

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

Hope this helps!