Check if value exists in column and output different result-CodePudding

global $wpdb;
    $query = '04454'; // test query - should be pulled from user search results, and should search through zipcodes first, matching to county AND state, then matching the ISC name to locations.

    $ISCs = $wpdb->get_results( $wpdb->prepare( "SELECT * FROM isc_locator_zipcodes AS z
    INNER JOIN isc_locator_counties AS c ON c.COUNTY = z.COUNTY AND c.STATE = z.STATE
    INNER JOIN isc_locator_locations AS l ON c.ISC = l.ISC_NAME
    WHERE z.ZIP = %d", $query ), OBJECT );

    //print_r($ISCs);

    foreach ( $ISCs as $ISC ) 
    {
        // this is where things are output. Style and structure as needed.
        $get_isc_zip = $ISC->ZIP;
        $get_isc_county = $ISC->COUNTY;
        $isc_data .=  '<hr>';  
        $isc_data .=  '<h2>County Served: ' . $ISC->COUNTY . '</h2>';
        $isc_data .= "<h2>ISC Info: " .   $ISC->ISC . "</h2>";

        $isc_data .= '<ul>';
        $isc_data .= "<li>ISC NAME: " . $ISC->ISC_NAME . "</li>";
        $isc_data .= "<li>FIRST NAME: " . $ISC->FIRST_NAME . "</li>";
        $isc_data .= "<li>LAST NAME: " . $ISC->LAST_NAME . "</li>";
        $isc_data .= "<li>ADDRESS: " . $ISC->ADDRESS . "</li>";
        $isc_data .= "<li>ADDRESS 2: " . $ISC->ADDRESS_2 . "</li>";
        $isc_data .= "<li>CITY: " . $ISC->CITY . "</li>";
        $isc_data .= "<li>STATE: " . $ISC->STATE . "</li>";
        $isc_data .= "<li>ZIP: " . $ISC->ZIP . "</li>";
        $isc_data .= "<li>PHONE: " . $ISC->PHONE . "</li>";
        $isc_data .= "<li>WEBSITE: " . $ISC->WEBSITE . "</li>";
        $isc_data .= "<li>EMAIL: " . $ISC->EMAIL . "</li>";
        $isc_data .= "<li>FACEBOOK: " . $ISC->FACEBOOK . "</li>";
        $isc_data .= "<li>TWITTER: " . $ISC->TWITTER . "</li>";
        $isc_data .= "<li>EXCLUSIVE: " . $ISC->EXCLUSIVE . "</li>"; // IF EXCLUSIVE, ELSE IF OPEN, ELSE IF RBDM
        $isc_data .= "<li>FIPS: " . $ISC->FIPS . "</li>"; // not needed
        $isc_data .= "<li>OPEN: " . $ISC->OPEN . "</li>";
        $isc_data .= "<li>RBDM: " . $ISC->RBDM . "</li>";
        $isc_data .= '</ul>';  
        return $isc_data;

    }

The $query will eventually be a search query/user input.

There are three tables -- zipcodes, counties and locations:

zipcodes & counties ALWAYS share the same COUNTY column.
zipcodes & locations share the same ZIP column.
counties & locations share the same ISC/ISC_NAME column values.

Essentially the issue I'm running into is when a zipcode is searched/queried whose ISC_NAME in the counties column DOES NOT match the ISC in locations, my array shows up blank. If it matches, it shows up fine.

Examples of values for ISC names that do not match would be either blank or 'Outside AgVenture coverage area'. Otherwise it would match what's available in the locations ISC_NAME column.

What I would like to happen is when the ISC is outside the coverage area or blank to still pull in some column data and not just stop working completely.

Any help is appreciated!

EDIT

Here's working examples:
https://agventuredev.wpengine.com/isc-locator/?zip=12345 -- ZIP is not located in the database, array shows as blank.
https://agventuredev.wpengine.com/isc-locator/?zip=61010 -- ZIP is in the database, all info showing is correct.

CodePudding user response：

Use LEFT JOIN instead of INNER JOIN

For further clarification:

INNER JOIN returns ONLY what is common to both tables.

While LEFT JOIN returns the entire left table and any data from the right table that matches the criteria