How Can I Avoid Having a MySQL query running inside a loop here?

I have a set of two tables. A message_table with id, language_id, message_id, message and a language table setup as id, language.

I need to 1) display results showing which messages have been translated fro开发者_运维问答m language 1 to language 2 and 2) also show which messages in language 1 have yet to be translated. The yet to be translated messages are not in messages_table.

Getting step one to work is easy:

SELECT message_table.message AS start, end.message AS end FROM message_table LEFT JOIN message_table AS end ON (end.message_id = message_table.message_id message_table.id != end.id AND message_table.language_id = '$start' AND end.language_id='$end'

But I need it to return results for messages that have not been translated yet.

Basically, I need to make the following happen with out a looping a mysql query within another mysql query:

$items = $this->query("SELECT message_id, message FROM message_table WHERE language_id = '$start'");

        foreach($items as $result) {

            $message_id = $result['message_id'];

            $returns = $this->query("SELECT * FROM message_table WHERE language_id = '$end' AND message_id = '$message_id'");

            if(!empty($returns)) {

                foreach($returns as $return) {

                    echo "English: " . $result['message'] . "<br>";
                    echo $return['message'] . '<br><br>';

                }

            } else {

                echo "English: " . $result['message'];
                echo "<br>none<br><br>";


            }


        }

Join the table to itself to find related messages.

SELECT
    m1.message as EnglishMessage, m2.message as TranslatedMessage
FROM
    message_table m1
LEFT JOIN
    message_table m2 on m1.message_id = m2.message_id
WHERE
    m1.language = '$start' and m2.language = '$end'

If TranslateMessage is not NULL, then display the translated message. Otherwise, there is no translation available:

foreach ($items as $result) {

    $EnglishMessage = $result['EnglishMessage'];
    $TranslatedMessage = $result['TranslatedMessage'];

    if ($TranslatedMessage) {   // not sure about this!

        echo 'English: '. $EnglishMessage. '<br />';
        echo $TranslatedMessage. '<br /><br />';

    } else {

        echo 'English: '. $EnglishMessage;
        echo '<br />none<br /><br />';

    }

}