Getting Last Record From a MySQL Query

Posted on 2008-11-15
Last Modified: 2013-12-13
How do you find out what the last record in a mysql table is?

I tried having it do a mysql_num_rows and then seeing if the ID is greater than the total but that does not work because some of my entries in my table have an id of say...503 but there's only 200 total rows.

I need to be able to know if the current selected row is the LAST record in the database so that I know whether not the NEXT id should be the FIRST id...and that leads me to another would I tell what the FIRST id is for the first row in the table?

If you need further clarification please let me know.

Question by:magnumiq
    LVL 18

    Expert Comment

    by:Matthew Kelly
    Does the below example help any?

    You should be able to tell by the index location the result being displayed is using:

    If this is not what you are looking for please explain your query/code farther.
    $sql = "SELECT * FROM tbl";
    $result = mysql_query($sql);
    echo "First record: ".mysql_result($result,0,"col_name");
    echo "Last record: ".mysql_result($result,mysql_num_rows($result)-1,"col_name");
    for ( $i=0; $i<mysql_num_rows($result);$i++ )
    if ( $i==0 ) { echo "First record: ".mysql_result($result,$i,"col_name"); }
    else if ( $i=(mysql_num_rows($result)-1) ) { echo "Last record: ".mysql_result($result,$i,"col_name"); }
    else { echo "Middle record number ".$i.": ".mysql_result($result,$i,"col_name"); }

    Open in new window


    Author Comment

    Nah I figured it out but thanks.  You're doing it waaay to complicated ;-)

    It's as simple as doing a query and displaying the records using ASC to get the FIRST entry and DESC to get the LAST ;-)
    LVL 39

    Expert Comment

    by:Roger Baklund
    An even simpler solution:
    select min(id) from mytable
    select max(id) from mytable

    Open in new window

    LVL 39

    Expert Comment

    by:Roger Baklund could even combine these in the same query:
    select min(id) as smallest,max(id) as largest from mytable

    Open in new window

    LVL 4

    Expert Comment

    Hi there, well with this function you can get it, and for instance: i am saving it into a ssession variable, the function must be placed after the mysql_query, about the first record you already answered, Xd, cheers!
    query code
    $Result1 = mysql_query($insertSQL, $ConextoDB) or die(mysql_error());

    Open in new window

    LVL 1

    Accepted Solution

    PAQed with points refunded (500)

    EE Admin

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    Consider the following scenario: You are working on a website and make something great - something that lets the server work with information submitted by your users. This could be anything, from a simple guestbook to a e-Money solution. But what…
    These days socially coordinated efforts have turned into a critical requirement for enterprises.
    The viewer will learn how to create and use a small PHP class to apply a watermark to an image. This video shows the viewer the setup for the PHP watermark as well as important coding language. Continue to Part 2 to learn the core code used in creat…
    The viewer will learn how to create a basic form using some HTML5 and PHP for later processing. Set up your basic HTML file. Open your form tag and set the method and action attributes.: (CODE) Set up your first few inputs one for the name and …

    794 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    15 Experts available now in Live!

    Get 1:1 Help Now