小贝子编程

使用多个筛选器筛选数据时，在选择一个特定筛选器之前，任何筛选器都不会显示数据

  • 本文关键字：筛选 数据 任何 显示 选择 一个 php mysql
  • 更新时间 :
  • 英文 :


所以我的网站有一些过滤器。它们都正确筛选数据，但只有在选择了所有筛选器之后。 如果我选择一个城市，那么它不会显示任何数据，如果我只选择眼睛的颜色，那么它就不起作用，如果我选择种族......无。我必须选择城市，眼睛颜色，头发颜色和种族，以便显示任何数据。我希望用户能够从每个下拉列表中进行任何选择，它将查询和显示数据。当用户选择某些内容时，它会通过 ajax 更新页面。 这是我对过滤器的了解。很抱歉帖子太长...有几个过滤器。

更新：我正在尝试从 2 个表、ADS 表和 USERS 表中提取数据。问题是当我只选择查询 ADS 表的下拉列表时，一切正常，一旦我将用户表过滤器添加到组合中，我需要在显示查询之前使用每个表的下拉列表。

// Set conditions for search 
$whereSQL = $orderSQL = '';
//City selection
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 "; 
}
//Rate Filter
if(isset($_POST['rateSelect']) || !empty($_POST['rateSelect'])){
$rate = $_POST['rateSelect'];
if($rate == "all"){
$whereSQL .= "AND ads.ad_rate >= 0 ";
}
else if($rate == "rate1"){
$whereSQL .= "AND ads.ad_rate BETWEEN 0 AND 150 ";
}
else if($rate == "rate2"){
$whereSQL .= "AND ads.ad_rate BETWEEN 151 AND 250 ";
}
else if($rate == "rate3"){
$whereSQL .= "AND ads.ad_rate BETWEEN 251 AND 350 ";
}
else if($rate == "rate4"){
$whereSQL .= "AND ads.ad_rate BETWEEN 351 AND 5000 ";
}else{
$whereSQL .= "AND ads.ad_rate >= 0 ";
}
}
//Keywords Filter
if(isset($_POST['keywords']) || !empty($_POST['keywords'])){    
$keywords = $filter->filter($_POST['keywords']);
$whereSQL .= "AND ads.ad_content LIKE :keywords "; 
}else{
$keywords = '';
$whereSQL .= "AND ads.ad_content LIKE :keywords ";
}
//Ethnicity Filter
//So once these filters from the USERS table are added, the query wont return anything until I select the city and other dropdowns with it.
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 ";
}
//Eye color filter
if(isset($_POST['eyeColorSelect']) || !empty($_POST['eyeColorSelect'])){ 
$eyeColor = $filter->filter($_POST['eyeColorSelect']);
if($eyeColor == "eyeColorAll"){
$whereSQL .= "AND users.eyeColor != :eyeColor ";
}else{
$whereSQL .= "AND users.eyeColor = :eyeColor ";
} 
}else{
$eyeColor = '';
$whereSQL .= "AND users.eyeColor != :eyeColor ";
}
//Hair color filter
if(isset($_POST['hairColor']) || !empty($_POST['hairColor'])){
$hairColor = $_POST['hairColor'];
if($hairColor == "hairColorAll"){
$whereSQL .= "AND users.hair != :hairColor ";
}else{
$whereSQL .= "AND users.hair = :hairColor ";
}
}else{
$hairColor = '';
$whereSQL .= "AND users.hair != :hairColor ";
}
//Height in inches
if(isset($_POST['height']) || !empty($_POST['height'])){
$height = $filter->filter($_POST['height']);
if($height == "heightAll"){
$whereSQL .= "AND users.height >= 0 ";
}else if($height == "lessFive"){
$whereSQL .= "AND users.height > 1 AND users.height < 152 ";
}else if($height == "five"){
$whereSQL .= "AND users.height > 151 AND users.height < 168 ";
}else if($height == "fiveSix"){
$whereSQL .= "AND users.height > 167 AND users.height < 183 ";
}else if($height == "sixplus"){
$whereSQL .= "AND users.height > 182 ";
}
}
//Female filter
if(isset($_POST['female']) || !empty($_POST['female'])){
$female = $_POST['female'];
if($female == '1'){
$whereSQL .= "  AND ads.ad_female >= '1'";
}else if($female = false){
$whereSQL .= "  AND ads.ad_female == '0'";
}
}
//male filter
if(isset($_POST['male']) || !empty($_POST['male'])){
$male = $_POST['male'];
if($male == '1'){
$whereSQL .= "  AND ads.ad_male >= '1'";
}else if($male = false){
$whereSQL .= "  AND ads.ad_male = '0'";
}
}
//Sort results
if(!empty($_POST['sortBy']) && (strtoupper($_POST['sortBy']) == 'ASC' || strtoupper($_POST['sortBy']) == 'DESC')){ 
$sortBy = $filter->filter($_POST['sortBy']);
}else{ 
$sortBy = 'DESC';
} 
$orderSQL = " ORDER BY ad_date $sortBy "; 
//Query for displaying results
$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,
ads.ad_female,              
ads.ad_male,
users.user_id,
users.username,
users.user_picture,
users.userAge,
users.height,
users.hair,
users.ethnicity,
users.eyeColor,
users.type,
users.industryName              
FROM
ads
INNER JOIN
users
ON
ads.ad_user = users.user_id
WHERE 
ads.ad_approved = '0'
$whereSQL $orderSQL                 
";
//Query for pagination results
$querySelect = "SELECT
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,
ads.ad_female,
ads.ad_male,
users.user_id,
users.username,
users.user_picture,
users.userAge,
users.height,
users.hair,
users.ethnicity,
users.eyeColor,
users.type,
users.industryName              
FROM
ads
INNER JOIN
users
ON
ads.ad_user = users.user_id
WHERE 
ads.ad_approved = '0'
$whereSQL $orderSQL                 
";      
// Count all records 
$query = $conn->prepare($querySelectCount); 
$query->bindParam(":city", $city);
$query->bindValue(':keywords', '%' . $keywords . '%');
$query->bindParam(":hairColor", $hairColor);
$query->bindParam(":ethnicity", $ethnicity);
$query->bindParam(":eyeColor", $eyeColor);
$query->execute();
$row = $query->fetch();
$rowCount = $row['rowNum']; 
//var_dump($query);
// Initialize pagination class 
$paginationConfig = array( 
'baseURL' => $baseURL, 
'totalRows' => $rowCount, 
'perPage' => $limit, 
'currentPage' => $offset, 
'contentDiv' => 'postContent', 
'link_func' => 'searchFilter' 
); 
$pagination =  new Pagination($paginationConfig); 
// Fetch records based on the offset and limit 
$querySelect .= " LIMIT $offset,$limit";
$query = $conn->prepare($querySelect);
$query->bindParam(':city', $city);
$query->bindValue(':keywords', '%' . $keywords . '%');
$query->bindParam(":hairColor", $hairColor);
$query->bindParam(":ethnicity", $ethnicity);
$query->bindParam(":eyeColor", $eyeColor);
$query->execute();
$results = $query->fetchAll();

