Is there any way to remove where clause from query using logic?

sql conditional where clause based on parameter
how to add case statement in where clause in sql
conditional where clause in sql server stored procedure
sql change where clause based on condition
nested case statement in oracle where clause with examples
conditional statement in where clause sql
sql case statement in where clause with parameters
conditional where clause sql

I am using CodeIgniter. I am getting the data from the controller.

Model code

 $select_filter_status=$parameters['select_filter_status'];
 $select_filter_action= $parameters['select_filter_action'];

if(($select_filter_status=='') && ($select_filter_action=='')){
       $where_2=" "; // I don't have any condition here. I have to display all the records

}
else if(($select_filter_status=='') || ($select_filter_action=='')){

        if ($select_filter_action=='') {
            $where_2="product_order.o_order_status=".$select_filter_status;
      }
      else{
            $where_2="product_order.o_order_status_confirm=".$select_filter_action;
      }
}
else{
        $where_2="product_order.o_order_status=".$select_filter_status." and product_order.o_order_status_confirm=".$select_filter_action;
  }




$this->db->select("*");
$this->db->from('tbl_customer');
$this->db->join('tbl_customer_billing', 'tbl_customer.cust_id=tbl_customer_billing.c_b_id', 'LEFT');
$this->db->join('tbl_customer_shipping', 'tbl_customer.cust_id=tbl_customer_shipping.cust_id', 'LEFT');
$this->db->where($com_report);
$this->db->where($where_2); // I want to remove this where clause when there is empty status and action
$this->db->order_by('product_order.o_date_of_added','DESC');
$query = $this->db->get();
$result = $query->result();

My issue is, I have two where clause in the query and I have to remove the second where condition if $select_filter_status and $select_filter_actionare found empty.

Is there any way to remove where clause or I have to use something like

if(){
//with two where clause
}
else{
//with one where clasue
}

you have to pass the where_2 query in your main query.. concatinate the variable $where_2 in your $com_report query

if(($select_filter_status=='') && ($select_filter_action=='')){
       $where_2=" "; // I don't have any condition here. I have to display all the records

}
else if(($select_filter_status=='') || ($select_filter_action=='')){

        if ($select_filter_action=='') {
            $where_2="and product_order.o_order_status=".$select_filter_status;
      }
      else{
            $where_2="and product_order.o_order_status_confirm=".$select_filter_action;
      }
}
else{
        $where_2="and product_order.o_order_status=".$select_filter_status." and product_order.o_order_status_confirm=".$select_filter_action;
  }

NOW

$com_report ='your where statements ' .$where_2 ;

pass it to the query builder now

$this->db->where($com_report);

i hope it will work for you

Using the SQL WHERE clause, In this article we will see how the WHERE clause can be used to select The WHERE clause is an optional clause in the following SQL statements: Logical operators combine with the comparison operators outlined above to give the be used in SELECT, UPDATE and DELETE statements to filter the rows that the query� Random Thoughts & Cartesian Products with Microsoft SQL Server. SQL WHERE clauses: Avoid CASE, use Boolean logic. Fri Nov 14, 2003by Jeff Smith in t-sql, techniques, efficiency. As some of you may know, I recommend to avoid using CASE expressions in the WHERE clause of a query to express conditional logic.

You can simply put only one if condition here like this. It will work for you.

if (!empty($where_2){
   $this->db->where($where_2);
}

if $where_2 is not empty it will execute second where condition, if it's empty it will ignore the second where.

Or you can concatenate the query string like this to use only one query clause

$com_report = $com_report . " " . $where_2;

Now no need for second where clause Hope it will work for you

Remove the where clause when the value supplied is null, Possible duplicate of How can I use optional parameters in a T-SQL stored procedure? Just use normal logic to handle it. Edit : Added the option clause in, so that the query plan is optimized for an unknown value I would choose this option first before going down the route of an option (recompile)� DISTINCT is useful in certain circumstances, but it has drawback that it can increase load on the query engine to perform the sort (since it needs to compare the result set to itself to remove duplicates) Below are alternate solutions : 1. Remove Duplicates Using Row_Number.

Yes, You can add where clause based on condition in Codeigniter, Like this,

$this->db->select("*");
$this->db->from('tbl_customer');
$this->db->join('tbl_customer_billing', 'tbl_customer.cust_id=tbl_customer_billing.c_b_id', 'LEFT');
$this->db->join('tbl_customer_shipping', 'tbl_customer.cust_id=tbl_customer_shipping.cust_id', 'LEFT');
$this->db->where($com_report);
if(!empty($select_filter_status) AND !empty($select_filter_action)){
    $this->db->where($where_2); 
}


$this->db->order_by('product_order.o_date_of_added','DESC');
$query = $this->db->get();
$result = $query->result();

Can I have a CASE Statement in the WHERE Clause?, CASE can be used in any statement or clause that allows a valid expression. For example, you can use CASE in statements such as SELECT, UPDATE, DELETE � 5. Remove The DISTINCT Clause if not required. The DISTINCT clause is used to obtain distinct results from a query by eliminating the duplicates. However, this increases the execution time of the query as all the duplicate fields are grouped together. So, it is better to avoid the DISTINCT clause as much as possible.

SQL DELETE Statement, This operation is carried out with the SQL DELETE statement. In the post about INSERT, under employee number 9-9-9-9-0-3 we added some information What should happen when we run this query is that only the row with employee� An example of this is the redundant conditions in joins or cases where you abuse the HAVING clause, like in the above examples, in which you query the database by performing a function and then calling another function, or you use logic that contains loops, conditions, User Defined Functions (UDFs), cursors, … to get the final result. In this

SQLite WHERE - Filter Rows in a Result Set, This tutorial shows you how to use SQLite WHERE clause to filter rows in a result set you add a WHERE clause to the SELECT statement to filter rows returned by the query. you can use the WHERE clause in the UPDATE and DELETE statements. Logical operators allow you to test the truth of some expressions. A where clause generally has many special characters in it such as =, >,<, and spaces. The shell command prompt in Unix and Windows is not going to like those characters very much. They will have to be escaped and how to do that is OS dependent. What I prefer to do is to always use a PARFILE with the QUERY option.

MySQL WHERE Clause with Examples, We looked at how to query data from a database using the SELECT statement in the WHERE clause combined with - AND LOGICAL Operator with the OR operator, is only executed if any or the entire specified filter criteria is met. the number of rows affected by a SELECT, UPDATE or DELETE query. I'm trying to make my LINQ query so that all 4 values are in my WHERE clause and the results drilldown each time the user enters data in another textbox. The query needs to be able to handle empty string or "" For example, there are 500 registered users. The user enters Smith in the LastName textbox and of the 500, there are 20 Smith users.

Comments
  • Give me some time to check
  • alright :) btw you can check your query by $q = $this->db->last_query(); and print_r($q);exit; to print it on the screen to see whats wrong with the query :)
  • Yes, your answer is working for me. Thanks for the help.
  • Give me some time to check
  • Your answer is also working for me. upvote from my side.
  • Can you help me in this link stackoverflow.com/questions/61745217/…
  • I thought I can't use like this because of wrong format.Give me some time to check