sql - How can I filter out records by selected dates in PHP CodeIgniter?
Get the solution ↓↓↓
I'm trying to draw a graph with reports of clients and I would like to filter out multiple days of stores being closed, because of non-commercial day. I also have a filter date from and to.
if( $dateFrom != null ) :
$dateFrom .= ' 00:00:01';
$this->db->where('created >=', $dateFrom);
endif;
if( $dateTo != null ) :
$dateTo .= ' 23:59:59';
$this->db->where('created <=', $dateTo);
endif;
And I'm trying to figure out how to add an additional filter of multiple days. So I currently have something like this, but it does not work.
$this->db->where("created BETWEEN '2020-01-01 00:00:01' AND '2020-01-25 23:59:59'");
$this->db->where("created BETWEEN '2020-01-26 00:00:01' AND '2020-04-05 23:59:59'");
$this->db->where("created BETWEEN '2020-04-06 00:00:01' AND '2020-04-25 23:59:59'");
$this->db->where("created BETWEEN '2020-04-26 00:00:01' AND '2020-06-27 23:59:59'");
$this->db->where("created BETWEEN '2020-06-28 00:00:01' AND '2020-08-29 23:59:59'");
$this->db->where("created BETWEEN '2020-08-30 00:00:01' AND '2020-12-12 23:59:59'");
$this->db->where("created BETWEEN '2020-12-13 00:00:01' AND '2020-12-19 23:59:59'");
$this->db->where("created BETWEEN '2020-12-20 00:00:01' AND '2020-12-31 23:59:59'");
So I'm just trying to figure out how to filter out days additionally to a set filter from a date and to a date.
Is there maybe a workaround to this or any fix?
Thanks!
Answer
Solution:
You can put all your exceptional dates in an array like this:
$exceptions = ['2020-04-26', '2020-04-27']; // And so on
Then capture/define your date range:
$dateFrom = $some_source_of_data;
$dateTo = $some_source_of_data2;
Then:
$this->db->where("DATE(created) BETWEEN DATE('{$dateFrom}') AND DATE('{$dateTo}')");
$this->db->where("DATE(created) NOT IN (".implode(',', $exceptions).")");
Answer
Solution:
Figure this one out myself. I added "NOT BETWEEN" and then added the days that I wanted to filter out.
Share solution ↓
Additional Information:
Link To Answer People are also looking for solutions of the problem: sqlstate[hy000] [1698] access denied for user 'root'@'localhost'
Didn't find the answer?
Our community is visited by hundreds of web development professionals every day. Ask your question and get a quick answer for free.
Similar questions
Find the answer in similar questions on our website.
Write quick answer
Do you know the answer to this question? Write a quick response to it. With your help, we will make our community stronger.