SitePoint Sponsor

User Tag List

Results 1 to 8 of 8

Hybrid View

  1. #1
    SitePoint Enthusiast britton's Avatar
    Join Date
    Jul 2005
    Location
    Tennessee
    Posts
    83
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Help - Math for an ID Range in a MySQL Query

    Hey guys,

    I'm making an activity feed for user uploaded pictures and since I want the feed to only display their new images and only one entry, I want it to pull thumbnails for a range based on the id of their newest photo.

    So, essentially, if their ID on the newest picture is 10 and they uploaded five photos, and I wanted to show 5 as a range I'd want the query to backstep and pull id #'s 10 thru 5.

    I have already pulled the ID of their most recent public picture and made it $id.

    Here is my query that will get the array data for the thumbs. I need help with scripting the math part of it to pull WHERE id = (a range of $id - 5)

    $result2775=mysql_query("SELECT url FROM imagetable WHERE name = '" . $active['name'] . "' && id = () ORDER BY id DESC LIMIT 0,5");

    Thanks, I hope its possible and that I wasn't too unclear about what I'm trying to accomplish. I've tried a number of things but none yielded favorable results.

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Code:
    SELECT url 
      FROM imagetable 
     WHERE name = '" . $active['name'] . "' 
    ORDER 
        BY id DESC LIMIT 0,5
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Enthusiast britton's Avatar
    Join Date
    Jul 2005
    Location
    Tennessee
    Posts
    83
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Yeah, I guess I should have been more specific. The reason it has to be in an id range is because I want it to show if they have uploaded multiple photos. If I just have it pull their most recent 5, it wouldn't be a very good activity feed. So it needs to pull in the range so it will show whether they uploaded 1, 2, 3, 4, 5 new photos.

    This is for an activity feed, sort of like MySpace and Facebook's. The code you presented there would show the last 5 they've ever uploaded. I only want it to display if the ID range is close so that it will pull new images.

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    what you're describing is a hack, and i would not do it that way if i were you

    if you want "recent" uploads, use a timestamp and test the date range
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Enthusiast britton's Avatar
    Join Date
    Jul 2005
    Location
    Tennessee
    Posts
    83
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    That seems silly to suggest that its a hack. I'd like to hear why its terrible to do it with IDs instead of a datestamp? There are approx. 800,000 images that would have improper timestamps if I added a new field.

    Anyway, I figured it out.
    I did it this way and it works perfectly:

    Code:
    $id2 = ($id - 50);
    $result2775=mysql_query("SELECT id, url FROM imagetable WHERE name = '" . $active['name'] . "' && id BETWEEN '$id2' and '$id' && priv = '0' ORDER BY id DESC LIMIT 4");

  6. #6
    SitePoint Wizard bronze trophy
    Join Date
    Jul 2006
    Location
    Augusta, Georgia, United States
    Posts
    4,194
    Mentioned
    17 Post(s)
    Tagged
    5 Thread(s)
    A primary key is not meant to represent when the rows were created. A primary key is meant to distinguish one unique row from another.

  7. #7
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    silly? thanks, i think that's a new one


    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  8. #8
    SitePoint Enthusiast britton's Avatar
    Join Date
    Jul 2005
    Location
    Tennessee
    Posts
    83
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I understand what it was meant for but I see no harm in it serving two purposes. Its not as if I'm changing it to meet my needs. It already exists.

    That's like me saying Trees were meant to provide oxygen. I'm just using them for shade, too, which in no way hinders their carbon scrubbing power. I'm not turning them into paper.

    Anyway, thanks for your help


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
  •