Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 8 of 8
  1. #1
    Regular Coder
    Join Date
    Sep 2010
    Posts
    331
    Thanks
    9
    Thanked 6 Times in 6 Posts

    How to get last id?

    How do you return the last id from a table? I need to find the last id in a table when the script runs the first time and then select (and echo) any new records that were inserted after the previous last id (i.e select any records from the table where id > the previous last time)
    EDIT: In PDO instead of MySQLi
    Last edited by elitis; 04-05-2013 at 04:30 AM.
    Coding is a challenge, get used to it
    Always remember to debug
    Try the guess & check method
    Break it down into simple steps

  • #2
    Master Coder felgall's Avatar
    Join Date
    Sep 2005
    Location
    Sydney, Australia
    Posts
    6,642
    Thanks
    0
    Thanked 649 Times in 639 Posts
    Where are you storing the information to identify the previous last id? You'd need to read it back from there in order to use it as the starting point.
    Stephen
    Learn Modern JavaScript - http://javascriptexample.net/
    Helping others to solve their computer problem at http://www.felgall.com/

    Don't forget to start your JavaScript code with "use strict"; which makes it easier to find errors in your code.

  • #3
    Regular Coder
    Join Date
    Sep 2010
    Posts
    331
    Thanks
    9
    Thanked 6 Times in 6 Posts
    That's part of what I need help figuring out. I do have the previous last id stored in a variable on a page and then restored into a javascript variable. But that variable only gets created if the user's browser doesn't support Server-Sent Events. In the case of the browser does support SSE, the best I can do is accessing the lastInsertId property, but obviously this is only a client-side solution.
    Coding is a challenge, get used to it
    Always remember to debug
    Try the guess & check method
    Break it down into simple steps

  • #4
    kdm
    kdm is offline
    New to the CF scene
    Join Date
    Apr 2013
    Posts
    2
    Thanks
    0
    Thanked 0 Times in 0 Posts
    You would have to set up some way of storing that id such as a new table, then you could do this do get the last id:
    Code:
    SELECT id
    FROM table
    ORDER BY id DESC
    LIMIT 1
    And store the result (ideally you would first clear where it is being stored), then to get any id added after the last time you would:
    Code:
    SELECT id
    FROM table
    WHERE id>(SELECT id FROM new_table)
    If you aren't storing it in another table then it would of course be a bit different
    And that should get you your ids that have been added since you ran the first one

  • #5
    New Coder
    Join Date
    Nov 2012
    Location
    chennai
    Posts
    14
    Thanks
    0
    Thanked 1 Time in 1 Post
    SELECT max( id )
    FROM movies
    ORDER BY id DESC

  • #6
    Master Coder felgall's Avatar
    Join Date
    Sep 2005
    Location
    Sydney, Australia
    Posts
    6,642
    Thanks
    0
    Thanked 649 Times in 639 Posts
    Quote Originally Posted by rashgang View Post
    SELECT max( id )
    FROM movies
    ORDER BY id DESC
    How does that determine what the max value was the last time the person visited? There could have been hundreds of additional entries added since then.

    The mysqli_insert_id($link) call can only be used while the script that did the insert is still running as it returns the last id inserted by this copy of the script - even if several more have been added by other people since - and so you need to store that retrieved value into another table with the current user as the primary key and read that back in any subsequent script where you want to be able to determine what the last entry added by the current user was.
    Stephen
    Learn Modern JavaScript - http://javascriptexample.net/
    Helping others to solve their computer problem at http://www.felgall.com/

    Don't forget to start your JavaScript code with "use strict"; which makes it easier to find errors in your code.

  • #7
    New Coder
    Join Date
    Nov 2012
    Location
    chennai
    Posts
    14
    Thanks
    0
    Thanked 1 Time in 1 Post
    it will select only last inserted id you can check with the query. always max(id) will check last inserted id only.

  • #8
    Master Coder felgall's Avatar
    Join Date
    Sep 2005
    Location
    Sydney, Australia
    Posts
    6,642
    Thanks
    0
    Thanked 649 Times in 639 Posts
    Quote Originally Posted by rashgang View Post
    it will select only last inserted id you can check with the query. always max(id) will check last inserted id only.
    Neither of which is what the OP asked how to get. They asked to retrieve the last id that the person has already seen so that they can display all the ids bigger than that.

    Also you should never use max(id) as by the time you come to use it there might be dozens more ids that have been inserted after that value.
    Stephen
    Learn Modern JavaScript - http://javascriptexample.net/
    Helping others to solve their computer problem at http://www.felgall.com/

    Don't forget to start your JavaScript code with "use strict"; which makes it easier to find errors in your code.


  •  

    Posting Permissions

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