SitePoint Sponsor

User Tag List

Results 1 to 5 of 5
  1. #1
    SitePoint Enthusiast
    Join Date
    Mar 2002
    Location
    Italy
    Posts
    98
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Finding records that don't have a translation

    Hi,
    I am back with another question: I love SPFs!

    I have a table which holds all the language strings for my website.
    A string is made up of three parts: the variable name (ie lang_home) of the string, the text associated with this string (ie "Home") and the language this is in. None of these is unique.
    I would like to query my DB to help me figure out which variables have not been translated. Here following are the DB scheme and some sample data:

    Code:
    CREATE TABLE locales (
      id mediumint(9) NOT NULL AUTO_INCREMENT,
      `name` varchar(100) NOT NULL,
      `text` text NOT NULL,
      lang varchar(5) NOT NULL,
      mod_time int(10) unsigned NOT NULL,
      PRIMARY KEY (id),
      KEY lang (lang),
      KEY `name` (`name`),
      FULLTEXT KEY `text` (`text`)
    ) ENGINE=MyISAM  DEFAULT CHARSET=latin1;
    Code:
    INSERT INTO locales SET name = 'lang_home', text = 'Home', lang = 'en-US';
    INSERT INTO locales SET name = 'lang_home', text = 'Casa', lang = 'it-IT';
    INSERT INTO locales SET name = 'lang_test', text = 'Test', lang = 'en-US';
    In this case, I would like to query the DB for missing it-IT translations and have it return 'lang_test'.
    How could I do this?


    Thanks,
    Adrien

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,015
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    Code:
    SELECT x.name
      FROM ( SELECT DISTINCT name FROM locales ) AS x
    LEFT OUTER
      JOIN locales
        ON locales.name = x.name
       AND locales.lang = 'it-IT'
     WHERE locales.name IS NULL
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Enthusiast
    Join Date
    Mar 2002
    Location
    Italy
    Posts
    98
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks 937, you are great!

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,015
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    thanks, but it's actually SQL that is great

    such a complex problem, and such an elegant solution

    can you imagine how a php programmer would do it?
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Enthusiast
    Join Date
    Mar 2002
    Location
    Italy
    Posts
    98
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    can you imagine how a php programmer would do it?
    yeah, that's exactly what i was tying to avoid


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
  •