Having a problem creating and using Stored Procedures, hoping for some advice and pointers thanks.
Ok. From a tut(using my own db), I have:
PREPARE stmt FROM "SELECT venue_id FROM tbl_venues WHERE category_id = 'disco'";
EXECUTE stmt;
works great!
The next part:
I’ve called the SP venueCat, do I need to change tbl & col below to my own details eg. col = category_id & tbl = tbl_venues?
delimiter //
DROP PROCEDURE IF EXISTS venueCat//
CREATE PROCEDURE venueCat(IN tbl CHAR(64), IN col CHAR(64))
READS SQL DATA
COMMENT 'Selects venue_id of column col in table tbl'
BEGIN
SET @s = CONCAT('SELECT venue_id(' , col , ') FROM ' , tbl);
PREPARE stmt FROM @s;
EXECUTE stmt;
END;
//
delimiter ;
What I mean is, should I change it to:
CREATE PROCEDURE venueCat(IN tbl CHAR(64), IN col CHAR(64))
READS SQL DATA
COMMENT 'Selects venue_id of column category_id in table tbl_venues'
BEGIN
SET @s = CONCAT('SELECT venue_id(' , category_id , ') FROM ' , tbl_venues);
CREATE PROCEDURE venueCat(IN tbl CHAR(64), IN col CHAR(64)) - do I need to create a separate col for the SP?
I want to be sure before I start messing with live data in my DB any help much appreciated, Barry
I’m running the code in phpadmin > sql window
My aim is to call the SP and output the different venues according to the category_id.
I’ll go into more detail about what I need as we get into the code thanks.
I took the code from an online tutorial, thought is was right (:
SET @s = CONCAT('SELECT venue_id(' , col , ') FROM ' , tbl);
So how should it look? What does col do? Should I be changing this to my own tbl_col.
And where do the SP getting stored in the DB?
Trying to get an understanding thanks, also on the PHP forum trying to do this but very keen to understand the SP approach and how things fit together thanks
yes I’ve read quite a bit, maybe not enough everything very technical, hoping for some explanations in English
Though if you can recommend the best pages to read (most relevant) best way to approach things thanks.
I thought the best approach was to create the SP’s in mysql client and echo the results with php, reduce code, better security and keep things cleaner and more organized? Or is this wrong? Maybe I should be using php’s api, mysqli_stmt_prepare?
i think you should skip the stored procedures and just use simple queries yes thanks, I want to learn and move to the next level, are you saying I’m wasting my time and just keep using loads of select querys in php instead? Surely this is not the way.
Ok r937 I appreciate you time
I’ll slow down a bit and try and get a better understanding of things, but you know what its like when you have projects you want to get finished… thanks again and chat soon
