0

sorry im not really expert on mysql.

i have table transactions(Trans_ID is PK but not Auto increment) but have transactionseq to trigger auto increment.

on table transactions i put trigger to get Trans_ID from table transactionseq

here below trigger code :

CREATE
DEFINER=`root`@`localhost`
TRIGGER `pos`.`tg_transactions_insert`
BEFORE INSERT ON `pos`.`transactions`
FOR EACH ROW
BEGIN
  INSERT INTO transactionseq VALUES (NULL);
  SET NEW.ID_Transaction = CONCAT('IDT', LPAD(LAST_INSERT_ID(), 3, '0'));
END$$

and i have php code for insert to my table here the code :

if($_SERVER['REQUEST_METHOD']=="POST"){
    $response = array();

    $ID_Employee = $_POST['ID_Employee'];
    $Name_Employee = $_POST['Name_Employee'];
    $ID_Customer = $_POST['ID_Customer'];
    $Table_Number = $_POST['Table_Number'];



    $insert = "INSERT INTO transactions
    (ID_Employee,Name_Employee,ID_Customer,Table_Number,ID_Outlet,CreatedBy_Transaction,CreatedDate_Transaction,Status_Transaction) 
    VALUES ('".$ID_Employee."','".$Name_Employee."','".$ID_Customer."','".$Table_Number."','1','".$ID_Employee."',NOW(),'OPEN')"; 

    if(mysqli_query($con, $insert)){
        $last_ID = mysqli_insert_id($con);
        $response['value']=1;
        $response['message']="Table berhasil dimulai".$last_ID;
        echo json_encode($response); 
    }
    else{
        $response['value']=0;
        $response['message']="Table gagal dimulai";
        echo json_encode($response); 
    }

}

since my table transaction have a trigger and no auto increment so i cant get the last Trans_ID by using mysqli_insert_id($con).

please your advice how to get my last insert Trans_ID?and will passing this Trans_ID to my flutter app.

Thank you

yunzen
  • 32,854
  • 11
  • 73
  • 106
Kart Zhai
  • 31
  • 1
  • 6
  • Why is it, that you don't have auto increment enabled? I'm just curious. And when you say 'the last Trans_ID', is it then the [max](https://www.w3schools.com/sql/func_mysql_max.asp) value? Or is it the one most recently added (the max of a `date`-column)? ... To be honest, I'm not sure if I understand your question properly. So if you don't get any answers, then others might feel the same way as me, - and then an elaboration or rephrasing of the question could be a good idea. :-) – Zeth Feb 06 '19 at 11:26
  • If you don't have `auto increment` then `LAST_INSERT_ID()` will not return anything – Nick Feb 06 '19 at 11:32
  • referse to this tutorial : https://stackoverflow.com/questions/17893988/how-to-make-mysql-table-primary-key-auto-increment-with-some-prefix.... – Kart Zhai Feb 06 '19 at 11:33
  • i have implement the same way how to have auto increment with prefix. currently i do insert to table trans and with trigger Trans_ID auto increment with prefix is work. but i need to get that last Trans_ID – Kart Zhai Feb 06 '19 at 11:35
  • i got an new option, i put a variable on mysql set @ asd = CONCAT('IDT', LPAD(LAST_INSERT_ID(), 3, '0')); how do i passing mysql variabel @ asd to receive on my PHP? what code i shoudl write?? – Kart Zhai Feb 06 '19 at 11:55
  • https://stackoverflow.com/questions/24436060/is-it-possible-to-pass-a-value-to-php-from-a-mysql-trigger – splash58 Feb 06 '19 at 12:03

0 Answers0