CODEWITHSUNDEEP
phpmysqlsql
Khan
Khan

Reputation: 341

MySQL Equivalent to PHP Explode()

Suppose, I have a MySQL column in a table with following data

1,2,3. Actually, these are primary_key of another table. Now, Let me say what I am trying to do.

I would like to explode these using comma (,) and want to create a sub query to get the information from another table.

I am giving two table data here, hope that will help you to understand properly..

tableA

------------------------------------
| col1       | col2      | col3    |
------------------------------------
| 1          | Sam       | 50      |
------------------------------------
| 2          | Zor       | 55      |
------------------------------------
| 3          | Sad       | 40      |
------------------------------------

tableB

--------------------------------
| col1       | idFromTableA    |
--------------------------------
| 1          | 1,3             |
--------------------------------
| 2          | 1,2             |
--------------------------------
| 3          | 1,2,3           |
--------------------------------

Now, I am querying from tableB. Something like following query-

SELECT * FROM tableB

I want to pick the col2 from tableA using idFromTableA. I know about GROUP_CONCAT but I can't understand.

How can I pick col2 value using id stored in tableB named idFromTableA? I want to explode the value of idFromTableA and want to create a sub-query to get the value from tableA. Can I achieve this with this logic?

After @Phil's suggestion I am creating another table and storing the data as follows- Is that okay?

mysql> select * from bb_user_branches;
+-------+---------+-----------+
| ub_id | user_id | branch_id |
+-------+---------+-----------+
|     1 |       5 |         1 |
|     2 |       5 |        29 |
+-------+---------+-----------+
2 rows in set (0.00 sec)

Upvotes: 0

Views: 638

Answers (1)

Phil
Phil

Reputation: 164730

Make a junction table to facilitate the many-to-many relationship, eg

CREATE TABLE `tableA_B` (
    a_col1 INT NOT NULL, -- or whatever the data type is for the foreign key
    b_col2 INT NOT NULL,
    PRIMARY KEY (a_col1, b_col1),
    FOREIGN KEY (a_col1) REFERENCES tableA (col1),
    FOREIGN KEY (b_col1) REFERENCES tableB (col1)
) ENGINE=InnoDB;

You can then insert the relevant data

INSERT INTO tableA_B (a_col1, b_col1) VALUES
(1, 1), (3, 1),
(1, 2), (2, 2),
(1, 3), (2, 3), (3, 3);

Then you can perform your select...

SELECT b.col1, a.col2
FROM tableB b
INNER JOIN tableA_B ab ON b.col1 = ab.b_col1
INNER JOIN tableA a ON ab.a_col1 = a.col1;

Optionally, remove the idFromTableA column from tableB...

ALTER TABLE `tableB` DROP COLUMN `idFromTableA`;

You may want to try and migrate the comma separated values into your junction table instead of using the INSERT statement above. You'd probably want to use a programmable client interface (like PHP + PDO / mysqli) to do this.

Upvotes: 2

Related Questions