mardi 24 décembre 2019

How can I join 2 tables so that I can use filters to query both

I have 2 tables, one called 'ads' and one called 'users'. I have different data stored in each. The ads.ad_user column is populated using the users.user_id from the users table. How can I build a query so that I can filter data from both tables.

Here is the query that I built but it won't allow me to filter data from both tables independently.

<div class="col-xs-6 col-sm-6">
    <div class="form-group">
    <label for="ethnicity">ETHNICITY</label>
    <select class="form-control ethnicitySelect" id="ethnicity" onchange="searchFilter();">
        <option selected disabled>Ethnicity...</option> 
        <option value="ethnicityall">All</option>
        <option value="other">Other</option>                                                
    </select>
</div>
<div class="form-group" style="padding:5px;color:#333;">
    <label for="citySelect">LOCATION</label>
    <select class="form-control citySelect" id="citySelect" onchange="searchFilter();">                                     
        <optgroup label="State">
        <option disabled selected>Select A City</option>
    </select>
</div>


$querySelectCount = "SELECT COUNT(ads.ad_id) as rowNum,
            ads.ad_id,
            ads.ad_title,
            ads.ad_content,
            ads.ad_date,
            ads.ad_user,
            ads.ad_photo,
            ads.ad_photo_thumb,
            ads.ad_age,
            ads.ad_city,
            ads.ad_rate,
            ads.ad_plan,
            ads.ad_approved,
            ads.adminPost,
            users.user_id,
            users.username,
            users.user_picture,
            users.userAge,
            users.height,
            users.hair,
            users.ethnicity,
            users.eyeColor,
            users.type              

            FROM
                ads
            LEFT JOIN
                users
            ON
                ads.ad_approved = '0'
            WHERE 
                ads.ad_user = users.user_id 
            $whereSQL $orderSQL                 
            ";

Here are a couple of the filters I am using.

if(isset($_POST['city']) || !empty($_POST['city'])){    
    $city = $filter->filter($_POST['city']);
    $whereSQL .= " AND ads.ad_city = :city "; 
}else{
    $city = '';
    $whereSQL .= " AND ads.ad_city != :city "; 
}


if(isset($_POST['ethnicity']) || !empty($_POST['ethnicity'])){
    $ethnicity = $filter->filter($_POST['ethnicity']);
    if($ethnicity == "ethnicityall"){
        $whereSQL .= "AND users.ethnicity != :ethnicity ";
    }else{
        $whereSQL .= "AND users.ethnicity = :ethnicity ";
    }
}else{
    $ethnicity = '';
    $whereSQL .= "AND users.ethnicity != :ethnicity ";
}

I am using multiple dropdowns to filter the data, for example: When I select a city nothing will display until I select options that are from the users table like ethnicity or height or eye color...Actually, I need to select all of the above before any data will even be displayed. Would another SELECT inside the query help me achieve what I am looking to do? Thanks.

Also Here is the filters I am applying enter image description here

And here is a pic of the query after selecting an item from the ads table and an item from the users table enter image description here

Aucun commentaire:

Enregistrer un commentaire