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.
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?
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
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?
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"
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.
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
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
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.
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
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;
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.
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
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([COLOR="#0000FF"]zx[/COLOR].order)
FROM wp_wpbrzonesads AS [COLOR="#0000FF"]zx[/COLOR]
WHERE [COLOR="#0000FF"]zx[/COLOR].order > 2
AND [COLOR="#0000FF"]zx[/COLOR].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' [COLOR="#FF0000"][B])[/B][/COLOR]
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([COLOR="#0000FF"]zx[/COLOR].order)
FROM wp_wpbrzonesads AS [COLOR="#0000FF"]zx[/COLOR]
WHERE [COLOR="#0000FF"]zx[/COLOR].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' [COLOR="#FF0000"][B])[/B][/COLOR]
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