dqczgtem06898
2018-02-05 09:53
浏览 73
已采纳

使用下拉值查询mySQL数据库

I've got below snippet where $filter_xx values are extracted from a dropdown basis user choice.

I'm trying to query the mySQL database with what the user chose to query the database with via dropdown selection.

You will see that there are 4 $filter_xx variables and how many of them are set in a given instance is completely random.

The issue is when I use && in the query it checks if all four parameters are true and then throws and output. (Well I know && is suppose to work that way!). I tried replacing all && operators with || and had no luck.

How do I search the database with only options selected by the user?

    if(isset($filter_brand) || isset($filter_year) || isset($filter_month) || isset($filter_status)) 
         {
          $query = "SELECT * FROM targets WHERE brand='$filter_brand' && startyear='$filter_year' && startmonth='$filter_month' && status='$filter_status' ORDER BY createdon DESC";
          } else {
          $query = "SELECT * FROM targets ORDER BY createdon DESC";
         }

图片转代码服务由CSDN问答提供 功能建议

我有以下片段,其中 $ filter_xx 值是从下拉基础用户中提取的 选择。

我正在尝试使用用户选择通过下拉列表选择查询数据库来查询mySQL数据库。

您将看到有4个 $ filter_xx 变量,并且在给定实例中设置了多少变量是完全随机的。 \ n

问题是当我在查询中使用&& 时,它检查所有四个参数是否为真,然后抛出并输出。 (我知道&& 假设以这种方式工作!)。 我尝试用 || 替换所有&& 运算符,但没有运气。

如何仅使用用户选择的选项搜索数据库?

  if(isset($ filter_brand)|| isset($ filter_year)|| isset($ filter_month)|| isset($ filter_status))
 {
 $ query  =“SELECT * FROM targets WHERE brand ='$ filter_brand'&& startyear ='$ filter_year'&&& startmonth ='$ filter_month'&&& status ='$ filter_status'ORDER BY在DESC创建”; \  n} else {
 $ query =“SELECT * FROM目标ORDER BY在DESC上创建”; 
} 
   
 
  • 写回答
  • 关注问题
  • 收藏
  • 邀请回答

4条回答 默认 最新

  • dsv17139 2018-02-05 09:58
    已采纳

    Try By This

    $join = "";
    //TAKE ONE BLANK VARIBLE THAT JOIN IF VALUE IS SET
    
    if(isset($filter_brand)){
        //IF VALUE ISSET THAN IT ADDED TO QUERY 
        $join .= " AND brand='$filter_brand'";
    }
    
    if(isset($filter_year){
        $join .= " AND startyear='$filter_year'";
    }
    
    $query = "SELECT * FROM targets WHERE id != '' $join ORDER BY createdon DESC";
    
    已采纳该答案
    打赏 评论
  • duanqianmou4661 2018-02-05 10:04

    Try this:

    $query = "SELECT * FROM targets WHERE 1 ";
    $query = isset($filter_brand) ? $query . " AND brand = '".$filter_brand."'" : $query;
    $query = isset($filter_year) ? $query . " AND startyear = '".$filter_year."'" : $query;
    $query = isset($filter_month) ? $query . " AND startmonth = '".$filter_month."'" : $query;
    $query = isset($filter_status) ? $query . " AND status = '".$filter_status."'" : $query;
    $query .= " ORDER BY createdon DESC";
    
    打赏 评论
  • drxdn40242 2018-02-05 10:06

    You can do something like this:

    $query = 'SELECT * FROM targets';
    
    $flag = 0;
    if(isset($filter_brand) ) 
    {
        $query = "SELECT * FROM targets WHERE brand='$filter_brand'";
        $flag = 1;
    }
    
    if(isset($filter_year)) {
        if($flag==1)
            $query .= " &&";
        $query .= " startyear='$filter_year'";
        $flag = 1;
    }
    
    if(isset($filter_month)) {
        if($flag==1)
            $query .= " &&";
        $query = " startmonth='$filter_month'";
        $flag = 1;
    }
    
    if(isset($filter_status)){
        if($flag==1)
            $query .= " &&";
        $query = " status='$filter_status'";
        $flag = 1;
    }
    
    if($flag == 1){
        $query .= " ORDER BY createdon DESC";
    } else {
        $query = "SELECT * FROM targets ORDER BY createdon DESC";
    }
    
    打赏 评论
  • dongpo5264 2018-02-05 10:10

    When you have several values that must work in a similar manner, use an array together with loop. I am supposing, you are using mysqli, change quoting for PDO if needed.

    $mysqli = new mysqli("localhost", "user", "pass", "test");
    //...
    
    //SQL attr name => name of POST parameter
    $filter = array('brand' => 'brand', 'startyear' => 'year', 
                    'startmonth' => 'month', 'status' => 'status');
    
    //here we'll store SQL conditions
    $sql_filter = array();
    
    foreach($filter as $key => $value)
    {
        if (isset($_POST[$value]))
        {
            //use your library function to quote the variable before using it in SQL
            $sql_filter[] = $key . '="'. $mysqli->escape_string($_POST[$value]) . '"';
        }
    }
    
    $query = "SELECT * FROM targets ";
    
    if(isset($sql_filter[0]))
    {
        $query .= 'WHERE ' . implode(' AND ', $sql_filter) . ' ';
    }
    
    $query .= 'ORDER BY createdon DESC';
    
    打赏 评论

相关推荐 更多相似问题