Running Where Clause with Like Not Working

Running Where Clause with Like Not Working - Printable Version

+- CodeIgniter Forums (https://forum.codeigniter.com)
+-- Forum: Using CodeIgniter (https://forum.codeigniter.com/forumdisplay.php?fid=5)
+--- Forum: General Help (https://forum.codeigniter.com/forumdisplay.php?fid=24)
+--- Thread: Running Where Clause with Like Not Working (/showthread.php?tid=87419)

Running Where Clause with Like Not Working - remesses_thegreat - 04-18-2023

Hi guys please help. I'm trying to run a search on one of the tables where i use multiple likes and a where statement. Code below. Where is ignored in this query and thus returning all the data Like searchText in that table. How do i make sure the where clause is not ignored

PHP Code:
//** */
    //** */
    //** SEARCH SERVICE PROVIDERS BY SEARCH KEYWORD */
    //** */
    //** */
    function getAllDriversLike($inputText,$companyId)
    {
        $db      = \Config\Database::connect();
        $builder = $db->table('contractors');
        $builder->where('ccompanyId', $companyId);
        $builder->orLike('contractor_id', $inputText);
        $builder->orLike('ccontact', $inputText);
        $builder->orLike('cfirst_name', $inputText);
        $builder->orLike('clast_name', $inputText);
 

        $query = $builder->get();
        
        return $query->getResult();
    } 

RE: Running Where Clause with Like Not Working - iRedds - 04-18-2023

https://codeigniter.com/user_guide/database/query_builder.html#query-grouping

RE: Running Where Clause with Like Not Working - remesses_thegreat - 04-18-2023

(04-18-2023, 05:29 AM)iRedds Wrote: https://codeigniter.com/user_guide/database/query_builder.html#query-grouping

Thanks for the response. I had a look at this. Wont the where clause return the exact match. Because was using like to search even if user inputed something similar. For example if user types 2 letters, all contractors with similar characters inputted would appear on the search box list. It does not have to be an exact match.

RE: Running Where Clause with Like Not Working - demyr - 04-18-2023

Try sth like this:

Code:
$builder = $db->table('contractors');

        $builder->like('contractor_id', $inputText);

        $builder->orLike('ccontact', $inputText);

        $builder->where('ccompanyId', $companyId);

use the "where" option in the end and start with like.

RE: Running Where Clause with Like Not Working - remesses_thegreat - 04-18-2023

(04-18-2023, 09:59 AM)demyr Wrote: Try sth like this:

Code:
$builder = $db->table('contractors'); $builder->like('contractor_id', $inputText); $builder->orLike('ccontact', $inputText); $builder->where('ccompanyId', $companyId);

use the "where" option in the end and start with like.

Thanks Demry. I tried this it still ignores the where clause irrespective of its position. This query returns contractor like it should but from all companies. I want it to search and return contractors associated with that company only.

RE: Running Where Clause with Like Not Working - SubrataJ - 04-18-2023

This is bizarre whenever you are using "OR" in a query, it's the best approach to group your query in a manner to avoid conflict.

PHP Code:
$builder = $db->table('contractors');
$builder->groupStart();
$builder->like('contractor_id', $inputText);
$builder->orLike('ccontact', $inputText);
$builder->groupEnd();
$builder->where('ccompanyId', $companyId); 

now your WHERE condition on companyId will be executed properly.

RE: Running Where Clause with Like Not Working - remesses_thegreat - 04-19-2023

(04-18-2023, 08:48 PM)SubrataJ Wrote: This is bizarre whenever you are using "OR" in a query, it's the best approach to group your query in a manner to avoid conflict.

PHP Code:
$builder = $db->table('contractors'); $builder->groupStart(); $builder->like('contractor_id', $inputText); $builder->orLike('ccontact', $inputText); $builder->groupEnd(); $builder->where('ccompanyId', $companyId);

now your WHERE condition on companyId will be executed properly.

Thank you so much this works as expected. Appreciated!!!!

RE: Running Where Clause with Like Not Working - SubrataJ - 04-19-2023

@remesses_thegreat No probs ehh, happy to help. I hope you get what you were doing wrong. and kindly mark it solved now

RE: Running Where Clause with Like Not Working - remesses_thegreat - 04-21-2023

(04-19-2023, 12:51 AM)SubrataJ Wrote: @remesses_thegreat No probs ehh, happy to help. I hope you get what you were doing wrong. and kindly mark it solved now

Yes i now understand. Its a great learning curve. Had never found myself writing queries with grouping. Now i will apply this when necessary. I marked as solved