CODEWITHSUNDEEP
mysql
lightbord
lightbord

Reputation: 173

MySQL - Array in cell

My scenario is that I have a table that stores classes at a school. Each class is a row in the table. One of the fields in there is the class period that the class is offered in. Some are only offered one period, some are offered multiple periods, some are offered every period.

In the CSV I have that contains the data, the class period data, it is stored as a list of comma separated values with no spaces. For example, Algebra runs periods 1, 2, 3 and 4, so in the CSV its represented as "1,2,3,4".

My issue is when it comes to query for, for example, all classes that run during 3rd period, something like SELECT * FROM `classes` WHERE `period` LIKE '1' won't find it. Adding wildcard % characters doesn't help because it also picks up classes that run during 11th period.

How can I store these multiple values in the cell, and query for them?

EDIT: This is all data I was provided. There are 200+ classes, so reformatting all the data into another table and using JOIN (which would be the proper way to do this), would be incredibly time consuming. I am looking for a way to work with the data in the format I already have it in.

Upvotes: 0

Views: 145

Answers (2)

lainatnavi
lainatnavi

Reputation: 1613

This is better done by creating a relations table between classes and periods.

create table class_period(
  class_id int,
  period int
)

Then you can query the classes with period 1

select * from classes c join class_period p on c.id = p.class_id where p.period = 1

Upvotes: 2

Peter Paul Kiefer
Peter Paul Kiefer

Reputation: 2134

If you insist to do this with your actual db structure there are some fancy methods. Here is one.

Store the data as e.g. "|1|3|4|11|" in column period. now you have unique strings to search for. ("|1|" != "|11|")

Example 

SELECT * FROM `classes` WHERE `period` LIKE '%|1|%';

If you use a little program you can derive the also mention reference table with little effort. The "join" proposal, is really the best way.

Upvotes: 0

Related Questions