SitePoint Sponsor

User Tag List

Results 1 to 3 of 3

Thread: mySQL question

  1. #1
    One website at a time mmj's Avatar
    Join Date
    Feb 2001
    Location
    Melbourne Australia
    Posts
    6,282
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Hi all,

    I have a particular question about my PHP/mySQL database.

    I have a database driven site of articles at http://www.mostlyamphigory.com/archives/

    Lets say I have an article of id $aid and date $date, and I want to find out what position that article would fall if the articles were sorted by date.

    [edit
    The table is, of course, indexed by date, so is there a way of finding where this article falls in the index by date?
    ]

    What I could do, is count the number of articles that are newer than this article, and then add one:

    "SELECT COUNT(*) FROM articles WHERE date > $date"

    But this, I believe, would be quite inefficient. If there were two articles with the same date, they would return the same value. Is there a better way?

    Reason:
    At the bottom of an article, I'd like to include links to next/previous articles. I need to know where the current article falls to be able to do this.

    Thanks in advance
    Last edited by mmj; Apr 17, 2001 at 03:06.
    [mmj] My magic jigsaw
    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    The Bit Depth Blog Twitter Contact me
    Neon Javascript Framework Jokes Android stuff

  2. #2
    <? echo "Kick me"; ?> petesmc's Avatar
    Join Date
    Nov 2000
    Location
    Hong Kong
    Posts
    1,508
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    This may be quite a pointless post, but if you change the date field to a timestamp then you would have to add two articles/posts within one second to have two of the same values.

    Peter

  3. #3
    One website at a time mmj's Avatar
    Join Date
    Feb 2001
    Location
    Melbourne Australia
    Posts
    6,282
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    It's actually a datetime field so it is accurate to a second.

    Duplicates wouldn't really be my main problem But I was wondering if there was a more efficient way of doing it, seeing as that sort of info would be in the index.


    To put my original question in a simpler way, let's say I have a table like this:
    Code:
    ID  RATING   MOVIE
    _________________________________________
    1   6.98     The Emperor's New Clothes
    2   8.34     Debbie and the Chocolate Factory
    3   2.2      Five Happy Mice
    4   4.0      Five Easy Pieces of Chocolate
    5   1.78     Star Wars 7: The Five Wars
    Let's say the user is viewing a page about "Five Happy Mice" with id of 3 and rating of 2.2

    How would I find out where that rating ranks out of every movie in my database? What would be the most efficient way of doing it?
    Last edited by mmj; Apr 17, 2001 at 09:38.
    [mmj] My magic jigsaw
    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    The Bit Depth Blog Twitter Contact me
    Neon Javascript Framework Jokes Android stuff


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
  •