SitePoint Sponsor

User Tag List

Results 1 to 5 of 5

Hybrid View

  1. #1
    SitePoint Zealot
    Join Date
    Sep 2010
    Posts
    131
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Maximum value in field "id"

    I am trying to get the maximum value in field "id".

    What is really happening is that "$id" is actually taking the lowest value of the "$rowid" array.

    How do I get the highest value in "$rowid"?

    PHP Code:
        $sql "SELECT `id` FROM `table1`";
        
    $result mysql_query($sql$conn);
        
    $rowid mysql_fetch_array($resultMYSQL_ASSOC);
        if (
    $rowid != "") {
            
    $id  max($rowid);
        } 
    P.S. You can not take the "max" of a null array.

  2. #2
    SitePoint Enthusiast
    Join Date
    Apr 2004
    Location
    Michigan
    Posts
    79
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    is id auto-increment?

    edit:
    order by id desc limit 1

  3. #3
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,216
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    SELECT MAX(id) AS max_id FROM table1

    there is no need to retrieve the entire table just to find the largest value
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  4. #4
    SitePoint Zealot
    Join Date
    Sep 2010
    Posts
    131
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    id is Auto_Increment. Could you please elaborate?

  5. #5
    Non-Member bronze trophy
    Join Date
    Nov 2009
    Location
    Keene, NH
    Posts
    3,760
    Mentioned
    23 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    SELECT MAX(id) AS max_id FROM table1
    Spot on, though that still has to count through the order tree. This is functionally identical:

    SELECT id FROM table1 ORDER BY id DESC LIMIT 1

    ... and may run faster despite the more complex query since it will use the existing index. I think that's what Litebearer was suggesting.

    Unless of course need that exact query because you're showing all of those results AND need to know the last ID... if you were using mysqli instead of mysql commands, you could seek to the last result, pull it's id, then seek to start rather than run a separate query. in PDO you could fetchAll letting you process it as a normal array (the last() command would be handy then) -- just be sure in that case to release the statement object so it's not hogging memory.


Bookmarks

Posting Permissions

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