使用MySQL和PHP创建过滤功能

Question

使用MySQL和PHP创建过滤功能

4

大家好，我遇到了问题，我正在使用SQL查询来根据文章的元数据返回结果。这些元数据由用户在用户界面中创建帖子时输入。我的问题在于过滤器，它只能一次处理一个变量，如果同时选择主题和媒体，则不会返回任何结果。以下是代码：

$db_build_post_filter_WHERE = array();
// Default to avoid errors on WHERE GROUP BY
$db_build_post_filter_WHERE [] = 'cmsp_post.post_id > 0';
//$db_build_post_filter_WHERE [] = 'cmsp_post.post_id = cmsp_post_meta.post_id';
if ( isset( $gnocore_cmsp_build_topic_slug_id_array[$filter_topic] ) ) {
    $db_build_post_filter_WHERE [] = 'post_meta_key = "topic_id" AND post_meta_value = ' . $gnocore_cmsp_build_topic_slug_id_array[$filter_topic];
}
if ( isset( $gnocore_cmsp_build_media_slug_id_array[$filter_media] ) ) {
$db_build_post_filter_WHERE [] = 'post_meta_key = "media_id" AND post_meta_value = ' . $gnocore_cmsp_build_media_slug_id_array[$filter_media];
}
if ( isset( $gnocore_cmsp_build_author_slug_id_array[$filter_author] ) ) {
$db_build_post_filter_WHERE [] = 'post_meta_key = "author_id" AND post_meta_value = ' . $gnocore_cmsp_build_author_slug_id_array[$filter_author];
}   

// PROJECT FILTER ARRAY 
$build_post_filter_array = array();
gnoshare_db_select ('cmsp_post LEFT JOIN cmsp_post_meta ON cmsp_post.post_id = cmsp_post_meta.post_id','cmsp_post.post_id',implode(' AND ',$db_build_post_filter_WHERE) . ' GROUP BY cmsp_post.post_id','cmsp_post.project_id, post_name, cmsp_post.post_id','db_build_post_filter_array_num','db_build_post_filter_array_results');
if ( $db_build_post_filter_array_num > 0 ) {
    foreach ($db_build_post_filter_array_results as $db_build_post_filter_array_result) {
        $build_post_filter_array [$db_build_post_filter_array_result->post_id] = '';
    }
}

我认为我的问题出在“PROJECT FILTER ARRAY”部分，如果有人能帮助我，将不胜感激。

谢谢

编辑：将“AND”更改为“OR”会导致仅显示所有帖子的结果，我认为可以说我的问题在这行代码中，但是我仍然找不到生成我想要的结果的方法。

gnoshare_db_select ('cmsp_post LEFT JOIN cmsp_post_meta ON cmsp_post.post_id = cmsp_post_meta.post_id','cmsp_post.post_id',implode(' OR ',$db_build_post_filter_WHERE) . ' GROUP BY cmsp_post.post_id','cmsp_post.project_id, post_name, cmsp_post.post_id','db_build_post_filter_array_num','db_build_post_filter_array_results');

编辑：这是我的表格（大致）：

+----------+---------------+-----------------+  
| post_id  | post_meta_key | post_meta_value |  
+----------+---------------+-----------------+  
|     1    |   topic_id    |        1        |  
+----------+---------------+-----------------+  
|     1    |   media_id    |        1        |  
+----------+---------------+-----------------+  
|     1    |   author_id   |        2        |  
+----------+---------------+-----------------+  
|     2    |   media_id    |        2        |  
+----------+---------------+-----------------+  
|     2    |   topic_id    |        2        |  
+----------+---------------+-----------------+

我希望它可以根据用户在文章中所做的选择进行过滤（例如：用户为文章1选择了主题1、媒体1和作者2，为文章2选择了媒体2和主题2 如上表所示）。如果这个例子是正确的，我想让我的网页在按照以下任何一种过滤方式进行选择时生成文章1：仅按主题1过滤将只显示文章1，按主题1和媒体1过滤将仅显示文章1，按主题1和媒体2过滤将显示一个消息，说明没有符合所选标准的匹配项等等。这能更清晰地解释吗？

- user2063199

使用gnoshare_db_select()函数，有没有一种方法可以打印出最终使用的查询语句？ - Levi

[query] => SELECT cmsp_post.post_id FROM cmsp_post LEFT JOIN cmsp_post_meta ON cmsp_post.post_id = cmsp_post_meta.post_id WHERE cmsp_post.post_id > 0 AND post_meta_key = "topic_id" AND post_meta_value = 1 GROUP BY cmsp_post.post_id ORDER BY cmsp_post.project_id, post_name, cmsp_post.post_id [return_val][return_val] => 查询：从cmsp_post和cmsp_post_meta表中选择post_id，通过左连接将它们关联起来，以确保post_id>0并且post_meta_key="topic_id"且post_meta_value=1。按cmsp_post.project_id、post_name和cmsp_post.post_id的顺序对结果进行分组和排序。 - user2063199

1个回答

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- Oscar Pérez · Answer 1

如果我没错的话，你应该在筛选器之间使用OR运算符，并将它们用括号括起来。类似这样：

 $db_build_post_filter_WHERE [] = '(cmsp_post.post_id > 0)';
 if ( isset( $gnocore_cmsp_build_topic_slug_id_array[$filter_topic] ) ) {
   $db_build_post_filter_WHERE [] = '(post_meta_key = "topic_id" 
                                     AND post_meta_value = ' . 
                $gnocore_cmsp_build_topic_slug_id_array[$filter_topic].')';
 }
 if ( isset( $gnocore_cmsp_build_media_slug_id_array[$filter_media] ) ) {
   $db_build_post_filter_WHERE [] = '(post_meta_key = "media_id" 
                                    AND post_meta_value = ' . 
               $gnocore_cmsp_build_media_slug_id_array[$filter_media].')';
 }
 //Other conditions

// PROJECT FILTER ARRAY 
$build_post_filter_array = array();
gnoare_db_select ('cmsp_post 
                   LEFT JOIN cmsp_post_meta
                          ON cmsp_post.post_id = cmsp_post_meta.post_id',
                 'cmsp_post_post.post_id',
                 implode(' OR ',$db_build_post_filter_WHERE) . 
                ' GROUP BY cmsp_post.post_id','cmsp_post.project_id, post_name, cmsp_post.post_id','db_build_post_filter_array_num','db_build_post_filter_array_results');