CODEWITHSUNDEEP
phpmysqlcodeignitercodeigniter-3
Hassaan
Hassaan

Reputation: 7672

MySQL/PHP output is not as expected

I have two tables in my database by name language and language_variables.

In language table, I have stored languages and in language_variables have value of specific word in all languages.

DB Structure & data of language

id, title, short_code, type enum('ltr', 'rtl'), default enum('yes', 'no'), status   enum('on', 'off')
1, English, en, ltr, yes, on
2, Urdu, ur, rtl, no, on

DB Structure & data of language_variables

id, language_id, keyword, value
1, 1, str_word, this is simple string
2, 2, str_word, اس سادہ سلک ہے
3, 1, str_word2, this is 2nd string

Output I want

I want to show keyword str_word for once and show drop down list of languages list for same keyword.

Issue in my output

PlayHD.pk

My SQL query is

SELECT
lv.id, lv.language_id, lv.keyword, lv.`value`, l.title
FROM language_variable AS lv
    INNER JOIN `language` AS l ON l.id = lv.language_id

GROUP BY lv.keyword
ORDER BY lv.id DESC

My view

<?php
foreach($records as $record)
{
?>
<div class="banner-row1">
    <div class="banner-row-text">
        <h2><?=$record->keyword?></h2>
        <p class="text-muted">
            <small>
            Value: <?=$record->value?><br />
            Languages: 
            <select id="language_id" required>
                <option></option>
                <?php
                foreach($languages as $language)
                {
                    echo '<option value="'.$record->id.'">'.$language->title.'</option>';
                }
                ?>
            </select>
            </small>
        </p>
    </div>
    <div class="clr"></div>
</div>
<?php
}
?>

Upvotes: 0

Views: 81

Answers (2)

Asenar
Asenar

Reputation: 7020

You need to do at least 2 queries (or change your whole code).

1) get all the "str_word" : query1 = select distinct keyword from language_variable

2) get all the values : query2 = select * from language_variable

3) do a foreach loop with query1, and inside it, display all the corresponding values str_word.

That solution requires to have the query2 as an associative array indexed on str_word.

The alternative, easier to code but requiring more resources is to do the query1 then, in the loop, do the corresponding query select * from language_variable where str_word=:that_keyword (that means as many sql query as there is keywords)

Upvotes: 1

Niranjan N Raju
Niranjan N Raju

Reputation: 11987

You should join with language_id, not with id in language_variables table.

INNER JOIN `language` AS l ON lv.id = l.id

Use this

INNER JOIN `language` AS l ON l.id = lv.language_id
                                        ^         ^

Upvotes: 1

Related Questions