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

Question

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!

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:

                $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!

Have you done a print_r($results) to see the array_push() worked, but just added them as new array elements, instead of added them to the $result[] that you created in the loop. — Sean
– Sean, Commented Apr 22, 2016 at 4:37
Yes, I do a print_r($results) but array_push doesn't add the new elements - because both english_definition and bulgarian_definition are NULL, I suppose. Neither does $results[] = $row['english_definition']. — cheeseus
– cheeseus, Commented Apr 22, 2016 at 8:52

Webeng · Accepted Answer · 2016-04-22 04:54:37Z

0

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.

edited Apr 22, 2016 at 4:54

answered Apr 22, 2016 at 4:16

Webeng

7,0984 gold badges37 silver badges61 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Sean Over a year ago

look at the query for $theme_query, it is SELECT theme_id,".$theme_name." FROM .... So those columns are not in $theme_row, but in $row which is the outer loop variable.

Webeng Over a year ago

@Sean You are correct. I commented on his question to display the code where he fills in the $row array, I'll edit my answer now considering the new info.

Sean Over a year ago

On your second part - $results[] = array( is correct if you are adding the array on each loop of while($row = $query->fetch(PDO::FETCH_ASSOC)) {. If they have more than 1 row returned, it needs to be $result[], as with $result only on each loop iteration it would overwrite the previous value, and only the last loop value(s) would be saved.

Webeng Over a year ago

@Sean so if I have an array called $result that had information in it already, and I wanted to add rows to it, I could then use $result[] = array(...) and that would give me the same result as array_push($result, array(...))?

Sean Over a year ago

Yes. I prefer $result[] over array_push(), but they basically do the same thing - stackoverflow.com/questions/559844/…

|

Collectives™ on Stack Overflow

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

1 Answer 1

6 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related