CODEWITHSUNDEEP
phpmysql
ChristopheL
ChristopheL

Reputation: 13

Get specific row from array where column is set to a given value

My objective is to create a multi-language web site. At first, I plan to have only 3 languages (French, German, and English).

The welcome page will be displayed in English and then next pages will be localized thanks to a variable carried through a php session.

I created a table page2 with the following columns:

Field_name | English | German | French

Table contains:

Name | "Your name" | "Ihre Name" | "Votre nom"

Street | "Streetname" | "Ihre Strasse" | "Votre rue"

...

php code:

$sql = "SELECT * FROM page2;";

$result = mysql_query($sql);

I want to display the table contents (in several parts of the page) in the appropriate language.

If in English: Your name: Smith (value retrieved from another table - not the question here!)

If in German: Ihre Name: Smith (value retrieved from another table - not the question here!)

My question is how to have "Your name" or "Ihre Name" or "Votre nom" such as:

echo $result['Name'][column=$lang];

Display "English" column value of row "Name" if English is set in $lang

Display "German" column value of row "Name" if German is set in $lang

Upvotes: 1

Views: 2985

Answers (5)

timclutton
timclutton

Reputation: 13004

I'm going to deviate from the pattern of other answers here and suggest that your data model is incorrect and should be changed.

Having a column for each language requires you to modify the structure of your table when you want to add more languages. I believe it would make much more sense to modify the data in your table to achieve this:

field_name | lang | value
-----------+------+-------------
name       | EN   | Your name
name       | DE   | Ihre Name
name       | FR   | Votre nom
street     | EN   | Streetname
street     | DE   | Ihre Strasse
...

This way you can use a query like so:

select * from page2 where lang = ?

and then simply use $result['name'] and $result['street'] in your code. See edit 2, below.

Adding a new language is as simple as executing an insert statement.

Edit

I strongly recommend you read 'Why shouldn't I use mysql_* functions in PHP?'

Edit 2

As noted by ChristopheL, the following method is needed to retrieve the values:

while ($row = mysql_fetch_assoc($result)) {
    $arrResult[$row['field_name']] = $row;
}
echo $arrResult['name']['value'];
echo $arrResult['street']['value'];

Upvotes: 0

Apul Gupta
Apul Gupta

Reputation: 3034

You should try this:

    $arrResult = array();
    $sql    = "SELECT * FROM page2";
    $result = mysql_query($sql);
    while($row    = mysql_fetch_assoc($result))
    {
    $arrResult[$row['field_name']] =  $row;
    }

    echo $arrResult['Name']['English'];

Upvotes: 0

Meneer Venus
Meneer Venus

Reputation: 1045

I think this is what you want: 

$query = "SELECT `$language` FROM `page2` WHERE `Field_name` = 'Street'";
echo $result[$language];

Upvotes: 1

Ole Haugset
Ole Haugset

Reputation: 3797

I dont fully understand the way you are describing your tables, but this should give you a general idea of one way to do it. We add all the results to a array named $names. Then for every row we add a key with the language, and under there we add the name of the person in that language.

$names = array();
for( $i=0; $r=mysql_fetch_assoc( $sql ); $i++ ){
    $names[$i][$r['language']] = $r['name'];
}

Upvotes: 0

Fluffeh
Fluffeh

Reputation: 33502

You pretty much have it already:

$lang="German";
$sql = "SELECT * FROM page2 where field_name='Name'";
// etc...
echo $result[$lang];

Just enter the field_name as you need and either access the result of the array "English", "German" or "French".

Upvotes: 0

Related Questions