PHP: Get specific elements from MySQL database to show on top of select options list

Question

I have a select menu on my website which displays all available countries. The countries come from my MySQL table which contains 10 countries, in a random order. I perfectly know how to retrieve all countries from my table and output them in my HTML select menu with PHP.

However, I was wondering what code-wise would be the most efficient way when I want to pick two specific countries to appear on top of my options list, with the other eight countries to be listed underneath? I would like to use as little sql and php as possible for achieving this goal.

Let's say I have the following table "countries":

   id   name
   1    United States
   2    France
   3    Brazil
   4    Puerto Rico
   5    India
   6    Australia
   7    Italy
   8    Denmark
   9    South Africa
   10   Oman

I would like to output the countries in a select menu and I want the list to start with Denmark and India (with their ids as option values).

My goal:

<select>
   <option value="8">Denmark</option>
   <option value="5">India</option>
   // ... the other eight countries underneath, in no specific order
</select>

The basic SQL query for retrieving all countries would be:

SELECT id, name FROM countries

How do I need to proceed for achieving my goal best?

donald123 · Accepted Answer · 2015-07-16 09:01:57Z

1

If you want an alphabetical ORDER as followers, you can also use this statement

   SELECT id, name 
     FROM countries
     ORDER BY id=8 DESC, id=5 DESC, name ASC

answered Jul 16, 2015 at 9:01

donald123

5,7943 gold badges29 silver badges24 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Max Over a year ago

almost perfect. in the resulting array, 'Denmark' and 'India' are the last items. But I want them to be the first array items. How do I achieve that?

donald123 Over a year ago

@Max i don't understand you ... this query returns an array Denemark, India, Australia, Brazil, ... and so on ...

M Khalid Junaid · Accepted Answer · 2015-07-16 09:00:01Z

1

You can use FIELD() function

SELECT id, name 
FROM countries
ORDER BY FIELD(name,'India','Denmark') DESC

or by using CASE

SELECT id, name 
FROM countries
ORDER BY CASE WHEN name = 'Denmark' THEN 0
              WHEN name = 'India' THEN 1
              ELSE 2 END

answered Jul 16, 2015 at 9:00

M Khalid Junaid

64.6k10 gold badges93 silver badges120 bronze badges

Collectives™ on Stack Overflow

PHP: Get specific elements from MySQL database to show on top of select options list

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related