SitePoint Sponsor

User Tag List

Results 1 to 21 of 21
  1. #1
    SitePoint Member
    Join Date
    Jan 2006
    Posts
    10
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Lightbulb Counting Row Position

    Ok,

    Basically say I got some data with IDs of "1,3,4,5,6". My aim is to make next and previous links to go from ID "3" back to "1" or forward to "4".

    I was going to SELECT and LIMIT X,Y where X is the starting row position, and Y is how many Rows I wish to go down, right?

    I want to know how to get the X value. At first I wanted to use the ID but ID "3" is actually on Row 2, see my dilemma?

    I've searched the MySQL function thingy and I think I may need to use mysql_row_seek() but I am not sure how to do so.

    Any help is appreciated!

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,347
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    to retrieve the row for ID=3, use this query:
    Code:
    select foo
         , bar
         , ( select min(id)
               from yourtable  
              where id > t.id ) as next_id
         , ( select max(id)
               from yourtable  
              where id < t.id ) as prev_id
      from yourtable as t
     where id = 3
    this brings along the values of the next and prev ids, regardless of "row number" (which doesn't exist anyway)
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Member
    Join Date
    Jan 2006
    Posts
    10
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Im really sorry but I'm only used to seeing SQL queries in PHP like this:

    PHP Code:
    $commentcount mysql_query("SELECT pictureID FROM comments WHERE pictureID='".$pic['pictureID']."'") or die(mysql_error()); 
    SO can anyone explain code above to me please.

    Thanks

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,347
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by Shreedhan
    SO can anyone explain code above to me please.
    which, yours or mine?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Member
    Join Date
    Jan 2006
    Posts
    10
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Yours, the longer one :-p

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,347
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    okay, not sure why you want it all one one line, but here you go:
    PHP Code:
    $commentcount mysql_query("select pictureID     , ( select min(pictureID)           from comments            where pictureID > t.pictureID ) as next_pictureID     , ( select max(pictureID)           from comments            where pictureID < t.pictureID ) as prev_pictureID  from yourtable as t where pictureID='".$pic['pictureID']."'") or die(mysql_error()); 
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    SitePoint Member
    Join Date
    Jan 2006
    Posts
    10
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks but I just wanted it explaing like what dos the MAX() actually do etc.. but if you dont want to don't worry

    Thanks anyway :-)

  8. #8
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,347
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    ah, okay, sorry, i understand now

    my query is exactly the same as your query, except mine returns three columns and yours returns only one

    the two additional columns that my query returns are both produced by a correlated subselect

    the correlation is via the correlation variable t which is the table alias of the comments table in the main query

    so each subselect can see which row is being processed by the main query

    now, the main query retrieves only one row, the row with pictureID 3

    so for that row, each subselect looks again into the comments table, and pulls out the highest (MAX) pictureID or the lowest (MIN) pictureID respectively

    not the highest and lowest overall -- no, the highest of all the pictureIDs that are lower than the pictureID being processed by the main query, and the lowest of all the pictureIDs that are higher than the pictureID being processed by the main query

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

  9. #9
    SitePoint Member
    Join Date
    Jan 2006
    Posts
    10
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    So how would I output the next and prev IDs (IDS 1 and 4 using my example). WOuld it be just

    PHP Code:
    echo $next_pictureID;
    echo 
    $prev_pictureID;
    ?? 

  10. #10
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,347
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    sorry, no idea, i don't do php

    but it looks right, because, yes, the query returns a single row with the column values pictureID=3, next_pictureID=4, prev_pictureID=1

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

  11. #11
    reads the ********* Crier silver trophybronze trophy longneck's Avatar
    Join Date
    Feb 2004
    Location
    Tampa, FL (US)
    Posts
    9,854
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    PHP Code:
    $result mysql_query("select foo
         , bar
         , ( select min(id)
               from yourtable  
              where id > t.id ) as next_id
         , ( select max(id)
               from yourtable  
              where id < t.id ) as prev_id
      from yourtable as t
     where id = 
    $current_id") or die(mysql_error());
    $row mysql_fetch_assoc($result) or die(mysql_error());
    echo 
    $row['next_id'];
    echo 
    $row['prev_id']; 

  12. #12
    SitePoint Member
    Join Date
    Jan 2006
    Posts
    10
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Wait a sec,

    I just realised I might be confusing you aswell as myself.

    My table is attached. Lemme go through this again:

    I want to basically get every image from a particular albumID. Then I want to generate pictureID's for the next and previous rows from the pictureID i am on.

    e.g. albumID is 1
    so picture IDs I have are 3,4,5,6,7,8,9.

    If current picture I am on is 5, then I want to generate 4 and 6. If go to 4 I want to generate 3 and 5, if I got to 3, I want to generate 'nothing' and 4.

    Help now?

    Sorry for me being stupid and explaining stupidly,

    Thanks
    Attached Images Attached Images

  13. #13
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,347
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by Shreedhan
    If current picture I am on is 5, then I want to generate 4 and 6. If go to 4 I want to generate 3 and 5, if I got to 3, I want to generate 'nothing' and 4.
    did you try my query at all? i would very much like to know if it produced any values other than those
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  14. #14
    SitePoint Member
    Join Date
    Jan 2006
    Posts
    10
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Yes I used this:
    PHP Code:
     $result mysql_query("select foo
         , bar
         , ( select min(id)
               from yourtable  
              where id > t.id ) as next_id
         , ( select max(id)
               from yourtable  
              where id < t.id ) as prev_id
      from yourtable as t
    where id = 
    $current_id") or die(mysql_error());
    $row mysql_fetch_assoc($result) or die(mysql_error());
    echo 
    $row['next_id'];
    echo 
    $row['prev_id']; 
    It gives me this error:
    You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'SELECT min(pictureID) FROM pictures WHERE pictureID > t.picture

  15. #15
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,347
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    something you should have mentioned a lot earlier in this thread is that you are on a greatgrandfather version of mysql

    current version is 5.1
    previous version is 5.0
    the one before that is 4.1

    you have an even earlier one

    it is your responsibility to mention this every time you post a question, to prevent people spending their time coming up with a solution that you won't be able to implement anyway

    please see the sticky thread at the top of the forum
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  16. #16
    SitePoint Member
    Join Date
    Jan 2006
    Posts
    10
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Damn, I'm really sorry. Urr sorry's all I can say , so what happens now?

  17. #17
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,347
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    hey no prob, everybody misses that sticky thread, even though it's right at the top of the forum and says PLEASE READ



    if you cannot upgrade, you will have to run three separate queries

    Edit: w00h00!! 10,000 posts!!
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  18. #18
    SitePoint Member
    Join Date
    Jan 2006
    Posts
    10
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Urr, how would I split it up??

  19. #19
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,347
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    1st query:
    Code:
    select pictureID   
      from comments
     where pictureID = $pic['pictureID']
    2nd query:
    Code:
    select min(pictureID) as next_pictureID  
      from comments
     where pictureID > $pic['pictureID']
    3nd query:
    Code:
    select max(pictureID) as prev_pictureID  
      from comments
     where pictureID < $pic['pictureID']
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  20. #20
    SitePoint Member
    Join Date
    Jan 2006
    Posts
    10
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    YAY I got the results I wanted! Thanks a lot!

  21. #21
    SitePoint Wizard guelphdad's Avatar
    Join Date
    Oct 2003
    Location
    St. Catharines, ON Canada
    Posts
    1,708
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    any reason you can't upgrade your mysql?
    three queries are always going to be slower than one.


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
  •