write select query for the below requirement

Question

This is my users table:

I need to select all rows those are not contain 2 in except column. How to write a query for this using php & Mysql.

The result i expect for this query is only return last row only.

Thank you.

score 3 · Accepted Answer · answered Sep 05 '14 at 06:30

3

Don't store comma separated values in your table, it's very bad practice, nevertheless you can use FIND_IN_SET

SELECT
  *
FROM 
  users
WHERE 
  NOT FIND_IN_SET('2', except)

answered Sep 05 '14 at 06:30

Ende Neu

1

can you please tell me why we should not store comma separate value in table? just want to know – Rakesh Shetty Sep 05 '14 at 06:34
2

Because you need an additional work to make them in readable or workable format. THat's why MySQL is relational database, and you can use the well-known relations e.g. many to many – Royal Bg Sep 05 '14 at 06:35
1

There's [this comprehensive SO post](http://stackoverflow.com/questions/3653462/is-storing-a-delimited-list-in-a-database-column-really-that-bad/3653574#3653574) about storing comma separate values in a columns. – Ende Neu Sep 05 '14 at 06:36

score 1 · Answer 2 · answered Sep 05 '14 at 06:30

1

Try this:

SELECT *
FROM users 
WHERE CONCAT(',', except, ',') NOT LIKE '%,2,%'

answered Sep 05 '14 at 06:30

Saharsh Shah

score 0 · Answer 3 · answered Sep 05 '14 at 06:33

0

this should work for you

SELECT *
FROM table
WHERE table.except NOT LIKE '%,2%'
OR table.except NOT LIKE '%2,%';

answered Sep 05 '14 at 06:33

nvm-uli

3 Answers3