Geek Answers Handbook

LEFT JOIN, add to array in PHP if results from second table exist

I am trying to extend the functionality of a dictionary that I create by showing definitions for some terms (if a definition exists).

I take data from two tables as follows:

    $query = $db->query("SELECT * FROM ".DICTIONARY_TABLE." " .
            "LEFT JOIN ".DICTIONARY_DEFINITIONS." ON ".DICTIONARY_TABLE.".id = ".DICTIONARY_DEFINITIONS.".term_id ".
            "WHERE ".DICTIONARY_TABLE.".".$source." LIKE '%".$keyword."%' ".
            "AND ".DICTIONARY_TABLE.".theme_id = ".$selected_theme_id." ".
            "ORDER BY ".DICTIONARY_TABLE.".id");

After that, in the loop, whileI first get the topic name (from another table), and then add the query results to the array:

        while($row = $query->fetch(PDO::FETCH_ASSOC)) {
    // get theme name
    $theme_name = "theme_".$lang;
    $theme_query= $db->query("SELECT theme_id,".$theme_name." FROM ".DICTIONARY_THEMES." WHERE theme_id = ".$theme_id."");
    $theme_row  = $theme_query->fetch(PDO::FETCH_ASSOC);
    $theme      = $theme_row[$theme_name];

    // add all results to an array
    $results[] = array(
        'english'           => $row['english'],
        'bulgarian'         => $row['bulgarian'],
        'english_abbr'      => $row['english_abbr'],
        'bulgarian_abbr'    => $row['bulgarian_abbr'],
        'theme'             => $theme
    );

After that, I try to check if I really LEFT JOINreturned any results from the definition table, and if so, add them to the array as well - but this is where I fail ...

    // check if definition exists for this term
    if(isset($row['bulgarian_definition'])) {
        array_push($results['bulgarian_definition'], $row['bulgarian_definition']);
    }
    if(isset($row['english_definition'])) {
        array_push($results['english_definition'], $row['english_definition']);
    }

I tried all the ways that I could find to first check if the variables are defined and then push them into an array $results. Nothing works.

, english_definition / bulgarian_definition. PhpMyAdmin, .

"", , , , . , , . !

EDIT: :

    // check if definition exists for this term
    if(isset($row['bulgarian_definition'])) {
        $results['bulgarian_definition'] = $row['bulgarian_definition'];
    }
    if(isset($row['english_definition'])) {
        $results['english_definition'] = $row['english_definition'];
    }

. $results while, .

Warning: Illegal string offset 'theme' in... 'english' 'bulgarian' - , $results foreach, :

    foreach($results as $result) {

    if($theme != $result['theme']) {
        $theme = $result['theme'];
        $search_results .= "<h3>" . $result['theme'] . "</h3>";
    }

    if($source == "english") {
        foreach ($keywords as $keyword) {
            $result['english'] = preg_replace("|($keyword)|Ui", "<span style=\"color:#780223\">" . $keyword . "</span>", $result['english']);
        }

, , .

: $results :

    while($row = $query->fetch(PDO::FETCH_ASSOC)) {
    // get theme name
    $theme_name = "theme_".$lang;
    $theme_query= $db->query("SELECT theme_id,".$theme_name." FROM ".DICTIONARY_THEMES." WHERE theme_id = ".$theme_id."");
    $theme_row  = $theme_query->fetch(PDO::FETCH_ASSOC);
    $theme      = $theme_row[$theme_name];

    // add all results to an array
    $results[] = array(
        'english'           => $row['english'],
        'bulgarian'         => $row['bulgarian'],
        'english_abbr'      => $row['english_abbr'],
        'bulgarian_abbr'    => $row['bulgarian_abbr'],
        'theme'             => $theme,
        'bulgarian_definition'  => $row['bulgarian_definition'],
        'english_definition'    => $row['english_definition']
    );  
}// end while

. , , 'english_definition' => null, , . .

, - . . , , , . - .

!

  • ( ).

  • while, , , . , :

                    $query = $db->query("SELECT * FROM ".DICTIONARY_TABLE." " .
        "JOIN ".DICTIONARY_THEMES." ON ".DICTIONARY_TABLE.".theme_id = ".DICTIONARY_THEMES.".theme_id ".
        "LEFT JOIN ".DICTIONARY_DEFINITIONS." ON ".DICTIONARY_TABLE.".id = ".DICTIONARY_DEFINITIONS.".term_id ".
        "WHERE ".DICTIONARY_TABLE.".".$source." LIKE '%".$keyword."%' ".
        "ORDER BY ".DICTIONARY_TABLE.".theme_id, ".DICTIONARY_TABLE.".id");

while:

    while($row = $query->fetch(PDO::FETCH_ASSOC)) {

    $theme_name = "theme_".$lang;

    // add all results to an array
    $results[] = array(
        'english'           => $row['english'],
        'bulgarian'         => $row['bulgarian'],
        'english_abbr'      => $row['english_abbr'],
        'bulgarian_abbr'    => $row['bulgarian_abbr'],
        'theme'             => $row[$theme_name],
        'bulgarian_definition'  => $row['bulgarian_definition'],
        'english_definition'    => $row['english_definition']
    );  
}// end while

- , ( ). "".

, , , - , , . , !

+4
1

EDITED: , . , . , , . :), , .

0

More articles:


All Articles