LEFT JOIN, add to array in PHP if results from second table exist
I am trying to expand the functionality of a dictionary I am building by showing definitions for some of the terms (if a definition exists).
I take the data from the 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 while loop, I first get the theme name (from another table), then add the query results to an 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 the LEFT JOIN has actually returned any results from the definitions table, and if yes, add those 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've tried all ways I could find to first check if the variables have been defined, and then push them to the $results array. Nothing works.
I don't seem to be able to successfully find out of english_definition and/or bulgarian_definition are set. When I run the query itself in PhpMyAdmin, it works just fine.
The only "solution" I can think of is to scrap the idea of having a separate table for the definitions and just expand the main table, but that's not a great approach I know. Any insight as to what I am doing wrong will be greatly appreciated. Thanks!
EDIT: I've changed the way elements are added to the array:
// 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'];
}
And this now does the trick. When I dump the $results array outside of the while loop, both definitions have been added.
However, I now get a large number of Warning: Illegal string offset 'theme' in... and 'english' and 'bulgarian' - this happens below, when I run the $results array in a foreach loop to start printing them:
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']);
}
No idea yet why this happens, will keep looking.
SECOND EDIT: Decided to put the two definitions directly inside the $results array as follows:
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
This now works just fine. When I dump the array, if no definition exists, I have 'english_definition' => null and if a definition exists, it's there. So far so good.
The new problem is that I can no longer group the results by theme - the theme of the last result found is shown. Which is a different problem altogether. What really irks me is that before I added the definitions, everything worked just fine. You can the working website here.
PROBLEM SOLVED!
Got rid of the extra query within the while loop that gets the theme's name, moving it instead to the query that performs the search. Thus the query itself is:
$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");
And the while loop is:
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
The website link from above will now load the upgraded search functionality, where definitions will be shown (if they exist). One example word for anyone curious is "worm".
As it turns out, sometimes all it takes to fix a problem is to show it to people and then start thinking about it, as sometimes the solution is right in front of you. Thanks to all who participated!
EDITED: Deleted previous answer since it was incorrect. Will update this answer once I have a solution to the problem. Have not deleted this answer since I haven't found the option that does so. Though sometimes it is fun to take up space and feel important :), don't hate the player, hate the game.
链接地址: http://www.djcxy.com/p/59418.html
上一篇: for循环中的PHP数组不会添加所有值