1

I am trying to fetch a row of data in mysql after inserting a row of data in mysql table using

SELECT * FROM incoming_sms WHERE id = last_insert_id(); but this returns an empty set of results although i have indeed inserted a new row and my table contains data.

How can i make my query return some data?.

user3272483
  • 408
  • 3
  • 13
  • [LAST_INSERT_ID() MySQL][1] [1]: http://stackoverflow.com/questions/3837990/last-insert-id-mysql Try this link – Saechel Aug 21 '14 at 04:42

1 Answers1

1

The normative pattern is to execute a SELECT LAST_INSERT_ID() statement immediately following the successful insert statement, from the same session that issue the INSERT statement.

There are several possible reasons that what you are attempting is not working. I've not seen the LAST_INSERT_ID() function used in a SELECT statement like you have it; I'm not sure if the behavior is defined. I've just never seen it used like that.

The normal pattern is:

INSERT INTO mytable (col) VALUES (val);
-- check if statement succeeded; if it successfully inserted a row
SELECT LAST_INSERT_ID();
-- retrieve returned value

Then use the retrieved value in subsequent processing.

Note (again) that the reference to LAST_INSERT_ID() must be made from the same session that performed the INSERT, and should be performed before any other SQL statement is issued.

http://dev.mysql.com/doc/refman/5.5/en/information-functions.html#function_last-insert-id

spencer7593
  • 106,611
  • 15
  • 112
  • 140
  • If i am using `new.id`,does the same rule apply for the session and before any other sql statement ? – user3272483 Aug 21 '14 at 04:56
  • @user3272483: No. In the context of an `AFTER INSERT` trigger, the `NEW.id` identifier is a reference to the value that was stored in the id column for the row. You can perform other SQL statements in the trigger body, the `NEW.id` identifier still references the value stored in the id column of the current row. – spencer7593 Aug 21 '14 at 04:59
  • @user3272383: The original question omits any mention of a trigger. In an `AFTER INSERT` trigger, the normal pattern for retrieving the value assigned to an auto_increment column is to reference `NEW.id` (or, NEW. whatever the name of the auto_increment column is.) – spencer7593 Aug 22 '14 at 01:11