提前谢谢。

所以我解决了这个问题，这是我用来正确查询多个表的代码，现在工作得很好。问题是，当通过 AJAX 将值发送到 PHP 页面时，任何空的值要么作为"null"发送，要么为空，所以我必须对两者运行检查，并更改 SELECT 查询本身。

$.ajax({
type: 'POST',
url: 'getData.php',
data:'page='+page_num+'&city='+city+'&rateSelect='+rateSelect;
success: function (html) {
$('#displayPage').html(html);
}
});

下面是一些更新的代码：

if(isset($_POST['city']) || !empty($_POST['city']) && $_POST['city'] !== 'null'){    
$city = $filter->filter($_POST['city']);
$adsSQL .= " AND ads.ad_city = :city "; 
}else{
$city = '';
$adsSQL .= " AND ads.ad_city != :city "; 
}
//Rate Filter
if(isset($_POST['rateSelect']) || !empty($_POST['rateSelect']) && $_POST['rateSelect'] !== 'null'){
$rate = $_POST['rateSelect'];
if($rate == "all"){
$adsSQL .= "AND ads.ad_rate >= 0 ";
}
else if($rate == "rate1"){
$adsSQL .= "AND ads.ad_rate BETWEEN 0 AND 150 ";
}
else if($rate == "rate2"){
$adsSQL .= "AND ads.ad_rate BETWEEN 151 AND 250 ";
}
else if($rate == "rate3"){
$adsSQL .= "AND ads.ad_rate BETWEEN 251 AND 350 ";
}
else if($rate == "rate4"){
$adsSQL .= "AND ads.ad_rate BETWEEN 351 AND 5000 ";
}else{
$adsSQL .= "AND ads.ad_rate >= 0 ";
}
}
if(isset($_POST['eyeColorSelect']) || !empty($_POST['eyeColorSelect'])){ 
$eyeColor = $filter->filter($_POST['eyeColorSelect']);
if($eyeColor == "eyeColorAll"){
$usersSQL.= "AND users.eyeColor != :eyeColor ";
}else{
$usersSQL.= "AND users.eyeColor = :eyeColor ";
} 
}else{
$eyeColor = '';
$usersSQL.= "AND users.eyeColor != :eyeColor ";
}

我需要根据每个表拆分查询，因为我正在从 2 个表中提取数据。广告和用户。所以我需要将 2 个变量$adSQL和$usersSQL，以便分别查询 2 个表。我摆脱了单一查询$whereSQL。现在，当我将查询放在一起时，它看起来像这样...

$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,
ads.ad_female,              
ads.ad_male,
users.user_id,
users.username,
users.user_picture,
users.userAge,
users.height,
users.hair,
users.ethnicity,
users.eyeColor,
users.type,
users.industryName          
FROM
ads
INNER JOIN
users
ON
ads.ad_user = users.user_id 
$usersSQL
WHERE 
ads.ad_approved = '0'               
$adsSQL $orderSQL                   
";

$usersSQL值位于 WHERE 子句之前，然后$adsSQL值位于 WHERE 子句之后。不确定这是否是最理想的情况，但就我而言，它非常有效。

相关内容

最新更新


热门标签：

javascript python java c# php android html jquery c++ css ios sql mysql arrays asp.net json python-3.x ruby-on-rails .net sql-server django objective-c excel regex ruby linux ajax iphone xml vba spring asp.net-mvc database wordpress string postgresql wpf windows xcode bash git oracle list vb.net multithreading eclipse algorithm macos powershell visual-studio image forms numpy scala function api selenium