CODEWITHSUNDEEP
phpmysql
Max
Max

Reputation: 860

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

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?

Upvotes: 0

Views: 633

Answers (2)

donald123
donald123

Reputation: 5749

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

Upvotes: 1

M Khalid Junaid
M Khalid Junaid

Reputation: 64476

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

Upvotes: 1

Related Questions