SitePoint Sponsor

User Tag List

Results 1 to 9 of 9
  1. #1
    SitePoint Addict
    Join Date
    Jan 2007
    Location
    Romania
    Posts
    203
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    functions in MySQL

    Hello,

    I just started to use stored functions in MySQL. I want to send a parameter to a function as a string (ex.: 'en') and in the function to have SELECT en FROM one_table. That en is not hardcoded, it must come from a parameter sent in function. Is better to watch the code below (check the update and insert statements, watch for language_)

    Code:
    SELECT getcities(1, 'ro', 'it', 'A string, does not matter', 4)

    Code:
    DELIMITER $$
    
    DROP FUNCTION IF EXISTS `database`.`getcities`$$
    
    CREATE DEFINER=`root`@`localhost` FUNCTION `getcities`(city_id_ INT, country_code_ CHAR(2), language_ CHAR(2), name_ VARCHAR(255), nr_hotels_ INT) RETURNS int(11)
    BEGIN
    	DECLARE exist INT;
    	SET exist = 0;
    	SELECT IF(city_id IS NOT NULL, 1, 0) INTO exist FROM sgetcities WHERE city_id = city_id_;
    	IF exist = 1 THEN
    		UPDATE sgetcities SET language_ = name_;
    	ELSE
    		INSERT INTO sgetcities (city_id, country_code, language_, nr_hotels) VALUES (city_id_, country_code_, name_, nr_hotels_);
    	END IF;
    	RETURN exist;
        END$$
    
    DELIMITER ;
    That language_ (parameter from function) can be en, fr, ro, it, uk... ISO country code. So is there a solution, other that just make a CASE for ever code I use?
    Last edited by iulian; Feb 22, 2007 at 07:25.

  2. #2
    reads the ********* Crier silver trophybronze trophy longneck's Avatar
    Join Date
    Feb 2004
    Location
    Tampa, FL (US)
    Posts
    9,854
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    mysql does not support using a parameter as an object name.

    normalize your tables. you should not have one column per language.

    also, using root as your security definer is a really bad idea. you should instead create a user for your application that has the minimum necessary rights to function and use that as your definer.

  3. #3
    SitePoint Addict
    Join Date
    Jan 2007
    Location
    Romania
    Posts
    203
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks longneck for the answer.

    About the root
    The database is just on my machine and it's used for development only. But you are right, I should change it otherwise I'll have some problems when I'll transfer functions on the real machine.

    About the table structure
    I'l describe in a few words why I use this structure: I get from an XML some city names translated in several languages. Bucharest (in English) is different by Bucarest (in Italian) and this is my only idea about storing these values. If you have a better solution I'll be happy to implement it.

  4. #4
    reads the ********* Crier silver trophybronze trophy longneck's Avatar
    Join Date
    Feb 2004
    Location
    Tampa, FL (US)
    Posts
    9,854
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

  5. #5
    SitePoint Addict
    Join Date
    Jan 2007
    Location
    Romania
    Posts
    203
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I like this method. I implemented it and, I don't know if it's ok like this, but I always get more than 1 results.

    For example, using
    Code:
    SELECT DISTINCT COALESCE(sec.name, prim.name) as name, coalesce(sec.country_code, prim.country_code) as country_code FROM sgetcities prim LEFT JOIN sgetcities sec ON prim.city_id = sec.city_id AND sec.country_code = 'it'
    ...I get
    Code:
    Bucharest, it
    (NULL),     it
    and table looks like this:

    Code:
    city_id|country_code|name
    ——————————————————————————————
    1      |en          |Bucharest
    1      |it          |(NULL)
    This way I get only the first result.

    I have something wrong?
    Yes, I have something wrong, I don't know what, yet.
    Last edited by iulian; Feb 15, 2007 at 09:28.

  6. #6
    reads the ********* Crier silver trophybronze trophy longneck's Avatar
    Join Date
    Feb 2004
    Location
    Tampa, FL (US)
    Posts
    9,854
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    you need to define the "base" language and make sure every country has an entry in that language. then add that language in the where clause.

    or you need to add the columns i had in the other post to define which entities are related, and which entity is the base entity.

  7. #7
    SitePoint Addict
    Join Date
    Jan 2007
    Location
    Romania
    Posts
    203
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I was using it with LIMIT 0,1 until now.

    It works like this:
    Code:
    select coalesce(s.name, p.name) from sgetcities p left join sgetcities s on p.city_id = s.city_id and s.languagecode = 'fr' where p.languagecode = 'en' and p.city_id = -1983157
    My base languagecode is en, the entity is city_id.

    Thanks for help!

  8. #8
    reads the ********* Crier silver trophybronze trophy longneck's Avatar
    Join Date
    Feb 2004
    Location
    Tampa, FL (US)
    Posts
    9,854
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    ok, your query is now correct. good luck!

  9. #9
    SitePoint Addict
    Join Date
    Jan 2007
    Location
    Romania
    Posts
    203
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Oh, good solution! It exempt me from writing some SQL functions (like in first post). I have several tables where I have to use the same idea.


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
  •