使用mysqli和php从数据库中检索语言短语

Question

I have the following 3 tables:

Phrase

PhraseID PhraseType Phrase
14       error      regexNameError
15       error      regexSurnameError
16       error      regexEmailError

Phrase_Language

PhraseLanguageID PhraseID LanguageID NativePhrase
4                14       1          Name must contain at least two characters, and may not contain numbers.
5                14       2          Nom doit contenir au moins deux caractères, et ne peut contenir que des chiffres.
6                14       3          Naam moet ten minste twee karakters bevat, en geen nommers nie.
7                15       1          Surname must contain at least three characters, and may not contain numbers.
8                15       2          Nom doit contenir au moins trois caractères, et ne peut contenir que des chiffres.
9                15       3          Van moet ten minste drie karakters, en geen nommers nie.
10               16       1          Please enter a valid email address.
11               16       2          S'il vous plaît entrer une adresse email valide.
12               16       3          Voer asseblief 'n geldige e-posadres.

Language

LanuageID Language
1         en
2         fr
3         af

What I'm trying to do, is to get all the error phrases into some kind of array or object or class or something that I can easily use in my PHP script.

At the moment I am using the following query to get all the phrases of phrasetype 'error':

SELECT * FROM Phrase JOIN Phrase_Language USING(PhraseID) JOIN Language Using(LanguageID) WHERE Language = 'en' AND PhraseType = 'error';

This gets me what I need, but I'm not sure how to get it into an object or something that I can maybe query like this:

$phraseError->regexNameError->NativePhrase

Because what I need is the NativePhrase. And I've grouped them by PhraseType so that I don't have to load the whole table into my query, and I only get what I need. - Got this tip from another thread.

What I am ultimately after is to have as little queries as possible, so that my site isn't slowed down by multiple (/duplicate) queries all over the show, but also not to have long queries that contain info which I don't necessarily need on the current page.

Answer 1

This should work -

<?php
    //Selecting only the Phrase and the NativePhrase columns.
    $query = "SELECT Phrase, NativePhrase FROM Phrase JOIN Phrase_Language USING(PhraseID) JOIN Language Using(LanguageID) WHERE Language = 'en' AND PhraseType = 'error'";

    //Run the query.
    $res = $db->query($query) or die(mysqli_query($db));

    $PhraseError = Array();

    while($row = $res->fetch_assoc()){
        $PhraseError[$row['Phrase']] = $row['NativePhrase'];
    }

    /*
        This will now allow you to search like this,
        echo $PhraseError['regexSurnameError'];
        //will return the nativePhrase for that.
    */
?>