I would like to display the sports clubs that offer the type of sports a user selected. So far I've done the following:

1. I added the filters to the page:

Filters

2. For the types of sports, I've created the array 'typefilter' via name="typefilter[]"

   Adventure
   Climbing
   Crossfit
   Cycling
   Kitesurfing
   Martial arts
   Multi-sports
   Pilates
   Rugby
   Running
   Swimming
   Tennis
   Triathlon
   Yoga

3. I checked that the selected items are appearing in the array and that works:

array

4. JOIN tables

I have a table with Organizations, and a table with Types, and also a Location table. The name of the sports are the name of the columns in the Types database:

types

When I select the organization info via the following query, it works when I have 'adventure' hardcoded in the query:

$result = $wpdb->get_results("SELECT * FROM wp_x_organizations
        JOIN wp_x_types ON wp_x_types.organization = wp_x_organizations.organization
        JOIN wp_x_location ON wp_x_location.organization = wp_x_organizations.organization
        WHERE wp_x_types.adventure = 1");
        ?>

The issue is, I want 'adventure' to be the value of the typefilter[] array. And even better if there is a loop or so, where all organizations with all selected sports types are shown in the results table.

Any ideas how to solve this?

CodePudding user response：

So use php to construct the sql string.

$arr = ['climbing', 'adventure', 'multi-sports'];
$arr_where = array_map(function($item) {
    return "wp_x_types.`$item` = 1";
}, $arr);
$str_where = "(" . implode (' OR ', $arr_where) . ")";
// echo $str_where;
$sql = "SELECT * FROM wp_x_organizations
        JOIN wp_x_types ON wp_x_types.organization = wp_x_organizations.organization
        JOIN wp_x_location ON wp_x_location.organization = wp_x_organizations.organization
        WHERE $str_where";
$result = $wpdb->get_results($sql);