How to select multiple where conditions using codeigniter?

Question

my table

This is my table i have to display the data using multiple where conditions. First i select using dms_expire_date,for this i got answer and i am using dms_doc_name in where conditions but i didn't got correct result.

I have tired so far.

]

See this picture if i use "or" in "where" i got testie not within this date.but if i use "and" in "where" i didn't got result for dms_expiry_date

See this picture

i don't know how to do this.please help to solve this problem.

SELECT * FROM `dms_document` WHERE dms_expire_date BETWEEN '2020-05-01' AND '2020-07-16' AND dms_doc_name = '' OR dms_category_id = '' OR dms_subcategory_id=''

I have tired in array also but i didn't result.

$this->db->select('*');
$this->db->join('dms_category as C', 'C.dms_category_id = D.dms_category_id', 'left outer');
$this->db->join('dms_sub_category as S', 'S.dms_subcategory_id = D.dms_subcategory_id', 'left outer');
$this->db->where('dms_expire_date >=', $newstart);
$this->db->where('dms_expire_date <=', $newend);
$this->db->where(array('dms_doc_name' =>$docname,'D.dms_category_id'=>$category,'D.dms_subcategory_id'=>$subcategory));
$data['documents']= $this->db->get('dms_document as D')->result_array();

most likely issue with brackets (),, but what result do you expect tho? what do you want to see when you have `dms_doc_name = ''`, `dms_category_id = ''` , `dms_subcategory_id = ''` — Boyke Ferdinandes, Jul 16 '20 at 03:11
if i give testie in doc_name within given dms_expiriy date and the result should be come — Jacky, Jul 16 '20 at 03:13
If i give expriy_date the result are coming correctly and if i give doc_name ='testie' the tesite result only come and if i give dms-category_id only that id should come and etc — Jacky, Jul 16 '20 at 03:19
@BoykeFerdinandes see 2nd pic i give testie in where conditon only testie row should come extra 2 rows are displaying and if i give category_id that category_id only displayed and so far.I think you can understand — Jacky, Jul 16 '20 at 03:21
on 2nd pic, you want testie record to come out as result? note that testie record has expiry_date = 2020-04-01 which is outside of your date range? — Boyke Ferdinandes, Jul 16 '20 at 03:27
To clarify the logic you actually want, I'd recommend adding parenthesis around the conditionals like `... WHERE x AND y AND (z OR a OR b)` vs `... WHERE x AND (y AND z) OR (a OR b)` which are different queries. — WOUNDEDStevenJones, Jul 16 '20 at 04:11

Boyke Ferdinandes · Answer 1 · 2020-07-16T03:49:27.417

0

try

SELECT * 
FROM dms_document
WHERE dms_expire_date BETWEEN $newstart AND $newend
AND CASE WHEN $docname IS NULL THEN 1 = 1 ELSE dms_doc_name = $docname END
AND CASE WHEN $category IS NULL THEN 1 = 1 ELSE dms_category_id = $category END 
AND CASE WHEN $subcategory IS NULL THEN 1 = 1 ELSE dms_subcategory_id = $subcategory END

--

edited. waiting for clearance.

if I get what you mean correctly, it is problem with understanding AND, OR, and the priority with brackets ()

-- if anyone want to use the fiddle, I have created the sample data for this issue

edited Jul 16 '20 at 03:49

answered Jul 16 '20 at 03:32

Boyke Ferdinandes

415
2
7

i give one example pic – Jacky Jul 16 '20 at 03:36
see the excel image that last column is result but it is dms_doc_tag – Jacky Jul 16 '20 at 03:37
last column? that column with 1,1,2,0,2,1,2,1 values? what do those values suppose to mean regarding the result you expected – Boyke Ferdinandes Jul 16 '20 at 03:43
let's wait for other helper, maybe they can understand your point better than I do. – Boyke Ferdinandes Jul 16 '20 at 03:44
you didn;t understad okay where i your answer – Jacky Jul 16 '20 at 03:44
i got error You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'IS NULL THEN 1 = 1 ELSE dms_doc_name = END AND CASE WHEN IS NULL THEN 1 = 1 E' at line 4 – Jacky Jul 16 '20 at 03:57
by $newstart, $newend, $docname, $category, $subcategory. replace them with your variables/parameters values – Boyke Ferdinandes Jul 16 '20 at 04:04
i have tired in application not in phpadmin – Jacky Jul 16 '20 at 04:14

WOUNDEDStevenJones · Answer 2 · 2020-07-16T13:36:21.170

You can write OR using CodeIgniter's or_where() (v3 docs) or orWhere() (v4 docs).

//your initial query
$sql = "SELECT
            *
        FROM
            `dms_document`
        WHERE
            dms_expire_date BETWEEN '2020-05-01' AND '2020-07-16'
            AND dms_doc_name = ''
            OR dms_category_id = ''
            OR dms_subcategory_id=''";

//building the same query using CI
$this->db->select('*');
$this->db->from('dms_document');
$this->db->where('dms_expire_date >=', '2020-05-01');
$this->db->where('dms_expire_date <=', '2020-07-16');
$this->db->where('dms_doc_name', '');
$this->db->or_where('dms_category_id', '');
$this->db->or_where('dms_subcategory_id', '');

You can confirm the query this generates by running $this->db->_compile_select(); or $this->db->last_query(); (https://stackoverflow.com/a/6145021/1499877 for reference).

echo '<pre>';
var_dump($sql);
var_dump($this->db->_compile_select());
echo '</pre>';
die();

I've updated this answer to match the query you initially posted. Can you please clarify if you are trying to replicate that exact query or if you're trying to do something else? I'm not sure where your JOINs came from in CI because your initial query doesn't have them. — WOUNDEDStevenJones, Jul 16 '20 at 13:39

How to select multiple where conditions using codeigniter?

2 Answers2