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

Question

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

score 1 · Answer 1 · answered Sep 19 '14 at 12:25

1

I think this is what you want:

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

answered Sep 19 '14 at 12:25

Meneer Venus

1,039
2
12
28

score 0 · Answer 2 · answered Sep 19 '14 at 12:23

0

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".

answered Sep 19 '14 at 12:23

Fluffeh

33,228
16
67
80

score 0 · Answer 3 · answered Sep 19 '14 at 12:24

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'];
}

score 0 · Accepted Answer · answered Sep 19 '14 at 12:42

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'];

score 0 · Answer 5 · edited May 23 '17 at 10:33

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'];

Hello Tim, I tried to implement my datamodel as you suggested. However, I do not manage to display the results. $sql = "SELECT field_name, value FROM page2"; $results = mysql_query ($sql); echo $results['name']; It does not display anything. Any idea? ChristopheL — ChristopheL, Sep 25 '14 at 13:08
Hello @ChristopheL. I must have been having a bad day when I wrote this, as it won't work! I'll revise my answer when I get a moment. — timclutton, Sep 25 '14 at 15:37
Hello Tim, I made it work with: $sql = "SELECT field_name, value FROM page2"; $results = mysql_query ($sql); while($row = mysql_fetch_assoc($result)){ $arrResult[$row['field_name']] = $row; } echo $arrResult['name']['value']; echo $arrResult['street']['value']; Cheers, ChristopheL. — ChristopheL, Sep 25 '14 at 15:48

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

5 Answers5