Next Record by Order

**Contrid** · Nov 7, 2012 08:25

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!

**guido2004** · Nov 7, 2012 08:58

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

**K. Wolfe** · Nov 7, 2012 12:25

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?

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?

**r937** · Nov 7, 2012 13:53

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"

**K. Wolfe** · Nov 7, 2012 14:05

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?

**Contrid** · Nov 7, 2012 14:05

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.

**Contrid** · Nov 7, 2012 14:08

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

**Contrid** · Nov 7, 2012 14:17

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

**r937** · Nov 7, 2012 14:18

Originally Posted by K. Wolfe

See that's a lot of connections

nope... one connection per user

**cpradio** · Nov 7, 2012 14:22

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

**K. Wolfe** · Nov 7, 2012 14:22

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.

**K. Wolfe** · Nov 7, 2012 14:24

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.

**r937** · Nov 7, 2012 14:35

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

**K. Wolfe** · Nov 7, 2012 14:41

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

**Contrid** · Nov 7, 2012 14:47

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 
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;

**Contrid** · Nov 7, 2012 14:48

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.

**Contrid** · Nov 7, 2012 14:50

Originally Posted by cpradio

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.

**Contrid** · Nov 7, 2012 14:58

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

**cpradio** · Nov 7, 2012 15:40

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.

**r937** · Nov 7, 2012 15:58

Originally Posted by Contrid

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

**Contrid** · Nov 8, 2012 01:28

Originally Posted by r937

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?

**r937** · Nov 8, 2012 02:51

Originally Posted by Contrid

How do you format your SQL nicely that way?

spaces and line breaks

**K. Wolfe** · Nov 8, 2012 08:21

Originally Posted by Contrid

Thank you for your reply @r937

Finish your tag with a space and a ; like so, @Contrid ;

**Contrid** · Nov 8, 2012 08:24

Originally Posted by K. Wolfe

Finish your tag with a space and a ; like so, @Contrid ;

Thanks, seems to work well @K. Wolfe ;

User Tag List

Thread: Next Record by Order

Thread Tools

Display

Next Record by Order

Bookmarks

Bookmarks

Posting Permissions