#1
  1. Senior Member
    Devshed Intermediate (1500 - 1999 posts)

    Join Date
    Jun 2000
    Location
    Enschede, The Netherlands
    Posts
    1,527
    Rep Power
    16
    Hi,

    I'm trying to retrieve the last row of a table for a php-web-page. The table consists of a column 'id', which is auto incrementing, and a bunch of other columns.
    Since the last inserted row has the highest id I tried the following code:

    <BLOCKQUOTE><font size="1" face="Verdana,Arial,Helvetica">code:</font><HR><pre>
    mysql_connect (localhost, name, pwd);
    mysql_select_db (dbname);
    $result = mysql_query ("SELECT * FROM dbtable WHERE id = ( SELECT MAX(id) FROM dbtable )");
    if ($row = mysql_fetch_array($result)) {
    do {
    print( $row["column2"]<br>" );
    // Zoek sub-menu's
    print( $row["column3"]."<br>" );
    print( $row["column4"]."<br>" );
    print( $row["column5"]."<br>" );
    // etc.
    }
    while($row = mysql_fetch_array($result));
    }
    else {
    print( "Sorry, no records were found!" );
    }
    [/code]

    I receive the following error:

    <BLOCKQUOTE><font size="1" face="Verdana,Arial,Helvetica">quote:</font><HR>
    Warning: Supplied argument is not a valid MySQL result resource in C:WEBSERVERHTDOCSmirax_news.php on line 133
    Sorry, no records were found!
    [/quote]

    I tried to change the SQL-query, but I don't get it working.
    Also, I've assured the table isn't empty (There are 2 records.)

    Does somebody knows what I'm doing wrong or how I can do this another way? Thanks!
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Novice (500 - 999 posts)

    Join Date
    Jul 2000
    Posts
    669
    Rep Power
    15
    I do belive this will work for you

    $result = mysql_query("SELECT * from listings",$db);

    //Gets the total no of records
    $count = mysql_num_rows($result);

    //Sets the internal pointer to the last record of the database
    mysql_data_seek($result, ($count - 1));

    //Prints the data from the record
    while ($myrow = mysql_fetch_array($result)) {
    print $myrow["ID"] ." ". $myrow["Name"] ." ". $myrow["Address"] ;
    }
  4. #3
  5. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jul 2000
    Posts
    45
    Rep Power
    15
    Much easier way..

    $query = "SELECT * FROM table ORDER BY Id DESC LIMIT 1";
    $result = mysql_query($query);
    $row = mysql_fetch_array($result);

    That statment will arrange the rows in descending order by their ids, then get only the first row (LIMIT 1).
  6. #4
  7. Senior Member
    Devshed Intermediate (1500 - 1999 posts)

    Join Date
    Jun 2000
    Location
    Enschede, The Netherlands
    Posts
    1,527
    Rep Power
    16
    Thanks Freddy & Polman for your help!!
    It works now!

    I just wonder why my query didn't work. Seemed to me it was just fine?! I got the impression mysql doesn't support sub-queries like the one I made...
  8. #5
  9. No Profile Picture
    Apprentice Deity
    Devshed Loyal (3000 - 3499 posts)

    Join Date
    Jul 1999
    Location
    Niagara Falls (On the wrong side of the gorge)
    Posts
    3,237
    Rep Power
    19
    You're right. MySQL does NOT support sub-queries... yet.
  10. #6
  11. Banned (not really)
    Devshed Supreme Being (6500+ posts)

    Join Date
    Dec 1999
    Location
    Brussels, Belgium
    Posts
    14,646
    Rep Power
    4492
    if you just inserted a value into the database, you can get the auto_incremented value by using mysql_insert_id($link_id). I don't know if this is what you are trying to get or not.

    ---John Holmes...

Similar Threads

  1. Replies: 1
    Last Post: February 5th, 2004, 12:03 PM
  2. Replies: 5
    Last Post: February 3rd, 2004, 02:59 PM
  3. Help using a database and php to update a scores page.
    By deathsythe in forum PHP Development
    Replies: 0
    Last Post: February 2nd, 2004, 03:48 PM
  4. PHP & Database Problems
    By flipsided in forum PHP Development
    Replies: 2
    Last Post: January 25th, 2004, 06:58 PM
  5. Replies: 0
    Last Post: January 22nd, 2004, 01:29 AM

IMN logo majestic logo threadwatch logo seochat tools logo