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

    Join Date
    Mar 2013
    Posts
    5
    Rep Power
    0

    Can I use a variable instead of a column name in SELECT


    I have following code snippet to implement a multilingual website. Idea is to, depending on user's language i need to translate word "Man" to "Hombre"
    My Question: Will it be valid to use $user_language instead of hard coded column name "Spanish" here.
    code: //$user_language (Spanish in this case) is passed from his profile after log in to the site
    Code:
    SELECT <b>$user_language</b> 
      FROM TranslationTable 
      WHERE English=Man
    [/highlight]

    Code:
    TranslationTable:
      English     Spanish   French
      Man          Hombre    homme


    Thanks
    SW
  2. #2
  3. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,026
    Rep Power
    4210
    i'm not sure you can use a variable like that... what happened when you tested it?

    in any case, if there's any chance you can redesign your TranslationTable, i urge you most strongly to do so
    Code:
    CREATE TABLE TranslationTable 
    ( baseword VARCHAR(37)
    , langcode CHAR(2)
    , PRIMARY KEY ( baseword , langcode )
    , langword VARCHAR(37)
    );
    INSERT INTO TranslationTable VALUES
     ( 'man'  , 'en', 'man' )
    ,( 'man'  , 'fr', 'homme' ) 
    ,( 'man'  , 'sp', 'hombre' ) 
    ;
    SELECT langword
      FROM TranslationTable
     WHERE baseword = 'man' -- always search on the base word
       AND langcode = 'sp' -- passed from user's login
    r937.com | rudy.ca
    please visit Simply SQL and buy my book
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2013
    Posts
    5
    Rep Power
    0

    Can I use a variable instead of a column name in SELECT


    Thanks for the response
    Never tested. To be frank I just started studying MSql and php (week ago)

    I do not want to change the code when I add more languages.
    Even in "WHERE" I am thinking of using dynamic variables on the left hand side as well.

    If this does not work, I believe MySQL should seriously think this as a new feature in their next version. It saves many lines of code. Also it is flexible, dynamic and efficient as well particularly in the multilingual area.

    Thanks again for your suggestion.

    SW
  6. #4
  7. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,026
    Rep Power
    4210
    Originally Posted by sunilwije
    I do not want to change the code when I add more languages.
    please explain how you will not change any code when you add another language and your TranslationTable looks like this --
    Code:
    TranslationTable
      English  Spanish  French  German
      man      hombre   homme   mann
    the suggestion i gave you is already designed not to change any code when any number of languages are added
    r937.com | rudy.ca
    please visit Simply SQL and buy my book
  8. #5
  9. No Profile Picture
    Problem Solver
    Devshed Specialist (4000 - 4499 posts)

    Join Date
    Jan 2001
    Location
    Stockholm, Sweden
    Posts
    4,495
    Rep Power
    534
    1. You should redesign your table the way Rudy suggests so that you don't have to redesign your database just because you are adding another languages.


    2. As for your question about variables:
    Originally Posted by sunilwije
    Thanks for the response
    Never tested. To be frank I just started studying MSql and php (week ago)

    I do not want to change the code when I add more languages.
    Even in "WHERE" I am thinking of using dynamic variables on the left hand side as well.

    If this does not work, I believe MySQL should seriously think this as a new feature in their next version. It saves many lines of code. Also it is flexible, dynamic and efficient as well particularly in the multilingual area.
    The thing is that you are mixing up what happens where, that is whats happening in PHP and whats happening in MySQL.

    Since you are saying that you are writing the code in PHP (and you named your variable with a $ at the beginning) I'm going to assume that it's a _PHP_ variable.

    And depending on how you enclose your SQL-string in PHP (with double or single quotes) that variable will or will not be parsed and substituted for it's value (double quotes parses and substitutes, single quotes doesn't).

    This means that if you use double quotes the SQL string that you have created already has a static string (the value of your variable) in it when it is sent to MySQL in your next line of code when you execute the query.

    And MySQL won't see nor care how you created your SQL-query as long as it's a valid SQL-syntax when it arrives to MySQL.

    So if you just create your queries with double quotes any PHP variables within them will be substituted for their values before they are sent to MySQL and everybody will be happy.




    (That said, although it isn't applicable in your case, you can also work with variables within MySQL (typically @myVariableNameHere]) or place holders ( ? ) (in case you use prepared statements). But if you work on this level and use these then you can't use them in place of an identifier for a table or a column name.
    The reason for this is because MySQL in those cases has (as all databases) optimizations for fast execution that can't handle such dynamic expressions, it would simply put slow things down to much to perform such extensive evaluations every time.

    But as I said this was the advanced level that wasn't part of the answer to your question.)
    Last edited by sr; March 17th, 2013 at 07:09 PM.
    /Stefan
  10. #6
  11. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2013
    Posts
    5
    Rep Power
    0
    I am not worried about the syntax

    Thanks for following up. My actual requirement is bit more complex than I originally posted. Here it is. So I wonder whether you code would help to achieve following without changing the code:
    Scenario:
    Luzy (Spanish) login to Spanish page of Babysitters.com (just an example) do a search for a female baby sitter between 30 and 40yrs from any country.She is interested in Mary from USA and need to translate her English profile to Spanish for Luzy.
    Algorithm to render English page to Spanish users
    $result = mysql_query("SELECT* FROM MasterTable WHERE gender=$gender and marital_status=$marital_status and age>=30 or <=40")
    Following are the matching results from MasterTable
    ID name language age gender education marital_status country_live
    125672 Mary English 35 female highschool single USA
    34521 Ann French 38 female middleschool married France

    while( $row=mysql_fetch_array($result)]{
    if $row['language'] != $user_language then translate data in that row to Spanish
    So Mary's language = English and Ann's = French and should be translated to Spanish
    $language=row$['language']
    Put all the data in that row in a Array called Bsitter_fields
    Bsitter_fields[0] = row$['gender']
    Bsitter_fields[1] = row$['education']
    Bsitter_fields[2] = row$['marital_status']
    Bsitter_fields[3] =row$['country_Live']
    $count=0
    Loop following query through 0 to 3
    $next_result = mysql_query("SELECT $user_language FROM TranslationTable WHERE $language=Bsitter_fields[count]")
    $count=$count+1
    while( $rows=mysql_fetch_array($next_result)]{
    echo $rows['name']." - ".$rows['gender']" - ".$rows['education']" - ".$rows['marital_status']" - ".$rows['country_live'];

    } end of $rows loop
    } end of $row loop
    I may be able to shorten the code by joining $result & $next_result but I still have the issue of using variables in SELECT and WHERE


    TanslationTable
    English Spanish
    Male Hombre
    Single sola
    Seperated separado
    Widowed viudo
    Divorced divorciado
  12. #7
  13. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2013
    Posts
    5
    Rep Power
    0

    Can I use a variable instead of a column name in SELECT


    Originally Posted by sr
    1. You should redesign your table the way Rudy suggests so that you don't have to redesign your database just because you are adding another languages.

    nswer to your question.)
    Thanks for the tip - Prepared statement . Let me work on that and let you know guys what i would come up with.

    SW

IMN logo majestic logo threadwatch logo seochat tools logo