MySQL/PHP Stored Procedures

Craig321

Craig321

Craig321

Soldato
Joined
2 May 2004
Posts
19,950
I've been looking into stored procedures as suggested in my other thread, but I can't find any decent tutorials on how to use them in PHP.

Am I right in saying you basically create a load of MySQL queries which effectively go into a queue which can be executed at the end of the script (for example)?

Does anyone know of any decent tutorials that show how to use them properly with PHP?

Thanks,
Craig.
 
So, for example if I was requesting pages according to whatever's in $_GET['id'] I'd do the following?:

PHP: 
CREATE PROCEDURE getpost @id int(11) AS SELECT id, post FROM posts WHERE id = @id

Which would then be executed as:
PHP: 
$id = $_GET['id'];
$sql = "EXECUTE getpost $id";
mysql_query($sql);
 
Last edited:
yes, but you only create the stored procedure once, ahead of time, and then call it like you've shown from your PHP code.

Even better would be to use an intermediary layer which wraps the call in a normal PHP function and then it becomes just like calling any other function/method.
 
By "only create the stored procedure once" do you mean I create it in e.g. phpMyAdmin, then I can call it in my script from then onwards, or do you mean the procedures are all temporary?

Thanks,
Craig.
 
You create it once, using phpMyAdmin (or the commandline mysql client) and it then lives in your database permanently, just like a trigger or table.
 
Hmm, when I enter the following into phpMyAdmin it just times out (300 seconds) and doesn't execute the query:

Code: 
DELIMITER '/';
CREATE PROCEDURE get_thread(IN, tid, int) 
BEGIN
SELECT post FROM posts WHERE threadid = tid
END/
DELIMITER ';'/
 
Last edited:
You must log in or register to reply here.
Back
Top Bottom