SitePoint Sponsor

User Tag List

Results 1 to 8 of 8
  1. #1
    SitePoint Zealot
    Join Date
    Apr 2005
    Posts
    100
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Extended LIMIT functionality needed in PHP and MySQL

    I have a blog-type site Running PHP 4.3.1 and MySQL where I will be updating the homepage on a regular basis. When the page is updated I want the old entry to go to an Archive page that will show all entries except the latest one (because it is on the home page). I thought of using LIMIT, but it is used more for what you want to include rather than what you don't want to include. The table has fields for a unique id, a date/time stamp that is recorded when the entry is created, an entry title and the entry text. Does anyone have any suggestions on what sort of query I would use to get the Archive page to show all entries except the last one created?
    Thank you for your help.

  2. #2
    SitePoint Addict fesh's Avatar
    Join Date
    Jan 2005
    Location
    Jackson, New Jersey
    Posts
    218
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    what about doing something like

    SELECT * WHERE id != MAX(id)

    or something of the sort
    Fesh
    imagine what it's going to be like

  3. #3
    SitePoint Zealot
    Join Date
    Apr 2005
    Posts
    100
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Cool, that looks good to me. I'll give it a try. Thanks!

  4. #4
    SitePoint Addict fesh's Avatar
    Join Date
    Jan 2005
    Location
    Jackson, New Jersey
    Posts
    218
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    if it doesnt work let me know
    Fesh
    imagine what it's going to be like

  5. #5
    SitePoint Zealot
    Join Date
    Apr 2005
    Posts
    100
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Nope. It seemed like it should work, but when I included it, I got:
    Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in....
    Here is what the code looks like for that page:

    PHP Code:
    <?php 
        
    require ('mysql_connect.php');
        
    $query "SELECT id, date, hometitle, hometext FROM racinghome WHERE id != MAX(id) ORDER BY date DESC";        
            
    $result mysql_query ($query);
            while (
    $row mysql_fetch_array ($resultMYSQL_ASSOC)) {
        echo 
    "<div class=\"body\" style=\"padding-top:15px\"><span class=\"bold\">{$row['date']}</span></div>
        <div class=\"headerSm\">
    {$row['hometitle']}</div>
        <div class=\"body\">
        <p>
    {$row['hometext']}</p>
        </div>"
    ;
        }
          
    ?>

  6. #6
    SitePoint Guru dbevfat's Avatar
    Join Date
    Dec 2004
    Location
    ljubljana, slovenia
    Posts
    684
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    your $result is not a valid MySQL result resource, so there must have been an error. See mysql_error() or something similar.

  7. #7
    SitePoint Zealot
    Join Date
    Apr 2005
    Posts
    100
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by dbevfat
    your $result is not a valid MySQL result resource, so there must have been an error. See mysql_error() or something similar.
    Well, the thing is, the script works just fine if I don't include the "WHERE id != MAX(id)" bit. I was doing a bit of reading on the subject, and it looks as though the MAX() function is used mostly with the GROUP BY function in MySQL. So perhaps it won't work by itself in this context. I don't really know. I'll have to read some more on the subject. If anyone has any other ideas please let me know.

  8. #8
    SitePoint Guru dbevfat's Avatar
    Join Date
    Dec 2004
    Location
    ljubljana, slovenia
    Posts
    684
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Dunno about a query, since MySQL doesn't support nested selects (I'm sure those could be somehow useful), but the way I see it, you'll be better off just selecting all and discarding the first result in your script. This will not be a performance hit, since you're fetching only one more result, but with a much simplier query. I am actually sure that (at least for more records) this would even be faster.


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
  •