SitePoint Sponsor

User Tag List

Results 1 to 24 of 24
  1. #1
    Working on it... Contrid's Avatar
    Join Date
    Apr 2006
    Location
    Online
    Posts
    955
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)

    Next Record by Order

    Good day,

    I've been trying to figure out a way to get the next record by order.

    Say I have 10 records and an 'order' column/field in the table being 1, 2, 3, 4.....9, 10.
    So I start at record with 'order' 1 and I need to select the next, it will be 2, etc.

    Here is my code: http://pastebin.com/eJszP9v3

    What happens when I get to 10? How can I automatically loop and get to 1 again?
    It has to be dynamic. Is there some WHERE clause I can put in the query to look for > 10 else == 1?

    Thank you for your input!
    And so I got lost in code...completely asphyxiated by it...

    Premium WordPress plugins - Tribulant Software

  2. #2
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,501
    Mentioned
    163 Post(s)
    Tagged
    4 Thread(s)
    Maybe this would do the trick?
    Code:
    SELECT `id`, `order` 
    FROM
      (SELECT `id`, `order`
       FROM `table` 
       WHERE `order` > '" . $current_order . "' 
       ORDER BY `order`
       LIMIT 1
       UNION ALL
       SELECT `id`, `order`
       FROM `table` 
       ORDER BY `order`
       LIMIT 1
      ) AS a
    LIMIT 1

  3. #3
    Always A Novice bronze trophy
    K. Wolfe's Avatar
    Join Date
    Nov 2003
    Location
    Columbus, OH
    Posts
    2,182
    Mentioned
    66 Post(s)
    Tagged
    2 Thread(s)
    Quote Originally Posted by Contrid View Post
    Good day,

    I've been trying to figure out a way to get the next record by order.

    Say I have 10 records and an 'order' column/field in the table being 1, 2, 3, 4.....9, 10.
    So I start at record with 'order' 1 and I need to select the next, it will be 2, etc.

    Here is my code: http://pastebin.com/eJszP9v3

    What happens when I get to 10? How can I automatically loop and get to 1 again?
    It has to be dynamic. Is there some WHERE clause I can put in the query to look for > 10 else == 1?

    Thank you for your input!
    You can do a Case statement to handle this but what is your end goal? Why do you need to select the next order, what do you need to select from it and what do you plan to do with your next order?

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,273
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by Contrid View Post
    How can I automatically loop and get to 1 again?
    the following handles wraparound and does not assume that the lowest order is 1
    Code:
    $current_order = 2;
    $query = "
    SELECT `id`
         , `order` 
      FROM `table` 
     WHERE `order` =
           ( SELECT MIN(`order`)
               FROM `table`
              WHERE `order` > " . $current_order . " ) 
    UNION ALL 
    SELECT `id`
         , `order` 
      FROM `table`
     WHERE `order` =
           ( SELECT MIN(`order`)
               FROM `table` ) 
    ORDER
        BY `order` DESC LIMIT 1"
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    Always A Novice bronze trophy
    K. Wolfe's Avatar
    Join Date
    Nov 2003
    Location
    Columbus, OH
    Posts
    2,182
    Mentioned
    66 Post(s)
    Tagged
    2 Thread(s)
    Quote Originally Posted by r937 View Post
    the following handles wraparound and does not assume that the lowest order is 1
    Code:
    $current_order = 2;
    $query = "
    SELECT `id`
         , `order` 
      FROM `table` 
     WHERE `order` =
           ( SELECT MIN(`order`)
               FROM `table`
              WHERE `order` > " . $current_order . " ) 
    UNION ALL 
    SELECT `id`
         , `order` 
      FROM `table`
     WHERE `order` =
           ( SELECT MIN(`order`)
               FROM `table` ) 
    ORDER
        BY `order` DESC LIMIT 1"
    See that's a lot of connections depending on how this is being used. @Contrid ;, can you elaborate on what your trying to accomplish?

  6. #6
    Working on it... Contrid's Avatar
    Join Date
    Apr 2006
    Location
    Online
    Posts
    955
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by guido2004 View Post
    Maybe this would do the trick?
    Code:
    SELECT `id`, `order` 
    FROM
      (SELECT `id`, `order`
       FROM `table` 
       WHERE `order` > '" . $current_order . "' 
       ORDER BY `order`
       LIMIT 1
       UNION ALL
       SELECT `id`, `order`
       FROM `table` 
       ORDER BY `order`
       LIMIT 1
      ) AS a
    LIMIT 1
    Thanks, removing the ORDER statement from the first SELECT query in the UNION makes the query work but it seems to default to the result in the 2nd SELECT query in the union each time. So the result is 1 each time even though the first SELECT actually returns something valid. I can't seem to do weighting with UNION as I want to but I don't know UNION that well.
    And so I got lost in code...completely asphyxiated by it...

    Premium WordPress plugins - Tribulant Software

  7. #7
    Working on it... Contrid's Avatar
    Join Date
    Apr 2006
    Location
    Online
    Posts
    955
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by K. Wolfe View Post
    See that's a lot of connections depending on how this is being used. @Contrid ;, can you elaborate on what your trying to accomplish?
    I want to loop through 10 MySQL records infinitely by 'order' field.

    So it starts at 1, outputs something on the page.
    Using Javascript setInterval and an Ajax request calling back to the script with the current order value 1.
    The script will then select 2 and output on the page and the Javascript will call to the script again with order value 2.
    And so it will go on until it reaches 10 and needs to then loop back to 1 since there is nothing after 10.
    I can't say how many records there will be, it is dynamic but that's the concept
    And so I got lost in code...completely asphyxiated by it...

    Premium WordPress plugins - Tribulant Software

  8. #8
    Working on it... Contrid's Avatar
    Join Date
    Apr 2006
    Location
    Online
    Posts
    955
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    the following handles wraparound and does not assume that the lowest order is 1
    Code:
    $current_order = 2;
    $query = "
    SELECT `id`
         , `order` 
      FROM `table` 
     WHERE `order` =
           ( SELECT MIN(`order`)
               FROM `table`
              WHERE `order` > " . $current_order . " ) 
    UNION ALL 
    SELECT `id`
         , `order` 
      FROM `table`
     WHERE `order` =
           ( SELECT MIN(`order`)
               FROM `table` ) 
    ORDER
        BY `order` DESC LIMIT 1"
    Fantastic, you are a genius! It works! Clever stuff
    I need to implement this with some additional conditions and joins though.
    I'll get working on it and post my resulting query here
    And so I got lost in code...completely asphyxiated by it...

    Premium WordPress plugins - Tribulant Software

  9. #9
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,273
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by K. Wolfe View Post
    See that's a lot of connections
    nope... one connection per user

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

  10. #10
    Hosting Team Leader silver trophybronze trophy
    cpradio's Avatar
    Join Date
    Jun 2002
    Location
    Ohio
    Posts
    5,161
    Mentioned
    152 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by Contrid View Post
    I want to loop through 10 MySQL records infinitely by 'order' field.

    So it starts at 1, outputs something on the page.
    Using Javascript setInterval and an Ajax request calling back to the script with the current order value 1.
    The script will then select 2 and output on the page and the Javascript will call to the script again with order value 2.
    And so it will go on until it reaches 10 and needs to then loop back to 1 since there is nothing after 10.
    I can't say how many records there will be, it is dynamic but that's the concept
    Wouldn't you just be better off retrieving all of the IDs you want to use in this process (maybe even all of the data you need) into an array and using a pointer to identify which element in the array you are currently at every time your setInterval function is called?

    Depending on how much data is needed to produce your output, I'd argue you can bring back all of it and store it in a multi-dimensional array and your performance will be FAR better. Keep in mind, you are setting up a situation where each visitor will be hitting your database every X seconds automatically. Sounds costly and slow to me...

    Oh, and not to mention with this setup your ability to cache the data seems limited. I imagine you can reuse the same pulled records for every one of your visitors (they don't get unique data), so by pulling them all, caching them, and then utilizing that cache, you can save a LOT of database activity. You will have 1 call every time your cache expires for all visitors instead of a call for each visitor every X seconds.

    Edit:

    added more to my rant regarding caching

  11. #11
    Always A Novice bronze trophy
    K. Wolfe's Avatar
    Join Date
    Nov 2003
    Location
    Columbus, OH
    Posts
    2,182
    Mentioned
    66 Post(s)
    Tagged
    2 Thread(s)
    Why not this:

    Code:
    $orderId = 15;
    $limit = 10;
    $sql = "SELECT 'ID', 'ORDER' FROM TABLE WHERE ORDER < $orderId ORDER BY 'ORDER' DESC LIMIT $limit"
    This will simply produce the last 10 orders that came before the current one.

  12. #12
    Always A Novice bronze trophy
    K. Wolfe's Avatar
    Join Date
    Nov 2003
    Location
    Columbus, OH
    Posts
    2,182
    Mentioned
    66 Post(s)
    Tagged
    2 Thread(s)
    Quote Originally Posted by r937 View Post
    nope... one connection per user

    Right, but if he's doing this for an existing record set, not just one 'ID', it gets messy.

  13. #13
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,273
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by K. Wolfe View Post
    Why not this:
    because there's no wraparound, that's why

    also, p.s., your single quotes should be backticks
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  14. #14
    Always A Novice bronze trophy
    K. Wolfe's Avatar
    Join Date
    Nov 2003
    Location
    Columbus, OH
    Posts
    2,182
    Mentioned
    66 Post(s)
    Tagged
    2 Thread(s)
    Quote Originally Posted by r937 View Post
    because there's no wraparound, that's why

    also, p.s., your single quotes should be backticks
    Am I off on what he's looking for? I believe he is looking for the previous 10 orders from the starting point, though I never did get why he's bringing up a reset at 10

  15. #15
    Working on it... Contrid's Avatar
    Join Date
    Apr 2006
    Location
    Online
    Posts
    955
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    the following handles wraparound and does not assume that the lowest order is 1
    Code:
    $current_order = 2;
    $query = "
    SELECT `id`
         , `order` 
      FROM `table` 
     WHERE `order` =
           ( SELECT MIN(`order`)
               FROM `table`
              WHERE `order` > " . $current_order . " ) 
    UNION ALL 
    SELECT `id`
         , `order` 
      FROM `table`
     WHERE `order` =
           ( SELECT MIN(`order`)
               FROM `table` ) 
    ORDER
        BY `order` DESC LIMIT 1"
    @r937

    I ran some tests here and it's very clever how you used the MIN() there
    My resulting query is done and working well and contains some dynamic values of course.

    Code:
    SELECT a.id, za.order, za.zone_id 
    FROM wp_wpbrads AS a 
    LEFT JOIN wp_wpbrzonesads AS za 
    ON a.id = za.ad_id 
    WHERE za.order = 
    (SELECT MIN(za.order) 
    FROM wp_wpbrzonesads AS za 
    WHERE za.order > 2 
    AND za.zone_id = 2 
    AND a.active = 'Y' 
    AND (a.expiry = '0000-00-00' OR a.expiry >= '2012-11-07') 
    AND (a.startDate = '0000-00-00' OR a.startDate <= '2012-11-07') 
    AND (a.impressions = 0 OR a.impressions <= a.views) AND a.id != '1') 
    UNION ALL 
    SELECT a.id, za.order, za.zone_id 
    FROM wp_wpbrads AS a 
    LEFT JOIN wp_wpbrzonesads AS za 
    ON a.id = za.ad_id 
    WHERE za.order = 
    (SELECT MIN(za.order) 
    FROM wp_wpbrzonesads AS za 
    WHERE za.zone_id = 2 AND a.active = 'Y' 
    AND (a.expiry = '0000-00-00' OR a.expiry >= '2012-11-07') 
    AND (a.startDate = '0000-00-00' OR a.startDate <= '2012-11-07') 
    AND (a.impressions = 0 OR a.impressions <= a.views) AND a.id != '1') 
    ORDER BY `order` DESC LIMIT 1;
    Last edited by Contrid; Nov 7, 2012 at 14:54. Reason: Just removed the indent
    And so I got lost in code...completely asphyxiated by it...

    Premium WordPress plugins - Tribulant Software

  16. #16
    Working on it... Contrid's Avatar
    Join Date
    Apr 2006
    Location
    Online
    Posts
    955
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by K. Wolfe View Post
    Why not this:

    Code:
    $orderId = 15;
    $limit = 10;
    $sql = "SELECT 'ID', 'ORDER' FROM TABLE WHERE ORDER < $orderId ORDER BY 'ORDER' DESC LIMIT $limit"
    This will simply produce the last 10 orders that came before the current one.
    Each record is called in a different process, using Ajax so it's not a solution.
    And so I got lost in code...completely asphyxiated by it...

    Premium WordPress plugins - Tribulant Software

  17. #17
    Working on it... Contrid's Avatar
    Join Date
    Apr 2006
    Location
    Online
    Posts
    955
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by cpradio View Post
    Wouldn't you just be better off retrieving all of the IDs you want to use in this process (maybe even all of the data you need) into an array and using a pointer to identify which element in the array you are currently at every time your setInterval function is called?

    Depending on how much data is needed to produce your output, I'd argue you can bring back all of it and store it in a multi-dimensional array and your performance will be FAR better. Keep in mind, you are setting up a situation where each visitor will be hitting your database every X seconds automatically. Sounds costly and slow to me...

    Oh, and not to mention with this setup your ability to cache the data seems limited. I imagine you can reuse the same pulled records for every one of your visitors (they don't get unique data), so by pulling them all, caching them, and then utilizing that cache, you can save a LOT of database activity. You will have 1 call every time your cache expires for all visitors instead of a call for each visitor every X seconds.

    Edit:

    added more to my rant regarding caching
    Thank you for your input on this.
    Each record is selected using a different process.
    Each record result is an advertisement/banner so caching is not an option in most cases.
    And so I got lost in code...completely asphyxiated by it...

    Premium WordPress plugins - Tribulant Software

  18. #18
    Working on it... Contrid's Avatar
    Join Date
    Apr 2006
    Location
    Online
    Posts
    955
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by guido2004 View Post
    Maybe this would do the trick?
    Code:
    SELECT `id`, `order` 
    FROM
      (SELECT `id`, `order`
       FROM `table` 
       WHERE `order` > '" . $current_order . "' 
       ORDER BY `order`
       LIMIT 1
       UNION ALL
       SELECT `id`, `order`
       FROM `table` 
       ORDER BY `order`
       LIMIT 1
      ) AS a
    LIMIT 1
    Btw... this query actually works when removing the first ORDER BY.
    And then adding the ORDER BY to the end of the query using ORDER BY order DESC
    Giving the first SELECT in the union the priority because it will most likely be 1 or higher
    And so I got lost in code...completely asphyxiated by it...

    Premium WordPress plugins - Tribulant Software

  19. #19
    Hosting Team Leader silver trophybronze trophy
    cpradio's Avatar
    Join Date
    Jun 2002
    Location
    Ohio
    Posts
    5,161
    Mentioned
    152 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by Contrid View Post
    Thank you for your input on this.
    Each record is selected using a different process.
    Each record result is an advertisement/banner so caching is not an option in most cases.
    That is fair enough.

  20. #20
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,273
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by Contrid View Post
    My resulting query is done and working well and contains some dynamic values of course.
    damn, i can't read that

    how about this --
    Code:
    SELECT a.id
         , za.order
         , za.zone_id 
      FROM wp_wpbrads AS a 
    LEFT OUTER
      JOIN wp_wpbrzonesads AS za 
        ON za.ad_id = a.id
     WHERE za.order = 
           ( SELECT MIN(zx.order) 
               FROM wp_wpbrzonesads AS zx 
              WHERE zx.order > 2 
                AND zx.zone_id = 2 
       AND a.active = 'Y' 
       AND (a.expiry = '0000-00-00' OR a.expiry >= '2012-11-07') 
       AND (a.startDate = '0000-00-00' OR a.startDate <= '2012-11-07') 
       AND (a.impressions = 0 OR a.impressions <= a.views) 
       AND a.id <> '1' ) 
    UNION ALL 
    SELECT a.id
         , za.order
         , za.zone_id 
      FROM wp_wpbrads AS a 
    LEFT OUTER
      JOIN wp_wpbrzonesads AS za 
        ON za.ad_id = a.id
     WHERE za.order = 
           ( SELECT MIN(zx.order) 
               FROM wp_wpbrzonesads AS zx
              WHERE zx.zone_id = 2 
       AND a.active = 'Y' 
       AND (a.expiry = '0000-00-00' OR a.expiry >= '2012-11-07') 
       AND (a.startDate = '0000-00-00' OR a.startDate <= '2012-11-07') 
       AND (a.impressions = 0 OR a.impressions <= a.views) 
       AND a.id <> '1' ) 
    ORDER 
        BY `order` DESC LIMIT 1;
    see those two closing parentheses in red? do me a favour and move them up in behind AND zx.zone_id = 2 in both SELECTs

    note that it is generally a very bad idea to use the same table alias inside a subquery as in the main outer query, especially if it refers to the same table... therefore, i renamed your alias inside the subqueries from za to zx
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  21. #21
    Working on it... Contrid's Avatar
    Join Date
    Apr 2006
    Location
    Online
    Posts
    955
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    damn, i can't read that

    how about this --
    Code:
    SELECT a.id
         , za.order
         , za.zone_id 
      FROM wp_wpbrads AS a 
    LEFT OUTER
      JOIN wp_wpbrzonesads AS za 
        ON za.ad_id = a.id
     WHERE za.order = 
           ( SELECT MIN(zx.order) 
               FROM wp_wpbrzonesads AS zx 
              WHERE zx.order > 2 
                AND zx.zone_id = 2 
       AND a.active = 'Y' 
       AND (a.expiry = '0000-00-00' OR a.expiry >= '2012-11-07') 
       AND (a.startDate = '0000-00-00' OR a.startDate <= '2012-11-07') 
       AND (a.impressions = 0 OR a.impressions <= a.views) 
       AND a.id <> '1' ) 
    UNION ALL 
    SELECT a.id
         , za.order
         , za.zone_id 
      FROM wp_wpbrads AS a 
    LEFT OUTER
      JOIN wp_wpbrzonesads AS za 
        ON za.ad_id = a.id
     WHERE za.order = 
           ( SELECT MIN(zx.order) 
               FROM wp_wpbrzonesads AS zx
              WHERE zx.zone_id = 2 
       AND a.active = 'Y' 
       AND (a.expiry = '0000-00-00' OR a.expiry >= '2012-11-07') 
       AND (a.startDate = '0000-00-00' OR a.startDate <= '2012-11-07') 
       AND (a.impressions = 0 OR a.impressions <= a.views) 
       AND a.id <> '1' ) 
    ORDER 
        BY `order` DESC LIMIT 1;
    see those two closing parentheses in red? do me a favour and move them up in behind AND zx.zone_id = 2 in both SELECTs

    note that it is generally a very bad idea to use the same table alias inside a subquery as in the main outer query, especially if it refers to the same table... therefore, i renamed your alias inside the subqueries from za to zx
    Thank you for your reply @r937

    Yes, I did initially move those parentheses after the zone_id = 2
    The problem is then that the MIN in both SELECTs return a single result, it acts like LIMIT 1 and an ORDER at the same time.
    So the 2 SELECTs end up fetching records which don't abide by those conditions like 'active', 'expiry', etc...

    So za.order = whatever comes back from those 2 SELECTs.
    Then applies the conditions and eventually we have no records to work with, that's why I moved the conditions into the inner SELECTs.
    Is that okay and will it work fine?

    How do you format your SQL nicely that way?
    And so I got lost in code...completely asphyxiated by it...

    Premium WordPress plugins - Tribulant Software

  22. #22
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,273
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by Contrid View Post
    How do you format your SQL nicely that way?
    spaces and line breaks
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  23. #23
    Always A Novice bronze trophy
    K. Wolfe's Avatar
    Join Date
    Nov 2003
    Location
    Columbus, OH
    Posts
    2,182
    Mentioned
    66 Post(s)
    Tagged
    2 Thread(s)
    Quote Originally Posted by Contrid View Post
    Thank you for your reply @r937
    Finish your tag with a space and a ; like so, @Contrid ;

  24. #24
    Working on it... Contrid's Avatar
    Join Date
    Apr 2006
    Location
    Online
    Posts
    955
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by K. Wolfe View Post
    Finish your tag with a space and a ; like so, @Contrid ;
    Thanks, seems to work well @K. Wolfe ;
    And so I got lost in code...completely asphyxiated by it...

    Premium WordPress plugins - Tribulant Software


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
  •