Put the most popular option at first on a select box with mysql

Question

Hi there. I'm new on mysql, but I have a database with some data, ordered by name:

<option value="4"> George </option>
<option value="55"> John </option>
<option value="13"> Paul  </option>
<option value="24"> Ringo </option>

I want to put this on a select, like this:

   while($row = mysql_fetch_array($result) ){
        echo "<option value='".$row['id']."'>".$row['name']."</option>";
   }

But I want to tell the select to put just one option at the top, the most popular choice, like this:

<option value="55"> John (most popular)</option> 
<option value="4"> George </option>
<option value="13"> Paul </option>
<option value="24"> Ringo </option>

How Can I exclude just one row? I have to use mysql_fetch_array?

Is better to do this on the SQL? Or using a loop?

Thanks!

Answer 1

How about order by popularity? This is not exactly what you asked for, but it would be a lot simpler, and maybe it's more logical?

Answer 2

your best option is to do this in SQL. We need to see your schema and your current SQL to help properly though. It is worth noting that you can order by two columns, e.g.

SELECT * FROM foo ORDER  BY col1,col2

Answer 3

If you have to use the alphabetic order, you may have to split it into two queries, first query only to get the very item, and second to list the rest of the others.

I'm sure this runs in linear time, and sorting runs no faster(if not slower).

Answer 4

This should work:

(SELECT id, name||' (most popular)' AS name FROM table ORDER BY id DESC LIMIT 1)
UNION DISTINCT
(SELECT id, name FROM table)

Run the query, then render it into HTML. The other approach could be to use JavaScript to move the element with the highest id to the top after the HTML is rendered.