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

i trying expand functionality of dictionary building showing definitions of terms (if definition exists).

i take data 2 tables follows:

    $query = $db->query("select * ".dictionary_table." " .             "left join ".dictionary_definitions." on ".dictionary_table.".id = ".dictionary_definitions.".term_id ".             "where ".dictionary_table.".".$source." '%".$keyword."%' ".             "and ".dictionary_table.".theme_id = ".$selected_theme_id." ".             "order ".dictionary_table.".id"); 

after that, in while loop, first theme name (from table), add query results array:

        while($row = $query->fetch(pdo::fetch_assoc)) {     // theme name     $theme_name = "theme_".$lang;     $theme_query= $db->query("select theme_id,".$theme_name." ".dictionary_themes." theme_id = ".$theme_id."");     $theme_row  = $theme_query->fetch(pdo::fetch_assoc);     $theme      = $theme_row[$theme_name];      // add results array     $results[] = array(         'english'           => $row['english'],         'bulgarian'         => $row['bulgarian'],         'english_abbr'      => $row['english_abbr'],         'bulgarian_abbr'    => $row['bulgarian_abbr'],         'theme'             => $theme     ); 

after that, try check if left join has returned results definitions table, , if yes, add array - fail...

    // check if definition exists 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've tried ways find first check if variables have been defined, , push them $results array. nothing works.

i don't seem able find out of english_definition and/or bulgarian_definition set. when run query in phpmyadmin, works fine.

the "solution" can think of scrap idea of having separate table definitions , expand main table, that's not great approach know. insight doing wrong appreciated. thanks!

edit: i've changed way elements added array:

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

and trick. when dump $results array outside of while loop, both definitions have been added.

however, large number of warning: illegal string offset 'theme' in... , 'english' , 'bulgarian' - happens below, when run $results array in foreach loop start printing them:

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

no idea yet why happens, keep looking.

second edit: decided put 2 definitions directly inside $results array follows:

    while($row = $query->fetch(pdo::fetch_assoc)) {     // theme name     $theme_name = "theme_".$lang;     $theme_query= $db->query("select theme_id,".$theme_name." ".dictionary_themes." theme_id = ".$theme_id."");     $theme_row  = $theme_query->fetch(pdo::fetch_assoc);     $theme      = $theme_row[$theme_name];      // add results 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 

this works fine. when dump array, if no definition exists, have 'english_definition' => null , if definition exists, it's there. far good.

the new problem can no longer group results theme - theme of last result found shown. different problem altogether. irks me before added definitions, worked fine. can working website here.

problem solved!

1. decided against pushing values array (as shown above).

2. got rid of query within while loop gets theme's name, moving instead query performs search. query is:

                   $query = $db->query("select * ".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." '%".$keyword."%' ".         "order ".dictionary_table.".theme_id, ".dictionary_table.".id"); 

and while loop is:

    while($row = $query->fetch(pdo::fetch_assoc)) {      $theme_name = "theme_".$lang;      // add results 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 

the website link above load upgraded search functionality, definitions shown (if exist). 1 example word curious "worm".

as turns out, takes fix problem show people , start thinking it, solution right in front of you. participated!

edited: deleted previous answer since incorrect. update answer once have solution problem. have not deleted answer since haven't found option so. though fun take space , feel important :), don't hate player, hate game.

---