Web Analytics Made Easy -
StatCounter Last insert id in autoincrement field - CodingForum


No announcement yet.

Last insert id in autoincrement field

  • Filter
  • Time
  • Show
Clear All
new posts

  • Last insert id in autoincrement field

    1/ I want to read a field in last record. How can I know which one
    How can I recognize what is the last id number into its autoincrement field?

    (It is not possible with mysql_insert_id() in PHP
    or LAST_INSERT_ID() in mySQL
    because they return a status of MY ACTIONS and a current connection)

    2/ Can I create a query like this

    SELECT aa,ss,dd from tbl WHERE MAX(id)
    I have to make 2 queries
    SELECT MAX(id) FROM tbl
    SELECT aa,ss,dd from tbl WHERE id=upper_find_value

    3/ Can I execute more of one query from PHP with one command?


  • #2
    Hi hryan,

    I'm not sure if there's a better way but I've tried a couple of things out in phpMyAdmin and from what I can gather the way to do it would be something like:
    PHP Code:
    # put your stuff to connect to your database here

    $sql "SELECT MAX(ID) FROM tbl";
    $result mysql_query($sql);
    $row mysql_fetch_row($result);
    $max_id $row[0];

    $sql "SELECT * FROM tbl WHERE ID=$max_id";
    $result mysql_query($sql);
    $row mysql_fetch_array($result);
    It works fine...

    Not sure what your 3rd question was about.
    Hope that helps a bit...


    • #3
      To answer your third question, you can do multiple commands in one statement by separating them with a semi-colon. However, I do not see how it will help in this instance.

      To do a multiple statement, it could be written like this:

      $sql = "SELECT * FROM table1; SELECT * FROM table2";

      You would not be able to read information on one command and then use it in the next command on the same line.

      To select the highest id number in one statement, you would need to use subselects, a function that is too limited in the current version of MySQL.

      The SQL statement would be something like:

      SELECT * FROM table1 WHERE id = (SELECT MAX(id) FROM table1);

      As per the MySQL manuel at http://www.mysql.com/doc/A/N/ANSI_diff_Sub-selects.html , MySQL curruntly only supports the subselect in insert and replace statments rather than in select statement. Version 4.1 should have a stronger implementation of subselets.

      Until then, the best way to look up the record with the highest ID is the method shown by Mouldy_Goat.

      ~~~ Pretend that I have a long and creative signature. ~~~


      • #4
        THANKS! This works fine!