0

I need to get the difference of two dates and update the difference of the dates in the database.

model

public function approve($id, $leave_type, $leave_start, $leave_end) { $diffDays= abs($leave_end - $leave_start); $this->db->set('leave_days', $diffDays); $this->db->set('status', Approved); $this->db->where('id', $id); $this->db->update('leave'); return $this->db->affected_rows() > 0; }

abs($leave_end - $leave_start); is not working. But when a hardcoded value is set to diffDays, it works fine.

Details about my schema:

CREATE TABLE leave ( id int(11) NOT NULL, user_name text NOT NULL, user_id int(11) NOT NULL, leave_type varchar(255) NOT NULL, leave_start date NOT NULL, leave_end date NOT NULL, leave_days int(11) NOT NULL, status text NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1; INSERT INTO leave (id, user_name, user_id, leave_type, leave_start, leave_end, leave_days, status) VALUES (121, 'Harry Potter', 6, 'Casual', '2018-06-28', '2018-06-29', 0, 'Approved'), (122, 'Harry Potter', 6, 'Annual', '2018-06-22', '2018-06-25', 0, 'Approved'), (123, 'Harry Potter', 6, 'Casual', '2018-06-23', '2018-06-25', 4, 'Approved');
Improve this question

4 Answers 4

Reset to default
1

Adjust the date format to the format you are using.

 $leave_start = DateTime::createFromFormat('Y-m-d', $leave_start); $leave_end = DateTime::createFromFormat('Y-m-d', $leave_end); $diffDays = $leave_end->diff($leave_start)->format("%a"); $this->db->where('id', $id); $this->db->update('leave', array('leave_days' => $diffDays));
Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

1 
Please try this for getting a difference between two dates. $date1=date_create($leave_start); $date2=date_create($leave_end); $diff=date_diff($date1,$date2); $days=$diff->format("%a"); $id = 1; $this->db->set('leave_days', $days); $this->db->where('id', $id); $this->db->update('leave');
Improve this answer

11 Comments

what? days are not got it.
Please describe more about your problem.
In my code above the status is set correctly but the diffDays is not set. I updated my above code. When I give a definite number for diffDays it works. But here 'abs($leave_end - $leave_start); ' is not working.
you have only update date difference in DB so why are you using this abs() function in your code.
Is there any other alternative to get the difference of dates other than abs() function
|
0

I recommend "levelling up" your database schema instead of making your codebase perform this calculation. This will make your query much cleaner.

Reconcile any leave_start and leave_end dates which are written in the opposite field (logically). Apply a constraints to prevent inappropriate values in fields. Calculate the leave_days automatically on-write.

class Migration_Autocalculate_leave_days extends CI_Migration { public function up() { $this->db->trans_start(); // Reconcile misappropriated dates $this->db->query(" UPDATE `leave` SET leave_start = (@tmp := leave_start), leave_start = leave_end, leave_end = @tmp WHERE leave_start IS NOT NULL AND leave_end IS NOT NULL AND leave_start > leave_end "); // Make space for the new version of the column $this->db->query("ALTER TABLE `leave` DROP COLUMN leave_days"); // Disallow leave end being earlier than leave start $this->db->query(" ALTER TABLE `leave` ADD CONSTRAINT chk_leave_days_nonneg CHECK (leave_start IS NULL OR leave_end IS NULL OR leave_end >= leave_start) "); // Disallow approval if either date is missing $this->db->query(" ALTER TABLE `leave` ADD CONSTRAINT chk_cannot_approve_null_dates CHECK ( status <> 'Approved' OR (leave_start IS NOT NULL AND leave_end IS NOT NULL) ) "); // Re-add the leave_days column as an auto-populated field // DATEDIFF() will return NULL if either value is NULL $this->db->query(" ALTER TABLE `leave` ADD COLUMN leave_days INT AS (DATEDIFF(leave_end, leave_start)) STORED "); $this->db->trans_complete(); } public function down() { $this->db->trans_start(); $this->db->query("ALTER TABLE `leave` DROP CHECK chk_leave_days_nonneg"); $this->db->query("ALTER TABLE `leave` DROP COLUMN leave_days"); $this->db->query("ALTER TABLE `leave` ADD COLUMN leave_days INT DEFAULT NULL"); $this->db->query(" UPDATE `leave` SET leave_days = DATEDIFF(leave_end, leave_start) "); $this->db->trans_complete(); } }

Here is the SQLize sandbox that I was using to test my migration SQL.

After you have run this migration, the responsibility to maintain leave_days is removed from INSERT/UPDATE queries.

// $record shouldn't have an id or leave_days element public function request(array $record): int { unset($record['id'], $record['leave_days'], $record['status']); // assuming status has a default value of Requested $this->db->insert('leave', $record); $id = $this->db->insert_id(); // potentially log change return $id; } 
// $changedData shouldn't have an id or leave_days element public function modify(int $id, array $changedData): bool { unset($record['id'], $record['leave_days']); $wasChanged = $this->db->update('leave', ['status' => 'Requested'] + $changedData, ['id' => $id]) && $this->db->affected_rows(); // potentially log change return $wasChanged; } 
// Method shouldn't have anything to do with calculating leave_days // The database will refuse the update if either date is missing public function approve(int $id): bool { $wasChanged = $this->db->update('leave', ['status' => 'Approved'], ['id' => $id, 'status' => 'Requested']) && $this->db->affected_rows(); // potentially log change return $wasChanged; }

p.s. I recommend removing user_name from your leave table; that data point should be fetched from a related table (user) so that the username is always up-to-date regardless of the age of the leave record.

Improve this answer

Comments

-1

$leave_start = '01-01-2018'; $leave_end = '10-01-2018';

$id = 1; $diffDays = abs($leave_end - $leave_start); $this->db->set('leave_days', $diffDays); $this->db->where('id', $id); $this->db->update('leave');

make sure that your $diffDays has some value because it worked for me.

Improve this answer

1 Comment

Have you printed your variable "$diffDays" ,I think it has empty value ?

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.