Stored procedures, MySQL, and PHP

[eholz1]

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

[Jerry Stuckle]

eholz1 wrote:

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.
js*******@attglobal.net
==================

[eholz1]

On Apr 2, 9:10 am, Jerry Stuckle <[e-mail]:

eholz1 wrote:

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.
[e-mail]
==================

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.

[Jerry Stuckle]

eholz1 wrote:

On Apr 2, 9:10 am, Jerry Stuckle <[e-mail]:

>eholz1 wrote:

>>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.
[e-mail]
==================

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.
js*******@attglobal.net
==================

[Schraalhans Keukenmeester]

eholz1 wrote:

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

You fetch an associative array and then access $rowx[0].
I'd expect a field name instead.

HTH

Sh.

[eholz1]

On Apr 2, 5:51 pm, Jerry Stuckle <[e-mail]:

eholz1wrote:

On Apr 2, 9:10 am, Jerry Stuckle <[e-mail]:

>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.
[e-mail]
==================

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.
[e-mail]
==================

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

[Jerry Stuckle]

eholz1 wrote:

On Apr 2, 5:51 pm, Jerry Stuckle <[e-mail]:

>eholz1wrote:

>>On Apr 2, 9:10 am, Jerry Stuckle <[e-mail]:
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.
[e-mail]
==================
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.
[e-mail]
==================

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

Well, it looks like a problem with your SP. I'd recommend following up
in comp.databases.mysql. Try to get it to work from the command line first.

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
js*******@attglobal.net
==================

[Andy Hassall]

On 2 Apr 2007 08:33:05 -0700, "eholz1" <ew****@gmail.comwrote:

>$help = @mysql_query("call sp_min_record ($folio)");

I was under the impression that only the "mysqli" extension could call MySQL
stored procedures, and that the older "mysql" extension only handles plain SQL.

--
Andy Hassall :: an**@andyh.co.uk :: http://www.andyh.co.uk
http://www.andyhsoftware.co.uk/space :: disk and FTP usage analysis tool