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 />';
}
}
精彩评论