PHP> Sort query results by name while letting each letter be on the top sometimes

I'm cu开发者_StackOverflowrrently working on a site that will display a list of online shops, Each shop will be stored on my database and I'll be using PHP to select and display them.

But since those shops will pay me, I want to let each shop to be on the top of the list sometimes, (for example if the shop name starts with a "Z", they will probably complain for being on the bottom of the list all the time, so I want to keep it fair).

So I thought about letting each letter be on the top of the list for an hour, but i have no idea how to do that.. Is that even possible?

Thanks in advance!

I'd show a separate box and call it "today's pick" or something with just one shop in it. That way you can push the shops starting with "Z" to the top once in a while and at the same time keep the user experience of a list of shops which is sorted normally.

Then use the database to save which shop has been in the "today's pick"-box how many times to get them all up there equally.

There's no sane way (that I'm aware of) to handle this directly in SQL without adding a "priority" field to your schema (although it's possible, it would be convoluted at best). That said, here are two suggestions:

Modify your schema

Simply add a "priority" field to the relevant schema and sort by priority, name (or whatever the default is). You will of course need to reset the priority field every hour, but this is a fairly trivial task.

Handle it in PHP

1. Carry out the query as per usual.

2. Grab all the results into an array.

3. Re-prioritise as required based on the current hour. (You'll need to array_splice the item(s) you want to bump out of the array and then array_unshift them to the top.)

4. Output based on the array.

This will of course become more convoluted/less efficient if you need to handle pagination, but the basic idea is the same.

A nice solution would be to add another column to the database with the shop names, and call it something like "last_shown" then when you show this shop, update the column with a timestamp, and each select do something like:

"SELECT name,link FROM shops ORDER BY last_shown DESC"

then in php you could check

<?php

if($row['last_shown']+3600 > now()){
    //run select but in ASC order
    //update the new row's column to the current timestamp

}



?>

that way it will only update once an hour, but otherwise it will keep selecting the shop at the top of the list for the hour

sorry it's a bit of a mess i just typed this out quickly at work

You can add 1) a extra column as shown_times in schema 2) order by shown_times asc 3) & as a shop is shown you would +1

or

Another solution : You can even use ORDER BY RAND()