doutang7383 2011-06-30 19:00
浏览 56
已采纳

Codeigniter省略了空标准的where子句

I have a page for browsing db records. The viewer can filter records by category, author, and tags. I'm using a form instead of url segments for filtering records (it feels more secure because I can validate inputs.)

For instance, when all form inputs are populated the query looks like this:

SELECT * FROM (`posts`) WHERE `category` = 'technolgy' AND `author` = 'lila' AND `tags` = 'ebook'

However if one input or more is empty, I get no results. For example:

SELECT * FROM (`posts`) WHERE `category` = '' AND `author` = 'lila' AND `tags` = '' 

I want the inputs to be optional so for example if just author name is entered, I can return records made by that author regardless of category and tags. How can I omit the and where clause if empty?

Note: or_where clause is not the solution because it doesn't return a precise query if all filter inputs all filled.

My Model

function filter($form_values)
    {                       
        $query = $this->db->get('posts');           
        $this->db->where($form_values); //adds clause to all array items
        return $query->result();        
    }

The function parameter is an array with input values from my view. Example,

$form_values = array('category' => $category, 'author' => $author, 'tags' => $tags);

and my View

$form_values = array (                  
                'category' => $this->input->post('category'),
                'author'   => $this->input->post('author'),
                'tags'     => $this->input->post('tags')
            );

$this->Records_model->filter($form_values);    

I know that in Codeigniter if $_POST' are empty they are set to FALSE. Can that be used to achieve what I'm trying? I'm not sure if I'm on the right track

  • 写回答

1条回答 默认 最新

  • dstd2129 2011-06-30 19:10
    关注

    You are correct that $this->input->post() will return FALSE if the $_POST value is not set. Unless you specifically want IS NULL to be part of the query (which I believe will happen by passing FALSE to param 2 of where(), not 100% sure), just filter out the empty values:

    function filter($form_values)
    {              
        $form_values = array_filter($form_values);
    
        // NOTE:
        // where() needs to be called first, or your query won't use the WHERE clause
    
        // You may need to make sure there is at least one value in $form_values
        if ( ! empty($form_values)) // however you wish to check for a value
        {
            $this->db->where($form_values); //adds clause to all array items
        }
    
        $query = $this->db->get('posts');           
    
        return $query->result();        
    }
    

    http://php.net/manual/en/function.array-filter.php

    The important part to note on array_filter():

    If no callback is supplied, all entries of input equal to FALSE (see converting to boolean) will be removed.

    本回答被题主选为最佳回答 , 对您是否有帮助呢?
    评论

报告相同问题?

悬赏问题

  • ¥15 爬取网页信息并保存需要完整代码
  • ¥15 一分十不等功分器阻设计问题,请问这个56Ω怎么得到的
  • ¥15 (标签-matlab)
  • ¥100 求看看这个数学建模,有偿
  • ¥15 深度学习目标检测现在框架加注意力的创新可以投几区?
  • ¥15 PdfiumViewer pdf转图片
  • ¥15 利用Java连接API接口总是出问题
  • ¥15 请教一个关于镜头标定,棋盘格格子大小的问题(畸变测试)
  • ¥15 安装GroudingDINO RuntimeError: Error compiling objects for extension
  • ¥15 急!C++指针编写相关的问题