MySQL STORED PROCEDURES in PHP

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
domagoj
Forum Newbie
Posts: 1
Joined: Sun Mar 02, 2003 7:47 pm

MySQL STORED PROCEDURES in PHP

Post by domagoj »

I tried to run some kind of "STORED PROCEDURE" from PHP since MySQL doesn't support it:

їcode]$query = "
DROP TABLE IF EXISTS tmp_table;
CREATE TEMPORARY TABLE tmp_table (mjgod VARCHAR(20)) TYPE=HEAP;
INSERT INTO tmp_table SELECT DATE_FORMAT(max(time), '%M, %Y') FROM clanci;
SELECT clanci.*, tmp_table.* as najnoviji from clanci, tmp_table WHERE DATE_FORMAT(clanci.time,'%M, %Y')=tmp_table.mjgod;
";
$result = mysql_query($query, $link) or die("No results for bulletin."); ї/code]

The only problem is that this code works inside mysql prompt, but when I try to execute multiple SQL statement string (above) from PHP it returns no results.

Does anybody know is it possible to execute some kind of SQL procedure like above one from PHP?
User avatar
Stoker
Forum Regular
Posts: 782
Joined: Thu Jan 23, 2003 9:45 pm
Location: SWNY
Contact:

Post by Stoker »

you can not process multiple queries in one mysel_query(), just split them and do it separately..
User avatar
anjanesh
DevNet Resident
Posts: 1679
Joined: Sat Dec 06, 2003 9:52 pm
Location: Mumbai, India

Post by anjanesh »

Searching for this in version 4.
Now available in PHP 5 : http://www.php.net/manual/en/function.m ... -query.php
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

the thread is over a year and a half old.. I think we can let it die.
User avatar
anjanesh
DevNet Resident
Posts: 1679
Joined: Sat Dec 06, 2003 9:52 pm
Location: Mumbai, India

Post by anjanesh »

Well I was searching for stored procedures and came up with just this thread - and I never did look at the date.
But still... I was just curious why this isn't so common.
malcolmboston
DevNet Resident
Posts: 1826
Joined: Tue Nov 18, 2003 1:09 pm
Location: Middlesbrough, UK

Post by malcolmboston »

this is interesting indeed.

So basically are stored procedures possible in PHP or not?
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

http://dev.mysql.com/doc/mysql/en/CREATE_PROCEDURE.html

Because it is supported in MySQL, maybe?
User avatar
patrikG
DevNet Master
Posts: 4235
Joined: Thu Aug 15, 2002 5:53 am
Location: Sussex, UK

Post by patrikG »

feyd wrote:http://dev.mysql.com/doc/mysql/en/CREATE_PROCEDURE.html

Because it is supported in MySQL, maybe?
Only since MySQL 5 - just liked subqueries. Looks like MySQL is slowly waking up to the fact that other databases have overtaken it in terms of usability and features.
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

it sure looks like at the least, create function has been in there for a while..
User avatar
patrikG
DevNet Master
Posts: 4235
Joined: Thu Aug 15, 2002 5:53 am
Location: Sussex, UK

Post by patrikG »

feyd wrote:it sure looks like at the least, create function has been in there for a while..
Yup, but AFAIK you couldn't reference to tables - I don't understand the point of a stored procedure if you can't do that.
MySQL Manual: http://dev.mysql.com/doc/mysql/en/Stored_Procedures.html wrote:Stored procedures and functions are a new feature in MySQL version 5.0.
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

damnit.. I hate it when the documentation doesn't say the compatibility on each page... oh well.

I don't use procedures or functions.. I think they are mostly a waste since there isn't more universal support with the versions we have to deal with.

However, if I only had to support 1 database, or I could afford to spend the time writing very custom interfaces to each of the databases I do have to support, then I'd probably use them. But until that day comes, I'll do the calculation in php and forget about it ;)
User avatar
Stoker
Forum Regular
Posts: 782
Joined: Thu Jan 23, 2003 9:45 pm
Location: SWNY
Contact:

Post by Stoker »

...mysl up until version 4 using myisam table is a very simple and very fast and space efficient DB engine.. from version 4 they started adding nev stuff fit for enterprise functionality, version 5 will be much more so.. If you just need a simple engine and can keep your logic in your application, mysql is just fine... If you need to build some or most application logic in the database (very often a smart thing to do), you need stored procedures, triggers, transactions, views and so on, Look at PotsgreSQL (or Oracle etc if you got cash) :)
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

yeah.. I would move to more custom interfaces and things that'd exploit speed increases if I had larger clients and they could afford it. But since the clients I have aren't all that large, or have all that much traffic to deal with, I don't have to optimize as much. :)
Post Reply