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
And here is a pic of the query after selecting an item from the ads table and an item from the users table
Aucun commentaire:
Enregistrer un commentaire