0

In one of my database table, I have a a column containing values separated by commas like '127,765,715,716'.

Colums are : id, firstname, skills (with commas values).

I need to write a query and to retrieve all the skills. For the moment I do :

  1. a simple select
  2. a foreach for looping through all rows.
  3. Inside that foreach, if "skills" isn't empty, I explode the "skills column" containing commas into an array.
  4. I again do a loop in the new skills array
  5. I make a new SELECT the to get the id, name, color of the skill (from a new table)

This is really cpu costy, once I get 200 rows, the cpu is at 100% and it takes 20 seconds.

Is there a way to do it better ?

lio
  • 185
  • 1
  • 3
  • 12
  • why not put each of those values into separate columns? You can use a join table to create a many-to-many relationship – Leland Barton Oct 10 '18 at 17:27
  • 2
    Let this be a lesson to properly normalize your tables. Things that are supposed to be very easy become complicated because your table is messed up. – Eric Oct 10 '18 at 17:30
  • @Eric, somewhere in my mind I expected a magic answer, but indeed, I will normalize, no choice. Thanks. – lio Oct 10 '18 at 18:14

0 Answers0