CODEWITHSUNDEEP
phpmysqlcodeigniter
S. M. Shahinul Islam
S. M. Shahinul Islam

Reputation: 2800

Inserting rows to a table depending on date

This is my function to insert new rows to a table, depending on the date. I want to avoid duplication of rows if one already exists. This function basically inserts November, 2014 as mwf_month, so mwf_student_id and mwf_month pairs are unique for the row. What modification should I do to avoid this kind of duplication?

public function month_wise_due($grade_due, $new_due, $id, $remaining) {

        $now1 = time();
        $now = date('F, Y', $now1);

        $store = array(
            'mwf_month' => $now,
            'mwf_previous' => $remaining,
            'mwf_due' => $grade_due,
            'total_due' => $new_due,
            'mwf_student_id' => $id,
            'mwf_pay_day' => 'Not Yet Paid',
            'mwf_payment' => 0,
            'mwf_diff' => $new_due
        );

        $this->db->trans_start();
        $this->db->insert('mwf', $store);
        $this->db->trans_complete();
    }

Upvotes: 0

Views: 62

Answers (2)

S. M. Shahinul Islam
S. M. Shahinul Islam

Reputation: 2800

I have found a solution from some where else without altering the table, it seems good to me.

$now1 = time();
        $now = date('F, Y', $now1);

        $row_exists = "SELECT * FROM mwf WHERE mwf_month = '" . $now."' AND mwf_student_id = '" . $id."' LIMIT 1";

        if ( ! $this->db->simple_query($row_exists)) {
        $store = array(
            'mwf_month' => $now,
            'mwf_previous' => $remaining,
            'mwf_due' => $grade_due,
            'total_due' => $new_due,
            'mwf_student_id' => $id,
            'mwf_pay_day' => 'Not Yet Paid',
            'mwf_payment' => 0,
            'mwf_diff' => $new_due
        );

        $this->db->trans_start();
        $this->db->insert('mwf', $store);
        $this->db->trans_complete();
        }

Upvotes: 0

David162795
David162795

Reputation: 1866

The right way is to update your database table by adding "unique key" on two fields "mwf_month+mwf_student_id". The SQL command to do it is:

ALTER TABLE `mwf` ADD UNIQUE `unique_month_student`(mwf_month,mwf_student_id);

Then, possible duplicity would end with SQL error you can catch. You can also suppress the exception with 'ignore' statement or use 'replace' method instead of 'insert'.

If you don't have needed privileges on the table, you would need to simply check whether the pair exist with separate sql call before inserting new record.

Upvotes: 4

Related Questions