#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2013
    Posts
    11
    Rep Power
    0

    Procedure and function help


    Hi everyone, I am new to mysql and trying to learn procedures and functions. a little background to the problem I am having. I have a table with 3 colums ( authID, AuthName, timestamp) I am trying to write a procedure that will return the authName when the authID is entered so pretty much example

    If authName = 5 then print the authors name. this is what I have


    DELIMITER $$

    Create PROCEDURE Getauthorsname1 ( IN authID INT, OUT authName VARCHAR (100))

    BEGIN

    SELECT authName from authors where authID = authName;

    END $$

    DELIMITER ;

    CALL Getauthorsname1 (2, @authName);

    When I call the procedure I am not getting any errors, however I am not getting anything outputted either.

    Problem 2

    Same table but I need to create a function that will return the number of even authors from the authID colum this is what I have so far I am not sure on how to write the script for even only so I am trying authID > 10


    create function Num_Authors(authID INT)
    returns INT
    select COUNT(*) "Number of Authors"
    FROM authors
    WHERE authID > 10;

    This function will not run I am trying to figure out what to do

    Thanks for your help in advance,

    John
  2. #2
  3. --
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Jul 2012
    Posts
    3,957
    Rep Power
    1046
    Hi,

    Originally Posted by John_in_Parrish
    When I call the procedure I am not getting any errors, however I am not getting anything outputted either.
    I'm sure you do get output, namely an empty set.

    Look at your query. That doesn't look right. Using the same name for the parameters and the columns also isn't a good idea. Which one is which in your query? And what exactly is the purpose of the OUT parameter if you don't use it anywhere?



    Originally Posted by John_in_Parrish
    This function will not run I am trying to figure out what to do
    "Will not run"? What does that mean? And what's this weird "Number of Authors" thing? Is that a comment? Then there must be a double dash -- in front of it.
    The 6 worst sins of securityHow to (properly) access a MySQL database with PHP

    Why can’t I use certain words like "drop" as part of my Security Question answers?
    There are certain words used by hackers to try to gain access to systems and manipulate data; therefore, the following words are restricted: "select," "delete," "update," "insert," "drop" and "null".
  4. #3
  5. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,145
    Rep Power
    4274
    Originally Posted by Jacques1
    And what's this weird "Number of Authors" thing?
    that's a column alias, young sql padawan
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  6. #4
  7. --
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Jul 2012
    Posts
    3,957
    Rep Power
    1046
    Originally Posted by r937
    that's a column alias, young sql padawan
    Only in MySQL, grandpa, only in MySQL.

    @John_in_Parrish: What I'm trying to say is that you shouldn't rely on MySQL quirks. If you ever switch to a real database system, you'll have a hard time unlearning all those bad habits.

    Yes, MySQL will let you get away with pretty much everything. Syntax? Data types? Proper grouping? The rules of mathematics? MySQL doesn't care. But most other database systems you'll encounder in your life do care. So it's probably best to learn the correct way rather than "the MySQL way".
    Last edited by Jacques1; November 17th, 2013 at 05:10 AM.
    The 6 worst sins of securityHow to (properly) access a MySQL database with PHP

    Why can’t I use certain words like "drop" as part of my Security Question answers?
    There are certain words used by hackers to try to gain access to systems and manipulate data; therefore, the following words are restricted: "select," "delete," "update," "insert," "drop" and "null".
  8. #5
  9. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,145
    Rep Power
    4274
    Originally Posted by Jacques1
    Only in MySQL, grandpa, only in MySQL.
    i just ran it through the mimer validator, and it conforms to core SQL-99

    your anti-mysql bias would be better if you confined yourself to other forums
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  10. #6
  11. --
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Jul 2012
    Posts
    3,957
    Rep Power
    1046
    Originally Posted by r937
    i just ran it through the mimer validator, and it conforms to core SQL-99
    Do I really have to explain the MySQL syntax to you?

    In MySQL, double quotes denote a string (unless ANSI_QUOTES is turned on). So in the query above, a string is used as an alias.

    ANSI SQL uses single quotes to denote strings. So the query above would be written as

    sql Code:
    SELECT COUNT(*) 'Number of Authors'
    FROM authors
    WHERE authID > 10;

    Now let's paste this into the validator:

    Code:
    Result:
    select COUNT(*) 'Number of Authors' 
                    ^------------------
    syntax error: 'Number of Authors' FROM
      correction: FROM
    
    FROM authors 
    WHERE authID > 10;
    Nope, that doesn't look like valid SQL.

    So what's wrong? Aliases must be identifiers.

    I generally recommend using standard identifiers (a single word, no spaces, no special characters). If you absolutely need to use spaces, then you have to wrap the identifier in identifier quotes. MySQL uses backticks `` for that (unless ANSI_QUOTES is turned on):

    sql Code:
    SELECT
    	column_1 AS some_standard_alias
    	, column_2 AS `Look, I'm a nonstandard identifier!`
    FROM
    	some_table
    ;

    But like I said, nonstandard identifiers are generally a bad idea. They're confusing and error-prone, they may require additional work in the application, and they're simply ugly.

    Use standard aliases.



    Originally Posted by r937
    your anti-mysql bias would be better if you confined yourself to other forums
    This is the MySQL forum, not the "MySQL fan forum".

    Whether or not I like MySQL is completely irrelevant here.
    The 6 worst sins of securityHow to (properly) access a MySQL database with PHP

    Why can’t I use certain words like "drop" as part of my Security Question answers?
    There are certain words used by hackers to try to gain access to systems and manipulate data; therefore, the following words are restricted: "select," "delete," "update," "insert," "drop" and "null".
  12. #7
  13. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,145
    Rep Power
    4274
    Originally Posted by Jacques1
    Whether or not I like MySQL is completely irrelevant here.
    however, being an arrogant adolescent is very much relevant, and i am not the first one to ask you to grow up and stop being such a shít-disturber

    if you really want to let loose with your anti-mysql posturing, write a blog or something

    but no, that would require that you reveal your true identity, which no troll such as yourself has the balls to do

    edit: added link to troll definition

    Last edited by r937; November 17th, 2013 at 08:10 AM.
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  14. #8
  15. --
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Jul 2012
    Posts
    3,957
    Rep Power
    1046
    Rudy, if you have a problem with me (for whatever reason), feel free to write me a personal message so that we can discuss this in private.

    But this is John's thread. So if you don't have anything useful to say, just let us help him. Right now, the only person who's running around angrily and calling people names is in fact you.
    The 6 worst sins of securityHow to (properly) access a MySQL database with PHP

    Why can’t I use certain words like "drop" as part of my Security Question answers?
    There are certain words used by hackers to try to gain access to systems and manipulate data; therefore, the following words are restricted: "select," "delete," "update," "insert," "drop" and "null".
  16. #9
  17. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,145
    Rep Power
    4274
    Originally Posted by Jacques1
    Rudy, if you have a problem with me (for whatever reason), feel free to write me a personal message so that we can discuss this in private.
    next time it happens, i will... or maybe i'll just slap a temporary ban on you

    furthermore, i would like to point out that it was you who first publicly derailed the thread with your anti-mysql screed

    calling on others to desist and return the thread to its proper topic is typical troll behaviour, once it's had the desired effect of causing a disturbance
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  18. #10
  19. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2013
    Posts
    58
    Rep Power
    1
    Originally Posted by John_in_Parrish
    HI have a table with 3 colums ( authID, AuthName, timestamp) I am trying to write a procedure that will return the authName when the authID is entered so pretty much example

    If authName = 5 then print the authors name. this is what I have

    Code:
    SELECT authName from authors where authID = authName;
    I'll confess I don't know a ton about procedures, but what you describe in the beginning doesn't seem to match what your select statement has.

    Shouldn't it be something more like
    Code:
    SELECT authName from authors WHERE authID='$authID'
    instead? You want the author name returned when the author ID is matched to a number, not to a name, right?
  20. #11
  21. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2013
    Posts
    11
    Rep Power
    0
    Originally Posted by aysiu
    I'll confess I don't know a ton about procedures, but what you describe in the beginning doesn't seem to match what your select statement has.

    Shouldn't it be something more like
    Code:
    SELECT authName from authors WHERE authID='$authID'
    instead? You want the author name returned when the author ID is matched to a number, not to a name, right?


    Yes that is correct, When called authID return AuthName
  22. #12
  23. --
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Jul 2012
    Posts
    3,957
    Rep Power
    1046
    Then rewrite your query accordingly.

    Start by choosing unique parameter names which don't collide with the column names of the table.

    And like I already said above, you have to actually store the result in the OUT parameter. Otherwise, the procedure will simply have no effect.
    The 6 worst sins of securityHow to (properly) access a MySQL database with PHP

    Why can’t I use certain words like "drop" as part of my Security Question answers?
    There are certain words used by hackers to try to gain access to systems and manipulate data; therefore, the following words are restricted: "select," "delete," "update," "insert," "drop" and "null".

IMN logo majestic logo threadwatch logo seochat tools logo