Results 1 to 5 of 5
  1. #1
    Join Date
    Feb 2004
    Location
    inida
    Posts
    62

    Thumbs up Unanswered: Last_insert_id() ??

    Plz find the following set of operations. Can anybody tell me how does LAST_INSERT_ID() works in this scenario?Im confused with the output im getting.thanx in advance.

    CREATE TABLE person (
    id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
    name CHAR(60) NOT NULL,
    PRIMARY KEY (id)
    );

    CREATE TABLE shirt (
    id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
    style ENUM('t-shirt', 'polo', 'dress') NOT NULL,
    color ENUM('red', 'blue', 'orange', 'white', 'black') NOT NULL,
    owner SMALLINT UNSIGNED NOT NULL REFERENCES person(id),
    PRIMARY KEY (id)
    );

    INSERT INTO person VALUES (NULL, 'Antonio Paz');

    mysql> select * from person;
    +----+-------------+
    | id | name |
    +----+-------------+
    | 1 | Antonio Paz |
    +----+-------------+
    1 row in set (0.00 sec)


    mysql> select * from shirt;
    +----+---------+-------+-------+
    | id | style | color | owner |
    +----+---------+-------+-------+
    | 1 | polo | blue | 1 |
    | 2 | dress | white | 1 |
    | 3 | t-shirt | blue | 2 |
    +----+---------+-------+-------+
    3 rows in set (0.00 sec)


    INSERT INTO shirt VALUES
    (NULL, 'polo', 'blue', LAST_INSERT_ID()),
    (NULL, 'dress', 'white', LAST_INSERT_ID()),
    (NULL, 't-shirt', 'blue', LAST_INSERT_ID());

    INSERT INTO person VALUES (NULL, 'Lilliana Angelovska');

    INSERT INTO shirt VALUES
    (NULL, 'dress', 'orange', LAST_INSERT_ID()),
    (NULL, 'polo', 'red', LAST_INSERT_ID()),
    (NULL, 'dress', 'blue', LAST_INSERT_ID()),
    (NULL, 't-shirt', 'white', LAST_INSERT_ID());



    mysql> select * from person;
    +----+---------------------+
    | id | name |
    +----+---------------------+
    | 1 | Antonio Paz |
    | 2 | Lilliana Angelovska |
    +----+---------------------+
    2 rows in set (0.00 sec)


    mysql> SELECT * FROM shirt;
    +----+---------+--------+-------+
    | id | style | color | owner |
    +----+---------+--------+-------+
    | 1 | polo | blue | 1 |
    | 2 | dress | white | 1 |
    | 3 | t-shirt | blue | 2 |
    | 4 | dress | orange | 2 |
    | 5 | polo | red | 4 |
    | 6 | dress | blue | 5 |
    | 7 | t-shirt | white | 6 |
    +----+---------+--------+-------+
    7 rows in set (0.00 sec)

  2. #2
    Join Date
    Mar 2004
    Posts
    480
    I tested what you have here, the shirts are listed as id of 1 for the first three and id of 2 for the final four. Try truncating your table and doing those inserts into the shirt table again.

  3. #3
    Join Date
    Feb 2004
    Location
    inida
    Posts
    62
    thanx
    I did as u said.
    But still my output is same.I tried with droping that tables and doing the insertion again.No change to the output.

    Im using mySQL4.0..

  4. #4
    Join Date
    Jul 2003
    Posts
    73
    LAST_INSERT_ID() returns to you the id of the last AUTO_INCREMENT column you updated - link. It looks like this variable is updated even when you insert more than one record in the INSERT statement.

    As both of your tables have AUTO_INCREMENTS on them - you are creating a new LAST_INSERT_ID() for each row you inserted. For example:

    Code:
    INSERT INTO person VALUES (NULL, 'Antonio Paz');
    You have added a new person - with the id being 1. LAST_INSERT_ID() will now return 1.
    Code:
    INSERT INTO shirt VALUES
    (NULL, 'polo', 'blue', LAST_INSERT_ID()),
    (NULL, 'dress', 'white', LAST_INSERT_ID()),
    (NULL, 't-shirt', 'blue', LAST_INSERT_ID());
    When you add the first column - LAST_INSERT_ID() is 1 - and it will set this as your owner. But as you've just added a shirt row - the LAST_INSERT_ID() will now return 1 (the ID of the shirt you just added).
    The second shirt you add is also getting 1 from LAST_INSERT_ID() - but as this new record has a key of 2 - LAST_INSERT_ID() will now return 2 for the next insert.
    Therefore - when you are adding the third shirt - it owner is listed as 2.

    This pattern is followed for the next set of inserts you do (the first owner is 2 - as you've just added a new person with ID 2. Then they increment for each shirt added - 4 to 6)

    You need to store the id of the person you've added - then use it as the owner for each shirt you then insert. I know how to do this with PHP - but I haven't used MySQL variables (if they actually exist).

    Good luck - I hope I've explained it well enough.
    Joel Dixon
    Analyst Programmer
    Melbourne, Australia

  5. #5
    Join Date
    Feb 2004
    Location
    inida
    Posts
    62
    ThankU very much Mr.Dixon for ur explanation.

    And as u said i used variables and resolved the problem.
    Here is vat i did and my output.

    CREATE TABLE person (
    id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
    name CHAR(60) NOT NULL,
    PRIMARY KEY (id)
    );

    CREATE TABLE shirt (
    id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
    style ENUM('t-shirt', 'polo', 'dress') NOT NULL,
    color ENUM('red', 'blue', 'orange', 'white', 'black') NOT NULL,
    owner SMALLINT UNSIGNED NOT NULL REFERENCES person(id),
    PRIMARY KEY (id)
    );

    INSERT INTO person VALUES (NULL, 'Antonio Paz');

    SELECT @last := LAST_INSERT_ID();

    INSERT INTO shirt VALUES
    (NULL, 'polo', 'blue', @last),
    (NULL, 'dress', 'white', @last),
    (NULL, 't-shirt', 'blue', @last);

    INSERT INTO person VALUES (NULL, 'Lilliana Angelovska');

    SELECT @last := LAST_INSERT_ID();

    INSERT INTO shirt VALUES
    (NULL, 'dress', 'orange', @last),
    (NULL, 'polo', 'red', @last),
    (NULL, 'dress', 'blue', @last),
    (NULL, 't-shirt', 'white', @last);

    SELECT * FROM person;
    +----+---------------------+
    | id | name |
    +----+---------------------+
    | 1 | Antonio Paz |
    | 2 | Lilliana Angelovska |
    +----+---------------------+

    SELECT * FROM shirt;
    +----+---------+--------+-------+
    | id | style | color | owner |
    +----+---------+--------+-------+
    | 1 | polo | blue | 1 |
    | 2 | dress | white | 1 |
    | 3 | t-shirt | blue | 1 |
    | 4 | dress | orange | 2 |
    | 5 | polo | red | 2 |
    | 6 | dress | blue | 2 |
    | 7 | t-shirt | white | 2 |
    +----+---------+--------+-------+

    SELECT s.* FROM person p, shirt s
    WHERE p.name LIKE 'Lilliana%'
    AND s.owner = p.id
    AND s.color <> 'white';

    +----+-------+--------+-------+
    | id | style | color | owner |
    +----+-------+--------+-------+
    | 4 | dress | orange | 2 |
    | 5 | polo | red | 2 |
    | 6 | dress | blue | 2 |
    +----+-------+--------+-------+

    Thanx once again.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •