order by filed that matches string

Question

I have a SQL database that contains a table called 'users' with a column called industriesrepresented

on my page, I have a string like $myindustry = '97'

in the field in my DB, users select from various industries which each have a unique id. ie - 97,89,83,44,42

I'm then running a query (SELECT * FROMusers)

but I would like to put it in order and display the users that have got for example '97' entered in the column 'industriesrepresented'

and put them at the top of the list.

hope this makes sense

Answer 1

You can use LIKE.

...
ORDER BY concat(',', industriesrepresented, ',') LIKE '%,97,%' DESC

The expression will yield an (implicit) Boolean, that is 1 if 97 is in the column 0 otherwise. Since 1 > 0 you have to use DESC.

Answer 2

SELECT * FROM users ORDER BY find_in_set("'.$myindustry.'", industries)  DESC

Another solution

select * 
from users 
order by (case industries when "'.$myindustry.'" then 0 else 1 end)

Answer 3

This is not how a DB is supposed to be used.

First, think about the relations between users and industries.

• An unique user can represent 0 to many (n) industries.
• A single industry can be represented by 0 to many (n) users.

The relation between users and industries is (0, n) - (0, n). Commonly called Many To Many

You have 2 tables, users and industries that can be represented this way :

users

 id | name

industries

 id | name

In SQL, to build this kind of relationship, you need a 3rd table that contains the foreign keys of both tables

users_industries

user_id | industry_id

To avoid duplicated entries, the primary key of the 3rd table is the composition of both FK

Schema (MySQL v5.7)

CREATE TABLE users
(
  id INT(6) PRIMARY KEY NOT NULL AUTO_INCREMENT,
  name VARCHAR(255)
);

CREATE TABLE industries
(
  id INT(6) PRIMARY KEY NOT NULL AUTO_INCREMENT,
  name VARCHAR(255)
);

CREATE TABLE users_industries
(
  user_id INT(6) NOT NULL,
  industry_id INT(6) NOT NULL,
  FOREIGN KEY (user_id) REFERENCES users(id),
  FOREIGN KEY (industry_id) REFERENCES industries(id),
  PRIMARY KEY (user_id, industry_id)
);

INSERT INTO users VALUES (default, "John"), (default, "Jane"), (default, "Bob"), (default, "Mary");

INSERT INTO industries VALUES (default, "Renault"), (default, "Peugeot"), (default, "Citroen"), (default, "Venturi");

INSERT INTO users_industries VALUES (1, 1), (1, 3), (2, 1), (2, 2), (2, 3), (2, 4), (3, 2), (3, 3), (3, 4), (4, 4);

---

Query #1 : How to get all users with their respectives industries ?

SELECT u.id AS "User id",
       u.name AS "User name",
       i.id AS "Industry id",
       i.name AS "Industry name"
FROM users u
LEFT JOIN users_industries ui
ON u.id = ui.user_id
LEFT JOIN industries i
ON i.id = ui.industry_id
ORDER BY u.id;

Output

| User id | User name | Industry id | Industry name |
| ------- | --------- | ----------- | ------------- |
| 1       | John      | 1           | Renault       |
| 1       | John      | 3           | Citroen       |
| 2       | Jane      | 1           | Renault       |
| 2       | Jane      | 2           | Peugeot       |
| 2       | Jane      | 3           | Citroen       |
| 2       | Jane      | 4           | Venturi       |
| 3       | Bob       | 2           | Peugeot       |
| 3       | Bob       | 3           | Citroen       |
| 3       | Bob       | 4           | Venturi       |
| 4       | Mary      | 4           | Venturi       |

---

Query #2 : How to get the industries names of a specific user ?

SELECT i.name AS "Industry name"
FROM industries i
LEFT JOIN users_industries ui
ON i.id = ui.industry_id
WHERE ui.user_id = 3
ORDER BY i.id;

Output

| Industry name |
| ------------- |
| Peugeot       |
| Citroen       |
| Venturi       |

---

Query #3 : How to get the user names of a specific industry ?

SELECT u.name AS "User name"
FROM users u
LEFT JOIN users_industries ui
ON u.id = ui.user_id
WHERE ui.industry_id = 3
ORDER BY u.id;

Output

| User name |
| --------- |
| John      |
| Jane      |
| Bob       |

---

View on DB Fiddle