CODEWITHSUNDEEP
phpmysql
Alex
Alex

Reputation: 827

Split in comma-separated string and show in MySQL and PHP

I have a small database:

+-----------+-----------+------------------------+
| Name      | Number    |   Hobby                | 
+-----------+-----------+------------------------+
| Alex      | 2, 3      | Game, Shopping         |
+-----------+------------------------------------+

It's mean Number 2 is Game and Number 3 is Shopping. How can I show above data like this table

+-----------+-----------+
| 2         | Game      |
+-----------+-----------+
| 3         | Shopping  |
+-----------+------------

Upvotes: 0

Views: 98

Answers (2)

asiby
asiby

Reputation: 3389

Your database is not normalized. You need a third table that will be what's usually called a join table.

The people table. The primary key is id

+-----------+-----------+
| Id        | Name      |
+-----------+-----------+
| 1         | Alex      |
| 2         | Thor      |
| 3         | Iron Man  |
| 4         | Dr Stange |
| 5         | Thanos    |
+-----------+------------

The hobbies Table

+-----------+-----------+
| Id        | Name      |
+-----------+-----------+
| 1         | Game      |
| 2         | Shopping  |
| 3         | Fighting  |
+-----------+-----------+


Join table called (for example) people_hobbies

+-----------+-----------+
| person_id | hobby_id  |
+-----------+-----------+
| 1         | 1         |
| 1         | 2         |
+-----------+-----------+

This people_hobbies table will use person_id and hobby_id to create a multi field primary key. This will ensure that you will not be able to add the same combination twice... which should not even make sense.

person_id is a foreign key that references the id from the people table. hobby_id is a foreign key that references the id from the hobbies table.

Having foreign keys will let you avoid having a key in the people_hobbies table that do not exist in both the people and the hobbies table.

The example in the table below shows that the person id 1 has two hobbies (1 and 2). For a human, that translates to Alex's hobbies are Game and Shopping.

The above structure will let you manage your DB the way most people do.

Just keep a few things in mind:

  1. You cannot add anything in people_hobbies before they exist in both people and hobbies tables
  2. You must have the CASCADE UPDATE and CASCADE DELETE to the foreign key definitions so that when you delete a person or a hobby from your tables, it will remove the relationship from the people_hobbies table.

Upvotes: 1

Strawberry
Strawberry

Reputation: 33935

SELECT * FROM ints;
+---+
| i |
+---+
| 0 |
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |
| 6 |
| 7 |
| 8 |
| 9 |
+---+

SELECT * FROM bad_schema;
+------+--------+----------------+
| name | number | hobby          |
+------+--------+----------------+
| Alex | 2, 3   | Game, Shopping |
+------+--------+----------------+

CREATE TABLE better_schema AS
SELECT DISTINCT name
              , SUBSTRING_INDEX(SUBSTRING_INDEX(number,',',i+1),',',-1) + 0 number
              , TRIM(SUBSTRING_INDEX(SUBSTRING_INDEX(hobby,',',i+1),',',-1)) hobby
           FROM bad_schema
              , ints;


SELECT * FROM better_schema;
+------+--------+----------+
| name | number | hobby    |
+------+--------+----------+
| Alex |      2 | Game     |
| Alex |      3 | Shopping |
+------+--------+----------+

Upvotes: 0

Related Questions