Next Record by Order

[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!

[DarthGuido]

Maybe this would do the trick?

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

[KyleWolfe]

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]

How can I automatically loop and get to 1 again?

the following handles wraparound and does not assume that the lowest order is 1

$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"

[KyleWolfe]

the following handles wraparound and does not assume that the lowest order is 1


$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]

Maybe this would do the trick?


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]

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]

the following handles wraparound and does not assume that the lowest order is 1


$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]

See that's a lot of connections

nope... one connection per user

[cpradio]

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

[KyleWolfe]

Why not this:

$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.

[KyleWolfe]

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]

Why not this:

because there's no wraparound, that's why

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

[KyleWolfe]

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]

the following handles wraparound and does not assume that the lowest order is 1


$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.

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]

Why not this:

`

$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]

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]

Maybe this would do the trick?


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]

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]

My resulting query is done and working well and contains some dynamic values of course.

damn, i can't read that

how about this --

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(<font color='"#0000FF"'>zx</font>.order) 
           FROM wp_wpbrzonesads AS <font color='"#0000FF"'>zx</font> 
          WHERE <font color='"#0000FF"'>zx</font>.order &gt; 2 
            AND <font color='"#0000FF"'>zx</font>.zone_id = 2 
   AND a.active = 'Y' 
   AND (a.expiry = '0000-00-00' OR a.expiry &gt;= '2012-11-07') 
   AND (a.startDate = '0000-00-00' OR a.startDate &lt;= '2012-11-07') 
   AND (a.impressions = 0 OR a.impressions &lt;= a.views) 
   AND a.id &lt;&gt; '1' <font color='"#FF0000"'>[B])[/B]</font> 
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(<font color='"#0000FF"'>zx</font>.order) 
           FROM wp_wpbrzonesads AS <font color='"#0000FF"'>zx</font>
          WHERE <font color='"#0000FF"'>zx</font>.zone_id = 2 
   AND a.active = 'Y' 
   AND (a.expiry = '0000-00-00' OR a.expiry &gt;= '2012-11-07') 
   AND (a.startDate = '0000-00-00' OR a.startDate &lt;= '2012-11-07') 
   AND (a.impressions = 0 OR a.impressions &lt;= a.views) 
   AND a.id &lt;&gt; '1' <font color='"#FF0000"'>[B])[/B]</font> 
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