大家好,我遇到了问题,我正在使用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过滤将显示一个消息,说明没有符合所选标准的匹配项等等。这能更清晰地解释吗?