# Thread: Next Record by Order

1. ## 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?

2. 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. Originally Posted by Contrid
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?

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. Originally Posted by Contrid
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"```

5. Originally Posted by r937
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. Originally Posted by guido2004
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.

7. Originally Posted by K. Wolfe
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

8. Originally Posted by r937
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

9. Originally Posted by K. Wolfe
See that's a lot of connections
nope... one connection per user

10. Originally Posted by Contrid
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. 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. Originally Posted by r937
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. Originally Posted by K. Wolfe
Why not this:
because there's no wraparound, that's why

also, p.s., your single quotes should be backticks

14. Originally Posted by r937
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. Originally Posted by r937
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
WHERE za.order =
(SELECT MIN(za.order)
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
WHERE za.order =
(SELECT MIN(za.order)
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;```

16. Originally Posted by K. Wolfe
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.

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.

18. Originally Posted by guido2004
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

19. Originally Posted by Contrid
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. Originally Posted by Contrid
My resulting query is done and working well and contains some dynamic values of course.

Code:
```SELECT a.id
, za.order
, za.zone_id
LEFT OUTER
WHERE za.order =
( SELECT MIN(zx.order)
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
LEFT OUTER
WHERE za.order =
( SELECT MIN(zx.order)
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

21. Originally Posted by r937

Code:
```SELECT a.id
, za.order
, za.zone_id
LEFT OUTER
WHERE za.order =
( SELECT MIN(zx.order)
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
LEFT OUTER
WHERE za.order =
( SELECT MIN(zx.order)
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

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?

22. Originally Posted by Contrid
How do you format your SQL nicely that way?
spaces and line breaks

23. Originally Posted by Contrid
Finish your tag with a space and a ; like so, @Contrid ;

24. Originally Posted by K. Wolfe
Finish your tag with a space and a ; like so, @Contrid ;
Thanks, seems to work well @K. Wolfe ;

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•