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.

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

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.

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.

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 DESCGiving the first SELECT in the union the priority because it will most likely be 1 or higher

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