1

I am trying to create a MySQL PROCEDURE with multiple SELECT statements. In its current state, the PROCEDURE only returns the results from the first query, and not the second query.

The Code:

CREATE PROCEDURE `GETPRODUCT`( productid INT ) DETERMINISTIC READS SQL DATA

SELECT DISTINCT product.id, product.name, product.price FROM products WHERE products.id = productid;

SELECT DISTINCT a.id, a.date, a.pid, a.uid, a.review, b.username FROM reviews a JOIN users b ON a.uid = b.id WHERE a.pid = ? ORDER BY a.date DESC LIMIT 0, 11;

END$


CALL GETPRODUCT( 1 );

Bascially, the first query grabs the product information, and the second query is a JOIN query that grabs the reviews from the reviews table, and the user's name from the users table.

The PROCEDURE is successfully created, and I can successfully call the PROCEDURE, but only the results from the first PROCEDURE is returned. There is matching information in both tables. Is there any reason why this is not returning all of the information?

mcbeav
  • 11,893
  • 19
  • 54
  • 84
  • What library are you using in your PHP client application code (PDO, MySQLi, etc)? http://dev.mysql.com/doc/refman/5.1/en/stored-routines-syntax.html _Multiple SELECT statements generate multiple result sets, so the client must use a MySQL client library that supports multiple result sets. This means the client must use a client library from a version of MySQL at least as recent as 4.1._ – Michael Berkowski Jul 27 '14 at 21:09
  • For example in PDO: http://php.net/manual/en/pdostatement.nextrowset.php – Michael Berkowski Jul 27 '14 at 21:10
  • I'm using MySQLi, and and running on I believe version 5.1 – mcbeav Jul 27 '14 at 21:11
  • For MySQLi, see http://php.net/manual/en/mysqli.next-result.php - I have never actually used this myself... – Michael Berkowski Jul 27 '14 at 21:13
  • Ah, [here are some good examples](http://stackoverflow.com/questions/1683794/retrieving-multiple-result-sets-with-stored-procedure-in-php-mysqli) – Michael Berkowski Jul 27 '14 at 21:13
  • Thanks for the help, I tried searching, but could not find what I was looking for. At least not I can get the information I need. – mcbeav Jul 27 '14 at 21:24

0 Answers0