SitePoint Sponsor

User Tag List

Results 1 to 10 of 10
  1. #1
    SitePoint Wizard
    Join Date
    Dec 2005
    Posts
    1,718
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Stored Procedures

    Hi guys

    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:
    Code MySQL:
    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?
    Code MySQL:
    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:
    Code MySQL:
    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.
    The more you learn.... the more you learn there is more to learn.

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,276
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by computerbarry View Post
    SET @s = CONCAT('SELECT venue_id(' , col , ') FROM ' , tbl);
    .
    .
    SET @s = CONCAT('SELECT venue_id(' , category_id , ') FROM ' , tbl_venues);
    what are those parentheses for?

    you can't put a second column after venue_id in parentheses like that

    Quote Originally Posted by computerbarry View Post
    I want to be sure before I start messing with live data in my DB
    i have a fantastic suggestion... test your stuff on a test database first

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Wizard
    Join Date
    Dec 2005
    Posts
    1,718
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Code:
    what are those parentheses for?
    I took the code from an online tutorial, thought is was right

    Code MySQL:
    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
    The more you learn.... the more you learn there is more to learn.

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,276
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by computerbarry View Post
    I took the code from an online tutorial, thought is was right
    sorry, it isn't

    have you read the documentation on mysql.com?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Wizard
    Join Date
    Dec 2005
    Posts
    1,718
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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'm I on the right track?
    The more you learn.... the more you learn there is more to learn.

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,276
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    i think you should skip the stored procedures and just use simple queries
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    SitePoint Wizard
    Join Date
    Dec 2005
    Posts
    1,718
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.
    The more you learn.... the more you learn there is more to learn.

  8. #8
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,276
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by computerbarry View Post
    yes thanks, I want to learn and move to the next level
    ok, that's fair

    then i urge you to study and practice, practice, practice

    remember that test database i mentioned?

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  9. #9
    SitePoint Wizard
    Join Date
    Dec 2005
    Posts
    1,718
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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
    The more you learn.... the more you learn there is more to learn.

  10. #10
    SitePoint Guru
    Join Date
    Jan 2004
    Location
    Uppsala, sverige
    Posts
    697
    Mentioned
    2 Post(s)
    Tagged
    1 Thread(s)
    you can't put a second column after venue_id in parentheses like that
    You can if venue_id is a user defined function.


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •