php - 根据不同的用户输入格式化 mysql 查询

Question

$searchParams 实际上是一个数组，如果所有选项都打开，则看起来像这样

[q] => 77 [enhanced] => Y [no_subjects] => Y [visible] => Y  

用户可以更改它，以便增强选项可能会或可能不会打开....等...

数组的外观将会改变

[q] => 77 [enhanced] => N [no_subjects] => N [visible] => N

等等...

public function getWebBookBasicSearchByParams($searchParams){
            $q = $searchParams['q'];
            $fields = "`bw`.`id`,`bw`.`isbn`,`bw`.`book_title`,`bw`.`edited_by`,`bw`.`book_schedule_date`,`bw`.`downloaded_num`,`bw`.`visible_online`,`snr`.`subject_id`";
            $join= "LEFT JOIN {$this->_t_subjects_relations} snr ON (bw.id = snr.object_id and snr.type ='bookweb') ";
            $counter=0;
            $add="";
        if (isset($searchParams['q']) && !empty($searchParams['q'])) {

            if($searchParams['no_subjects'] == 'Y'){
                $add.= " snr.subject_id = NULL";
                $counter ++;
            }
            if($searchParams['enhanced'] == 'Y'){
                $add.= " MATCH(bw.description1,bw.about_the_book) AGAINST ('{$q}'IN BOOLEAN MODE)";
                $counter++;
            }
            if($searchParams['visible'] == 'Y'){
                $add.= " bw.visible_online = 1";
                $counter++;
            }
            if($searchParams['visible'] == 'N'){
                $add.= " bw.visible_online = 0";
                $counter++;
            }
            $inCopula = ($counter > 0) ? " AND" : " " ;
            $extCopula = ($counter > 0) ? "WHERE " : " " ;
        }

        $query = "SELECT {$fields} FROM {$this->_t_books_web} bw {$join}{$extCopula}{$add}";

使用这段代码我得到这个$query

SELECT `bw`.`id`,`bw`.`isbn`,`bw`.`book_title`,`bw`.`edited_by`,`bw`.`book_schedule_date`,`bw`.`downloaded_num`,`bw`.`visible_online`,`snr`.`subject_id` FROM `books_web` bw LEFT JOIN `subjects_new_relations` snr ON (bw.id = snr.object_id and snr.type ='bookweb') 
AND snr.subject_id = NULL
MATCH(bw.description1,bw.about_the_book) AGAINST ('77'IN BOOLEAN MODE) bw.visible_online = 1

显然这是错误的，它应该是这样的

SELECT `bw`.`id`,`bw`.`isbn`,`bw`.`book_title`,`bw`.`edited_by`,`bw`.`book_schedule_date`,`bw`.`downloaded_num`,`bw`.`visible_online`,`snr`.`subject_id` FROM `books_web` bw LEFT JOIN `subjects_new_relations` snr ON (bw.id = snr.object_id and snr.type ='bookweb') 
**WHERE**(snr.subject_id = NULL **AND**
MATCH(bw.description1,bw.about_the_book) AGAINST ('77'IN BOOLEAN MODE) **AND** bw.visible_online = 1)

您将如何修改上面的代码以获得所需的mysql查询，谢谢您的帮助!

Answer 1

只需使用一个数组来存储所有条件并使用 implode来聚合它们。

public function getWebBookBasicSearchByParams($searchParams){
                $q = $searchParams['q'];
                $where = array();
                $fields = "`bw`.`id`,`bw`.`isbn`,`bw`.`book_title`,`bw`.`edited_by`,`bw`.`book_schedule_date`,`bw`.`downloaded_num`,`bw`.`visible_online`,`snr`.`subject_id`";
                $join= "LEFT JOIN {$this->_t_subjects_relations} snr ON (bw.id = snr.object_id and snr.type ='bookweb') ";
                $counter=0;
                $add="";
            if (isset($searchParams['q']) && !empty($searchParams['q'])) {

                if($searchParams['no_subjects'] == 'Y'){
                    $where[] = "snr.subject_id = NULL";
                    $counter ++;
                }
                if($searchParams['enhanced'] == 'Y'){
                    $where[] = " MATCH(bw.description1,bw.about_the_book) AGAINST ('{$q}'IN BOOLEAN MODE)";
                    $counter++;
                }
                if($searchParams['visible'] == 'Y'){
                    $where[] = " bw.visible_online = 1";
                    $counter++;
                }
                if($searchParams['visible'] == 'N'){
                    $where[] = " bw.visible_online = 0";
                    $counter++;
                }
                $inCopula = ($counter > 0) ? " AND" : " " ;
                $extCopula = ($counter > 0) ? "WHERE " : " " ;
            }

            $query = "SELECT {$fields} FROM {$this->_t_books_web} bw {$join}{$extCopula} WHERE " . implode(' AND ', $where);
    }