MySQL STORED PROCEDURES in PHP
Moderator: General Moderators
MySQL STORED PROCEDURES in PHP
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?
ї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?
Searching for this in version 4.
Now available in PHP 5 : http://www.php.net/manual/en/function.m ... -query.php
Now available in PHP 5 : http://www.php.net/manual/en/function.m ... -query.php
-
malcolmboston
- DevNet Resident
- Posts: 1826
- Joined: Tue Nov 18, 2003 1:09 pm
- Location: Middlesbrough, UK
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.feyd wrote:http://dev.mysql.com/doc/mysql/en/CREATE_PROCEDURE.html
Because it is supported in MySQL, maybe?
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.feyd wrote:it sure looks like at the least, create function has been in there for a while..
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.
- feyd
- Neighborhood Spidermoddy
- Posts: 31559
- Joined: Mon Mar 29, 2004 3:24 pm
- Location: Bothell, Washington, USA
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
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
...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) 