Jump to content

[SOLVED] Using Mysql stored procedure from PHP


Barand

Recommended Posts

I've at last got around to upgrading to MySql 5, stored procedures at last!.

Having created and tested a simple SELECT procedure using MySql's Query Browser tool I'm now trying to call it from php.

I've tried
[code]$res = mysql_query ("call getBaaGridData('S')") or die (mysql_error());[/code]

which gives error "PROCEDURE test.getBaaGridData can't return a result set in the given context"

Anyone know how to get a result set back from a MySql stored procedure?

The procedure definition
[code]DELIMITER $$

DROP PROCEDURE IF EXISTS `test`.`getBaaGridData`$$
CREATE PROCEDURE `getBaaGridData`(IN parea varchar(5))
BEGIN
    SELECT * FROM baagriddata
    WHERE area = parea;
END$$

DELIMITER ;[/code]
Link to comment
Share on other sites

nm. Looks like PHP4 users are out of luck, but MySqli users can do it

[code]<?php
$db = new mysqli("localhost","","","test");
$db->multi_query ("CALL getBaaGridData('S')");
$res = $db->store_result();
echo '<pre>';
while ($row = $res->fetch_row()) {
    vprintf ('%-12s%-20s%6d%12.2f%3s%5d<br>',$row);
}
echo '</pre>';
?>[/code]

However, v4 users are able to use functions and stored procedures with input and output parameters. Just stored procedures that grab a result set seem to be a no-no.
Link to comment
Share on other sites

[quote author=Barand link=topic=105741.msg423465#msg423465 date=1156787782]
However, v4 users are able to use functions and stored procedures with input and output parameters. Just [b]stored procedures that grab a result set seem to be a no-no.[/b]
[/quote]

Well, that just plain sucks.  I'd love to move all the sql stuff out of my classes and into the db where it belongs.  :/

Link to comment
Share on other sites

  • 11 months later...
HOw could I call the procedure without PHP5?  here is the procedure:

DELIMITER //

CREATE PROCEDURE get_partner_link_id_old (IN shopper_link_code VARCHAR(255),OUT partner_code VARCHAR(255), OUT old_partner_id VARCHAR(255))

BEGIN
DECLARE OLD_ID, P_CODE VARCHAR(255);
DECLARE empty INT;
DECLARE pl_id_cur CURSOR FOR SELECT partner_link_id from shopper_link where code = 'shopper_link_code';

DECLARE p_code_cur CURSOR FOR SELECT partner_code,type from partner_link where partner_link_id = 'old_partner_id';

DECLARE CONTINUE HANDLER FOR NOT FOUND
SET empty = 1;

SET empty = 0;
OPEN pl_id_cur;

FETCH pl_id_cur INTO OLD_ID;
IF empty = 0 THEN
CLOSE pl_id_cur;

SET old_partner_id = OLD_ID;

OPEN p_code_cur;
FETCH p_code_cur INTO P_CODE;
CLOSE p_code_cur;
SET partner_code = P_CODE;
END IF;


END;//

DELIMITER ;//
Link to comment
Share on other sites

Found it. This was the code I was testing with

SP
[code]
DELIMITER $$

DROP PROCEDURE IF EXISTS `test`.`getAreaSales`$$
CREATE PROCEDURE `getAreaSales`(
  OUT n float,
  OUT s float,
  OUT e float,
  OUT w float
  )
BEGIN
  SET n = (SELECT SUM(value) FROM baagriddata WHERE area = 'N');
  SET s = (SELECT SUM(value) FROM baagriddata WHERE area = 'S');
  SET e = (SELECT SUM(value) FROM baagriddata WHERE area = 'W');
  SET w = (SELECT SUM(value) FROM baagriddata WHERE area = 'E');

END$$

DELIMITER ;
[/code]

PHP
[code]<?php
$res = mysql_query ("CALL getAreaSales(@N,@S,@E,@W)") or die(mysql_error());
$res = mysql_query("SELECT @N as North, @S as South, @E as East, @W as West") or die(mysql_error());
$row = mysql_fetch_assoc($res);
echo '<pre>', print_r($row, true), '</pre>';
?>
[/code]
Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.