Select mixed value from a column in MySql

Question

I am having registration table, in that student grades are 1,2,3,4,5,6,7,8,9,10,11,12 and after sometimes client has changed the grade values to Grades 1 - 3, Grades 4 - 6, Grades 7 - 12

The MySql DB column has the Grades value of 1,2,3,4,5,6,7,8,9,10,11,12,Grades 1 - 3, Grades 4 - 6, Grades 7 -12.

Now I am creating the report, so I need to filter the grades based on 1,2,3,4,5,6,7,8,9,10,11,12 but I need to select the Grades 1 and also the record has Grades 1 - 3.

If I use the SELECT * FROM registration WHERE Grades LIKE "%1%" means I am also getting the Grades 1,10,12,Grades 1 - 3

Any suggestions or help is appreciated.

If you have a situation where client can input anything they like then there can be no definitive solution.. — P.Salmon, Oct 05 '20 at 13:39
Now you know why comma delinited lists in a column are a bad idea, they are a processing nightmare — RiggsFolly, Oct 05 '20 at 13:41
Are you saying that the grades are not stored in a comma delimited list in a single column? — RiggsFolly, Oct 05 '20 at 13:44
It's going to be tricky selecting where grade = 2 and you have grade13 stored (or grade 1- 3 or grade1 -3) — P.Salmon, Oct 05 '20 at 13:45
@RiggsFolly Yes, It is stored either from 1 to 12 or Grades 1 - 3, Grades 4 - 6, Grades 7 - 12 — Boopathi D, Oct 05 '20 at 13:46
It's possible of you have cast iron rules for grade formats. — P.Salmon, Oct 05 '20 at 13:47

score 0 · Accepted Answer · answered Oct 05 '20 at 14:05

0

This simple answer solves my problem,

SELECT Grades FROM registration WHERE Grade ="1" OR Grade ="Grades 1 - 3"

Based on the query from user, using if condition(in php) the Grades in the WHERE clause will be populated.

answered Oct 05 '20 at 14:05

Boopathi D

361
2
21

Does it? It seems you have to know all the possible formats when searching for grade 1 - that's not really what programming is about - what would happen if grade 1 - 6 appeared without your knowledge..When writing code you should make it as general as possible to capture all the known and as many of the unknowns you can imagine. – P.Salmon Oct 05 '20 at 14:34
And trap any unimagined unknowns elsewhere so that you can finesse your code. – P.Salmon Oct 05 '20 at 14:35
@P.Salmon As I already said it is select box which has fixed value currently `Grades 1 - 3`, `Grades 4 - 6`, `Grades 7 - 12` – Boopathi D Oct 05 '20 at 14:36

Select mixed value from a column in MySql

1 Answers1