Hi folks,

I have a strange situation here and I simply can't come up with a solution. What started as a normal company listing, sorted alphabetically, has taken a strange turn with my client. He would like the results to 'rotate' arbitrarily (every 5 days) so that the listing that was in the first position of the results will be shifted to last, then the second company will be first, the 3rd second, and so on. They were originally sorted alphabetically, but now they'll be alphabetically but starting with "B" for example, going through "Z", then showing the "A" records.

Here is how the data is currently retrieved:

SELECT company_id, company_name, company_slogan, company_brief, company_stars, company_discount, company_phone, company_email
FROM companies
WHERE company_category = $_GET['company_category']
ORDER BY company_name ASC

Is there a way to adjust that statement to have an alphabetical result, but starting with record #2 -> end, then showing #1?

It would have to be dynamic, adjusting the starting record according to a 5-day interval, but I'm sure I can figure that out. I just don't even know where to start with this one.

Thanks to anyone who can help!
-DW

Hi Rudy, thanks for your response so quickly. Could you help me understand why 9 and 37 are the numbers you used here? I was planning on modifying it to fit the situation, i.e. the 5-day interval, or have only one company go to the back of the line and keep the other A's etc.

Your technique works well though, I just gave it a run and it does exactly what I hoped, thanks for that!

-DW

9 & 37, especially when joined together are a magic number as far as Rudy is concerned.

you could use any numbers providing the one you want first is has the lowest value

in the first line of the CASE expression, instead of 'B', just code the name of the company you want at the top of the list

OK this works great as long as there is a definable company name that I add to the SELECT, but what about a more all-encompassing query that could sort any list? For example, in some pages there will be A's, B's, C's etc., but some pages will only have two or three companies starting with other letters.

The idea I suppose was to have a sort of conveyor system that rotates the companies alphabetically every so often.

Thanks for this by the way, you're being incredibly helpful.
-DW

Hi folks,

I thought I'd come back with a solution I came up with that may help somebody some day

The solution here was to use UNION with two LIMITed SELECT statements. The first one selects the whole set minus the "offset" number which is related in my case to the day of the year, and the second one selects the first three that got left out.

(SELECT company_id, company_name, company_slogan, company_brief, company_stars, company_discount, company_phone, company_email
FROM companies
WHERE company_category = $_GET['company_category']
ORDER BY company_name ASC
LIMIT $conveyor_offset , $maxRows_category_list)
UNION
(SELECT company_id, company_name, company_slogan, company_brief, company_stars, company_discount, company_phone, company_email
FROM companies
WHERE company_category = $_GET['company_category']
ORDER BY company_name ASC
LIMIT 0,$conveyor_offset )

The offset was a couple of lines of PHP like this:
$dayofyear = date("z");
$conveyor_offset = (round($dayofyear / 5) % $maxRows_category_list);

...so for what I figure it will loop through to the next record after 5 days, and then repeat the procedure until all records have looped.

Thanks for the help! It got me started on the path for sure.