Select only items that contain the number in their value

Asked

Viewed 51 times

0

I have a table conversas:

STRUCTURE

+--------+---------+
|  Nome  |  Tipo   |
+--------+---------+
| id     | int     |
| users  | text    |
| titulo | varchar |
+--------+---------+

I need to select items that contain the logged-in user id in the field users. Example:

table conversations:

+----+---------+------------+
| id |  users  |   titulo   |
+----+---------+------------+
|  1 | 2;4;3;6 | Conversa 1 |
|  2 | 3;6;12  | Conversa 2 |
|  3 | 1;5;3;7 | Conversa 3 |
|  4 | 8;2;    | Conversa 4 |
|  5 | 1;12;3  | Conversa 5 |
+----+---------+------------+

User id: 12

Must return:

+----+--------+------------+
| id | users  |   titulo   |
+----+--------+------------+
|  2 | 3;6;12 | Conversa 2 |
|  5 | 1;12;3 | Conversa 5 |
+----+--------+------------+

I tried to use:

SELECT * FROM `conversas` WHERE users IN (12)

and it didn’t work very well. How should I do?

-EDIT

I also own a table users and mensagens.

users

+--------+---------+
|  Nome  |  Tipo   |
+--------+---------+
| id     | int     |
| nome   | varchar |
+--------+---------+

mensagens

+-------------+----------+
|    Nome     |   Tipo   |
+-------------+----------+
| id          | int      |
| id_user     | int      |
| id_conversa | int      |
| mensagem    | text     |
+-------------+----------+
  • How about normalizing? It would be much more practical to bring the results you need. In the current way a risky alternative that would require a lot of attention would be using 'LIKE''

  • As well as "normalize"?

  • 3

    You can help => http://answall.com/a/1792/91

  • 2
  • The code SELECT * FROM conversas WHERE FIND_IN_SET('12',users); works but if I put SELECT * FROM conversas WHERE FIND_IN_SET('12,7',users); It doesn’t work

1 answer

-1

SELECT * FROM tabela_conversas WHERE users LIKE '%12%';

Remembering that it would be more ideal to create a separate relationship table for this case.

  • i have a table users which has a field id

  • This does not work. If you have a record with (85;112;30) it will bring the result.

Browser other questions tagged

You are not signed in. Login or sign up in order to post.