⚠ In case you've missed it, we have migrated to our new website, with a brand new forum. For more details about the migration you can read our blog post for website migration. This is an archived forum. ⚠

  •     

profile picture

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



larasmith

larasmith
  • profile picture
  • Member

Posted 08 June 2017 - 01:43 AM

Why does multiple "where condition" doesn't work as "AND"?
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

web-johnny
  • profile picture
  • Administrator
  • 1,166 posts

Posted 08 June 2017 - 03:58 AM

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

larasmith

larasmith
  • profile picture
  • Member

Posted 08 June 2017 - 05:01 AM

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

larasmith
  • profile picture
  • Member

Posted 11 June 2017 - 02:28 AM

Sir Johnny have you checked this one out?

 

I tried upgrading to 2.2.9 and still the same output.


web-johnny

web-johnny
  • profile picture
  • Administrator
  • 1,166 posts

Posted 12 June 2017 - 04:33 AM

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

larasmith
  • profile picture
  • Member

Posted 13 June 2017 - 01:07 AM



 

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

web-johnny
  • profile picture
  • Administrator
  • 1,166 posts

Posted 13 June 2017 - 13:27 PM

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

larasmith
  • profile picture
  • Member

Posted 14 June 2017 - 00:26 AM

 

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

cleberkr
  • profile picture
  • Member

Posted 09 November 2019 - 12:45 PM

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.