Jump to content


Photo

[SOLVED] Using Mysql stored procedure from PHP


  • Please log in to reply
5 replies to this topic

#1 Barand

Barand
  • Moderators
  • Sen . ( ile || sei )
  • 18,023 posts

Posted 26 August 2006 - 08:01 PM

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
$res = mysql_query ("call getBaaGridData('S')") or die (mysql_error());

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
DELIMITER $$

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

DELIMITER ;

If you are still using mysql_ functions, STOP! Use mysqli_ or PDO. The longer you leave it the more you will have to rewrite.

Donations gratefully received






moon.png

|baaGrid| easy data tables - and more
|baaChart| easy line, column and pie charts

#2 Barand

Barand
  • Moderators
  • Sen . ( ile || sei )
  • 18,023 posts

Posted 28 August 2006 - 05:56 PM

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

<?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>';
?>

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.
If you are still using mysql_ functions, STOP! Use mysqli_ or PDO. The longer you leave it the more you will have to rewrite.

Donations gratefully received






moon.png

|baaGrid| easy data tables - and more
|baaChart| easy line, column and pie charts

#3 jsimmons

jsimmons
  • Members
  • PipPip
  • Member
  • 16 posts

Posted 01 September 2006 - 11:39 AM

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.


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.  :/



#4 Barand

Barand
  • Moderators
  • Sen . ( ile || sei )
  • 18,023 posts

Posted 01 September 2006 - 07:31 PM

There may be a way - perhaps I'm just not casting the runes right or am uttering the wrong incantations. I've Googled and as is often the case, I can find lots pf people with the same problem but no actual solution yet.
If you are still using mysql_ functions, STOP! Use mysqli_ or PDO. The longer you leave it the more you will have to rewrite.

Donations gratefully received






moon.png

|baaGrid| easy data tables - and more
|baaChart| easy line, column and pie charts

#5 mwq27

mwq27
  • Members
  • PipPip
  • Member
  • 12 posts

Posted 09 August 2007 - 03:46 AM

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 ;//

#6 Barand

Barand
  • Moderators
  • Sen . ( ile || sei )
  • 18,023 posts

Posted 11 August 2007 - 06:17 PM

Found it. This was the code I was testing with

SP
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 ;

PHP
<?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>';
?>

If you are still using mysql_ functions, STOP! Use mysqli_ or PDO. The longer you leave it the more you will have to rewrite.

Donations gratefully received






moon.png

|baaGrid| easy data tables - and more
|baaChart| easy line, column and pie charts




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users