iT邦幫忙

0

想製作資料篩選

目前使用很笨的方法查詢出結果,想要把程式碼寫得更有彈性。現在想法是如何讓WHERE後的欄位名稱是可變動的變數,希望幫有解答

<?php
    if(isset($_GET['inquire']) && $_GET['inquire'] == 'inquire' ){
        if($_GET['classID'] != '*' && $_GET['publish'] != '*' ){
            $inpqire_sql = "SELECT `article`.`classID`,`article`.`title`,`article`.`publish`,`article`.`create_time`,`classify`.`classID`,
            `classify`.`className` FROM `article` LEFT JOIN `classify` on `article`.`classID` = `classify`.`classID`
            WHERE  `article`.`classID` = '{$_GET['classID']}' AND `article`.`publish` = '{$_GET['publish']}'
            ORDER BY `article`.`create_time` DESC  ";
        }elseif($_GET['classID'] != '*' && $_GET['publish'] = '*' ){
            $inpqire_sql = "SELECT `article`.`classID`,`article`.`title`,`article`.`publish`,`article`.`create_time`,`classify`.`classID`,
            `classify`.`className` FROM `article` LEFT JOIN `classify` on `article`.`classID` = `classify`.`classID`
            WHERE `article`.`classID` = '{$_GET['classID']}'
            ORDER BY `article`.`create_time` DESC  ";
        }elseif($_GET['classID'] = '*' && $_GET['publish'] != '*' ){
            $inpqire_sql = "SELECT `article`.`classID`,`article`.`title`,`article`.`publish`,`article`.`create_time`,`classify`.`classID`,
            `classify`.`className` FROM `article` LEFT JOIN `classify` on `article`.`classID` = `classify`.`classID`
            WHERE `article`.`publish` = '{$_GET['publish']}'
            ORDER BY `article`.`create_time` DESC  "; 
        }elseif($_GET['classID'] = '*' && $_GET['publish'] = '*' ){
            $inpqire_sql = "SELECT `article`.`classID`,`article`.`title`,`article`.`publish`,`article`.`create_time`,`classify`.`classID`,
            `classify`.`className` FROM `article` LEFT JOIN `classify` on `article`.`classID` = `classify`.`classID`
            ORDER BY `article`.`create_time` DESC  "; 
        };   

        $inquire_result = mysqli_query($link,$inpqire_sql);
        $inquire_row = mysqli_fetch_assoc($inquire_result);    
        }; 
?>

查詢語法

<div id="controller">
                <a href="article_Add.php">新增文章</a>
                    <form action="<?php $_SERVER['PHP_SELF']?>" method="get">
                        <span>類別:</span>
                            <select name="classID" id="classID">
                            <option value="*">All</option>
                            <?php do{ ?>
                                <option <? if($row_className['classID'] == $_GET['classID']){ echo "selected=true";  }; ?> value="<?php echo $row_className['classID'] ?>"><?php echo $row_className['className'] ?></option>
                            <?php }while($row_className = mysqli_fetch_assoc($result_className)) ?>
                            </select> 
                        <span>發布/不發布</span>
                        <select name="publish" id="publish">
                            <option value="*" <? if($_GET['publish'] == "*"){ echo "selected=true";  }; ?> >All</option>
                            <option value="1" <? if($_GET['publish'] == "1"){ echo "selected=true";  }; ?>>發布</option>
                            <option value="0" <? if($_GET['publish'] == "0"){ echo "selected=true";  }; ?>>不發布</option>
                        </select> 
                        <input type="hidden" name="inquire" value="inquire" >
                        <input type="submit" value="查詢" >
                    </form>
            </div>

下拉是選單

應該至少要pdo吧

1 個回答

1
rogeryao
iT邦高手 1 級 ‧ 2019-05-27 22:40:01
最佳解答

依原程式修改 :

<?php
    if(isset($_GET['inquire']) && $_GET['inquire'] == 'inquire' )
    {
        $SqlStr1  = "SELECT `article`.`classID`,`article`.`title`,
                    `article`.`publish`,`article`.`create_time`,
                    `classify`.`classID`,`classify`.`className` 
                    FROM `article` 
                    LEFT JOIN `classify` on `article`.`classID` =             `classify`.`classID` 
                    WHERE 1=1 ";
            
        if($_GET['classID'] != '*')
        {
           $SqlStr2  = "AND `article`.`classID` = '{$_GET['classID']}'  ";
        }
        else
        {
           $SqlStr2  =" ";
        }
        //
        if($_GET['publish'] != '*')
        {
           $SqlStr3  = "AND `article`.`publish` = '{$_GET['publish']}' ";
        }
        else
        {
           $SqlStr3  =" ";
        }        
        //  
        $SqlStr4  = "ORDER BY `article`.`create_time` DESC  ";
        //
        $inpqire_sql = $SqlStr1.$SqlStr2.$SqlStr3.$SqlStr4;
        //
        $inquire_result = mysqli_query($link,$inpqire_sql);
        $inquire_row = mysqli_fetch_assoc($inquire_result);    
    }; 
?>
豬豬人 iT邦新手 5 級 ‧ 2019-05-27 22:51:06 檢舉

謝謝rogeryao 我希望是可以不用elseif 就有辦法依下拉是選單選擇後送出直接加入查詢條件 因為這樣寫更有彈性 請問有甚麼方法嗎?謝謝

rogeryao iT邦高手 1 級 ‧ 2019-05-27 23:37:24 檢舉

程式已更新,另外
A.依查詢條件:
1.類別:全部/指定類別
2.發布/不發布:All/發布/不發布
B.每一種查詢條件皆需要對應一條 SQL 指令,才能將正確的資料回傳
C.有辦法依下拉式選單選擇後送出 "直接加入查詢條件" <= 不易明瞭

豬豬人 iT邦新手 5 級 ‧ 2019-05-28 00:34:33 檢舉

非常感謝你的回答 我學到很多 寫程式碼腦筋真的不能太死 謝謝你

我要發表回答

立即登入回答