0

How to get the next id in mysql to insert it in the table ?

INSERT INTO payments (date, item, method, payment_code)
VALUES (NOW(), '1 Month', 'paypal', CONCAT("chaminda", id))
0

Here is an approach for this.

Step 1: Insert without payment_code.

Step 2: Get mysql_insert_id from STEP 1.

Step 3: Update payment_code with where condition as mysql_insert_id.

Code Snippet in Php: The commands might have to be corrected.

1.    mysql_query("INSERT INTO payments (date, item, method, payment_code)
    VALUES (NOW(), '1 Month', 'paypal')");

2.     printf("Last inserted record has id %d\n", mysql_insert_id());

3.     mysql_query(“UPDATE payments set payment_code=CONCAT("chaminda",mysql_insert_id()) where id=mysql_insert_id());
  • I need SQL query without any php code. – Chaminda Bandara Jun 9 '17 at 10:06
0

try this :

INSERT INTO payments (date, item, method, payment_code)
VALUES (NOW(), '1 Month', 'paypal', NULL);

UPDATE payments
set payment_code=CONCAT("chaminda",LAST_INSERT_ID())
where id = LAST_INSERT_ID();
  • 2
    I think last_insert_id() returns whichever ID was issued last, not necessary from the given table. And it has other problems, too, read: dev.mysql.com/doc/refman/5.7/en/…. Also, always explain why and how your solution works. – dezso Jun 9 '17 at 11:15

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.