By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
449,278 Members | 1,106 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 449,278 IT Pros & Developers. It's quick & easy.

Stored procedures, MySQL, and PHP

P: n/a
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

Apr 2 '07 #1
Share this Question
Share on Google+
7 Replies


P: n/a
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
==================
Apr 2 '07 #2

P: n/a
On Apr 2, 9:10 am, Jerry Stuckle <jstuck...@attglobal.netwrote:
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.
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.

Apr 2 '07 #3

P: n/a
eholz1 wrote:
On Apr 2, 9:10 am, Jerry Stuckle <jstuck...@attglobal.netwrote:
>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.
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.
js*******@attglobal.net
==================
Apr 3 '07 #4

P: n/a
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.
Apr 3 '07 #5

P: n/a
On Apr 2, 5:51 pm, Jerry Stuckle <jstuck...@attglobal.netwrote:
eholz1wrote:
On Apr 2, 9:10 am, Jerry Stuckle <jstuck...@attglobal.netwrote:
>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
Apr 3 '07 #6

P: n/a
eholz1 wrote:
On Apr 2, 5:51 pm, Jerry Stuckle <jstuck...@attglobal.netwrote:
>eholz1wrote:
>>On Apr 2, 9:10 am, Jerry Stuckle <jstuck...@attglobal.netwrote:
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

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
==================
Apr 3 '07 #7

P: n/a
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
Apr 3 '07 #8

This discussion thread is closed

Replies have been disabled for this discussion.