0

I'm attempting to filter members from the tables who will be celebrating their birth anniversary between two dates.

$start_date = '2021-05-30';
$end_date = '2021-07-03';

I want an array like below.

$date = array (
   array ('start' => '2021-05-30', 'end' => '2021-05-31'),
   array ('start' => '2021-06-01', 'end' => '2021-06-30'),
   array ('start' => '2021-07-01', 'end' => '2021-07-03')
);
mathew
  • 86
  • 7

1 Answers1

1

This was challenging, but I think I've found it.

The straight PHP/MYSQL:

$s = date("md", strtotime($start));
$e = date("md", strtotime($end));
$query = "SELECT registration.* 
FROM registration 
LEFT JOIN members 
ON members.mem_tbl_id = registration.mem_tbl_id
WHERE registration.ch_id = $ch_id
AND members.status = 'active'
AND CONCAT(LPAD(MONTH(registration.birth),2,'0'), LPAD(DAYOFMONTH(registration.birth),2,'0')) BETWEEN $s AND $e
ORDER BY registration.birth DESC";

Here's a stab at the CodeIgniter version

$s = date("md", strtotime($start));
$e = date("md", strtotime($end));
$this->db->select('*');
$this->db->from('registration');
$this->db->join('members', 'members.mem_tbl_id = registration.mem_tbl_id', 'left'); 
$this->db->where("CONCAT(LPAD(MONTH(registration.birth),2,'0'), LPAD(DAYOFMONTH(registration.birth),2,'0')) BETWEEN $s AND $e");
$this->db->where("registration.ch_id", $ch_id);
$this->db->where("members.status", "active");
$this->db->order_by("registration.birth DESC");
$result = $this->db->get()->result();
Kinglish
  • 23,358
  • 3
  • 22
  • 43
  • Hi sir, I want the members who celebrate their birth days from given dates. Above my model is working fine. but when i pass two date from two different months. my model giving empty result – mathew May 30 '21 at 05:47
  • My apologies. I misread your post and was wrong. I have updated my answer but need clarification on the `members.status` part. – Kinglish May 30 '21 at 06:07
  • No problem Sir. member.status having list of active, death, transfer, suspend. we need members who are active till the date – mathew May 30 '21 at 06:13
  • Ok, so members is a separate table right? or is there a field in registration called 'members_status'? If it is a separate table, what is the user_id (ch_id) called in members? – Kinglish May 30 '21 at 06:21
  • ch_id is similar to user id – mathew May 30 '21 at 06:27
  • You left this line out of your original question `$this->db->join('members', 'members.mem_tbl_id = registration.mem_tbl_id', 'left'); ` - that was important. I have updated my answer. I am not sure why you are getting empty results, but maybe this reorganized query will help. – Kinglish May 30 '21 at 06:38
  • $this->db->order_by("DATE_FORMAT(registration.birth, '%d')"); $this->db->where("DATE_FORMAT(registration.birth,'%m')", date("m", strtotime($start))); $this->db->where("DATE_FORMAT(registration.birth, '%d') >= ".date("d", strtotime($start))); $this->db->where("DATE_FORMAT(registration.birth, '%d') <= ".date("d", strtotime($end))); $this->db->join('members', 'members.mem_tbl_id = registration.mem_tbl_id', 'left'); $result->abdo = $this->db->get_where('registration', array('registration.ch_id' => $ch_id, 'members.status' => 'active'))->result() – mathew May 30 '21 at 06:44
  • my apologies. if i pass dates from same month return result. if i pass date from different monts returns empty result – mathew May 30 '21 at 06:46
  • Let us [continue this discussion in chat](https://chat.stackoverflow.com/rooms/233074/discussion-between-mathew-and-kinglish). – mathew May 30 '21 at 06:47