how to retrieve id of record that is inserted by MySQL event

Question

Following is my code:

$schedule = $this->db->query("CREATE EVENT ".$data['id']." ON SCHEDULE 
EVERY 1 MONTH ON COMPLETION PRESERVE ENABLE DO INSERT INTO oops_invoice 
(id, prefix, number, bill, account_id, order_delivery_id, date, staff_id, 
customer_note, recurrence, status, itp, utp) VALUES ('', '".$data['prefix']."', 
'".$data['number']."', '".$data['bill']."', '".$data['account_id']."', NULL, 
'".$data['date']."', NULL, '".$data['customer_note']."', '".$data['recurrence']."', 
'Draft', '".$data['itp']."', CURRENT_TIMESTAMP)");

$this->db->query("SET GLOBAL event_scheduler =  'ON'");

I stuck with id that how to retrieve id od row inserted by MySQL event..

i have tried following but does not worked when table is empty...

following code works fine except one case when table is empty....at that time it return nothing so i have been stuck...

this one is not same as we insert simple data this one inserted using MySQL event so $this->db->insert_id(); does not working......

$last = $this->db->query('SELECT MAX(id) AS `maxid` FROM `oops_invoice`')->row()->maxid;

Well, describe the issue. "Does not work" is a little unspecific. — mario, Apr 13 '15 at 12:35
Why do you need the ID of the inserted row if the table is empty? — TiiJ7, Apr 13 '15 at 12:41
possible duplicate of [CodeIgniter activerecord, retrieve last insert id?](http://stackoverflow.com/questions/1985967/codeigniter-activerecord-retrieve-last-insert-id) — Matheno, Apr 13 '15 at 12:41
oh man $this->db->insert_id(); does not working because data is inserted by MySQL event — Sagar Naliyapara, Apr 13 '15 at 12:43
@TiiJ7 i want it after inserting data using event creation and $this->db->insert_id(); does not working in that case..... — Sagar Naliyapara, Apr 13 '15 at 12:46
@SagarNaliyapara: Is the `id` column an auto_increment field? — Ravinder Reddy, Apr 13 '15 at 12:48
@SagarNaliyapara: When no data found and if you want to return any default you can try with `coalesce( max( id ), your_default_value_here )` — Ravinder Reddy, Apr 13 '15 at 12:59
@Ravinder there is data inserted when event created but does not return id using function may be inserted after complition of execution so question is that is mysql event inserts data at that time or after that...?? if after then how to insert at creation of it — Sagar Naliyapara, Apr 13 '15 at 13:02
@SagarNaliyapara: Insertion occurs when `event` is being executed but not at the time of event creation. You can also include `starts` clause for `event`. Example: `ON SCHEDULE EVERY 1 MONTH STARTS DATE_FORMAT(NOW(),'%Y-%m-%d %H:55:00')` — Ravinder Reddy, Apr 13 '15 at 13:08

score 1 · Accepted Answer · answered Apr 25 '15 at 13:00

No requirement of retrieving id, You can start event before 1 or 2 minutes of it's creation like this... it will eliminate 2 insertion in table.

CREATE EVENT ".$data['id']." ON SCHEDULE EVERY 1 MONTH STARTS DATE_SUB(NOW(),INTERVAL '0:01' HOUR_MINUTE) ON COMPLETION PRESERVE ENABLE DO INSERT INTO oops_invoice (id, prefix, number, bill, account_id, order_delivery_id, date, staff_id, customer_note, recurrence, status, itp, utp) VALUES ('', '".$data['prefix']."', '".$data['number']."', '".$data['bill']."', '".$data['account_id']."', NULL, '".$data['date']."', NULL, '".$data['customer_note']."', '".$data['recurrence']."', 'Draft', '".$data['itp']."', CURRENT_TIMESTAMP)");
    $this->db->query("SET GLOBAL event_scheduler =  'ON'");

score 0 · Answer 2 · answered Apr 13 '15 at 12:37

0

$this->db->lastInsertId()

must be your solution

answered Apr 13 '15 at 12:37

DDA

161
6

Don't you try to get the last id of a creation event in your database ? – DDA Apr 13 '15 at 12:50

how to retrieve id of record that is inserted by MySQL event

2 Answers2