LEFT JOIN，如果存在第二个表的结果，则在PHP中添加数组

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循环中，我首先获取主题名称（来自另一个表），然后将查询结果添加到数组中：

        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...

之后，我尝试检查LEFT JOIN是否实际返回了定义表中的任何结果，如果是，则将它们添加到数组中 - 但这是我失败的地方......

    // 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.

我已经尝试了所有可以找到的方法来首先检查变量是否已经定义，然后将它们推送到$ results数组。什么都行不通。

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.

我似乎无法成功找到english_definition和/或bulgarian_definition。当我在PhpMyAdmin中运行查询时，它工作得很好。

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.

现在这就是诀窍。当我将$ results数组转储到while循环之外时，两个定义都已添加。

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数组开始打印它们时：

    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:

第二个编辑：决定将两个定义直接放在$ 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

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.

现在这很好用。当我转储数组时，如果不存在定义，我有'english_definition'=> null，如果存在定义，它就在那里。到现在为止还挺好。

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!

问题解决了！

Decided against pushing values to the array (as shown above).
决定不将值推送到数组（如上所示）。

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:

摆脱了获取主题名称的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");

And the while loop is:

而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

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!

事实证明，有时解决问题所需的只是向人们展示，然后开始考虑它，因为有时解决方案就在你面前。感谢所有参与了的人！

1 个解决方案

#1

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.

编辑：删除以前的答案，因为它是不正确的。一旦我找到问题的解决方案，将更新此答案。没有删除这个答案，因为我没有找到这样做的选项。虽然有时候占用空间并感觉很重要:)但是，不要讨厌玩家，讨厌游戏。

秒客网

LEFT JOIN，如果存在第二个表的结果，则在PHP中添加数组

1 个解决方案

#1

#1

相关文章