Posted by eholz1 on 04/03/07 21:07

On Apr 2, 5:51 pm, Jerry Stuckle <jstuck...@attglobal.net> wrote:
> eholz1wrote:
> > On Apr 2, 9:10 am, Jerry Stuckle <jstuck...@attglobal.net> wrote:
> >>eholz1wrote:
> >>> Hello PHP group,
> >>> Could someone help me out? I have PHP 5.2, Apache 2.0, and MySQL 5.0
> >>> running on Linux (Redhat Fedora Core 6).
> >>> All that works fine. I would like to be able to "call" a stored
> >>> procedure from a PHP program, and
> >>> run a stored procedure. I have yet to figure out the proper way to do
> >>> this. My stored procedures work fine from the mysql command line
> >>> using syntax: "call sp_min_record (101);"
> >>> But when I try to do the same thing within my PHP page - no luck I get
> >>> an error.
> >>> Here is my stored procedure code:
> >>> DELIMITER $$
> >>> DROP PROCEDURE IF EXISTS `portfolios`.`sp_min_record` $$
> >>> CREATE DEFINER=`ewh`@`localhost` PROCEDURE
> >>> `portfolios`.`sp_min_record`
> >>> (IN folio INT)
> >>> BEGIN
> >>> SELECT MIN(id) INTO minr FROM images3 WHERE p = folio;
> >>> END$$
> >>> DELIMITER ;
> >>> and here is how I am trying to call it from php:
> >>> $dbconnect = db_connect('portfolios') or trigger_error("Error
> >>> Connecting to Database: "
> >>> . mysql_error(), E_USER_ERROR);
> >>> $help = @mysql_query("call sp_min_record ($folio)");
> >>> $rowx = @mysql_fetch_assoc($help);
> >>> $minrec = $rowx[0];
> >>> but I get no value returned. How am I supposed to do this
> >>> (correctly :))?
> >>> Thanks,
> >>>eholz1
> >> First of all, get rid of the '@' characters. Then check the results of
> >> mysql_query to see if you got an error. If so, print the error and see
> >> what's actually happening.
>
> >> Pet Peeve: Why do people disable error reporting with '@', don't check
> >> the results of their calls, then come asking why it isn't working?
>
> >> --
> >> ==================
> >> Remove the "x" from my email address
> >> Jerry Stuckle
> >> JDS Computer Training Corp.
> >> jstuck...@attglobal.net
> >> ==================
>
> > Hello Jerry et al,
>
> > removed the offending @'s!! No error messages. No results either!
> > It seems that my query (stored procedure is not really being called
> > correctly from my php code. As I mentioned earlier, it produces a
> > result from the mysql command line. I could cheat, and use the old
> > "SELECT MIN(id) from table where val = 101;", but a stored procedure
> > seems much cooler.
>
> And what about the second half of my question - what is the result of
> the query? Is mysql_query returning false? Or is it returning a result
> set? And if the former, what's the error (hint: mysql_error()).
>
> --
> ==================
> Remove the "x" from my email address
> Jerry Stuckle
> JDS Computer Training Corp.
> jstuck...@attglobal.net
> ==================

The query is returning false. Here is the error (sorry for not doing
this FIRST!!!)
Fatal Error: Stored Procedure Error: PROCEDURE
portfolios.sp_min_record can't return a result set in the given
context (# 256).

I seem to have this error everytime I get the bright idea to use a
stored procedure that takes data or returns data. I am currently
using the "SELECT min(id) INTO minval FROM xxx" as above, if I call
this sp from the command line I get no result! So I am guessing wrong
syntax??? If I take the "INTO" out of the and use this SELECT MIN(id)
FROM images3 WHERE p = pval; I get a result from the command line, and
the error above when run from the PHP page.

Thanks for the info on this (as always)
eholz1

[Back to original message]