[larasmith]

Why does multiple "where condition" doesn't work as "AND"?

I used this guide: https://www.grocerycrud.com/enterprise/api-and-function-list/where-3#/!

I tried implementing this:

 

$crud->where([
    'tbluserlist.u_Type <> ?' => $_SESSION['U00'],
    'tbluserlist.u_Type <> ?' => $_SESSION['U01'],
    'tbluserlist.u_Type <> ?' => $_SESSION['U04']
]);

 

and the filtered result only displays records which are not equal  to:$_SESSION['U04'].

The records that are equal to $_SESSION['U00'] and $_SESSION['U01'] is still displayed.

[web-johnny]

Hello @larasmith, This is probably a bug? I will have it a look and I will let you know. Thanks Johnny

[larasmith]

Hello @larasmith, This is probably a bug? I will have it a look and I will let you know. Thanks Johnny

 

Thank you Sir Johnny!  :)

[larasmith]

Sir Johnny have you checked this one out?

 

I tried upgrading to 2.2.9 and still the same output.

[web-johnny]

Hello @larasmith,

 

Yes, I did try it and unfortunately, I can't reproduce that :( . I guess there is something to do with the custom model that you are using? Please have in mind that if you are using custom models, it is better to use the queries inside the custom model rather than using the methods of Grocery CRUD. For example in your case:

public function getList() {

 

        $order_by = $this->orderBy;

        $sorting = $this->sorting;

 

        // Add your custom queries (like join, where... e.t.c. here)

        $this->db->where('tbluserlist.u_Type !=', $_SESSION['U00']);

        $this->db->where('tbluserlist.u_Type !=', $_SESSION['U01']);

        $this->db->where('tbluserlist.u_Type !=', $_SESSION['U04']);

 

        if ($order_by !== null) {

            $this->db->order_by($order_by. " " . $sorting);

        }

 

        if (!empty($this->_filters)) {

            foreach ($this->_filters as $filter_name => $filter_value) {

                $this->db->like($filter_name, $filter_value);

            }

        }

 

        if (!empty($this->_filters_or)) {

            foreach ($this->_filters_or as $filter_name => $filter_value) {

                $this->db->or_like($filter_name, $filter_value);

            }

        }

 

        $this->db->limit($this->limit, ($this->limit * ($this->page - 1)));

        $results = $this->db->get($this->tableName)->result_array();

 

        return $results;

 

    }

[larasmith]

 

Hello @larasmith,

 

Yes, I did try it and unfortunately, I can't reproduce that :( . I guess there is something to do with the custom model that you are using? Please have in mind that if you are using custom models, it is better to use the queries inside the custom model rather than using the methods of Grocery CRUD. For example in your case:

public function getList() {

 

        $order_by = $this->orderBy;

        $sorting = $this->sorting;

 

        // Add your custom queries (like join, where... e.t.c. here)

        $this->db->where('tbluserlist.u_Type !=', $_SESSION['U00']);

        $this->db->where('tbluserlist.u_Type !=', $_SESSION['U01']);

        $this->db->where('tbluserlist.u_Type !=', $_SESSION['U04']);

 

        if ($order_by !== null) {

            $this->db->order_by($order_by. " " . $sorting);

        }

 

        if (!empty($this->_filters)) {

            foreach ($this->_filters as $filter_name => $filter_value) {

                $this->db->like($filter_name, $filter_value);

            }

        }

 

        if (!empty($this->_filters_or)) {

            foreach ($this->_filters_or as $filter_name => $filter_value) {

                $this->db->or_like($filter_name, $filter_value);

            }

        }

 

        $this->db->limit($this->limit, ($this->limit * ($this->page - 1)));

        $results = $this->db->get($this->tableName)->result_array();

 

        return $results;

 

    }

 

 

Hello Sir Johnny!

I am NOT using a custom model for this.

I even tried recreating a new installation and the same error occurs.  :(

What seems to be the problem?

 

I will be sending you via email a complete sample setup of this scenario

for you be able to see what is happening. Thank you for your assistance. 

[web-johnny]

Hello @larasmith,

 

After investigating on this it seems that this is not an issue with Grocery CRUD itself. So for example if you have a simple array:
 

$myArray = [    
    'tbluserlist.u_Type <> ?' => 'type1',
    'tbluserlist.u_Type <> ?' => 'type2',
    'tbluserlist.u_Type <> ?' => 'type3',
];

print_r($myArray);

You will only see this:

Array
(
    [tbluserlist.u_Type <> ?] => type3
)

In your case, you can simply do it with a full string. Make sure that you are starting and ending with parenthesis:

$crud->where("(tbluserlist.u_Type <> '". $_SESSION['U00'] . "' AND tbluserlist.u_Type <> '". $_SESSION['U01'] . "' AND tbluserlist.u_Type <> '". $_SESSION['U01'] . "')");

Please let me know if the above worked.

 

Thanks

Johnny

[larasmith]

 

Hello @larasmith,

 

After investigating on this it seems that this is not an issue with Grocery CRUD itself. So for example if you have a simple array:
 

$myArray = [    
    'tbluserlist.u_Type <> ?' => 'type1',
    'tbluserlist.u_Type <> ?' => 'type2',
    'tbluserlist.u_Type <> ?' => 'type3',
];

print_r($myArray);

You will only see this:

Array
(
    [tbluserlist.u_Type <> ?] => type3
)

In your case, you can simply do it with a full string. Make sure that you are starting and ending with parenthesis:

$crud->where("(tbluserlist.u_Type <> '". $_SESSION['U00'] . "' AND tbluserlist.u_Type <> '". $_SESSION['U01'] . "' AND tbluserlist.u_Type <> '". $_SESSION['U01'] . "')");

Please let me know if the above worked.

 

Thanks

Johnny

 

 

Yes Sir Johnny! This worked!  :lol:

 

I haven't thought that as an Associative Array,

I am using a single named key that's why it returns only one  value.  :rolleyes:

 

Thank you very much! :)

I hope it helps others facing the same issue.  :ph34r:

[cleberkr]

Hello Johnny, how are you doing today?

 

I have read this topic and I had the same problem and yes, the solution works.

 

While reading your documentation for WHERE fields on Grocery Crud Enterprise:

 

"1. You should already notice that you can also have a string input at where statement. Please have in mind that this is not a suggested way as it is easier to have SQL injections" (ref. https://www.grocerycrud.com/enterprise/api-and-function-list/where-3/)

 

I was a FREE VERSION grocery crud user and on the free version the WHERE statement is not handled by an array, so we can use several $crud->where('xxx'). On enterprise version it has been changed to an array so we can't filter two or more where clauses for the same columns using grocery crud classes, and you suggest us to use the solution that makes the application vulnerable to SQL injections.

 

I think this is something that need to be changed on the core.

 

What do you think?

 

Thank you very much.