0

I have a table like this:

// mytable
+----+--------+-------------------+
| id |  word  |      numbers      |
+----+--------+-------------------+
| 1  | hello  | 1<br>2<br>3<br>   |
| 2  | how    | 12<br>15<br>      |
| 3  | are    | 453<br>1<br>      |
| 4  | you    | 3<br>33<br>453<br>|
+----+--------+-------------------+

And I want this output:

// mynewtable
+----+--------+---------+
| id |  word  | numbers |
+----+--------+---------+
| 1  | hello  | 1       |
| 2  | hello  | 2       |
| 3  | hello  | 3       |
| 4  | how    | 12      |
| 5  | how    | 15      | 
| 6  | are    | 453     |
| 7  | are    | 1       |
| 8  | you    | 3       |
| 9  | you    | 33      |
| 10 | you    | 453     |
+----+--------+---------+

I can do that using PHP. First I have to fetch all rows and implode them by <br> then insert them again. But now I want to know, is there any better solution? (specially using pure MySQL)

Shafizadeh
  • 9,960
  • 12
  • 52
  • 89
  • 3
    Possibly this would be a good time to refactor a rather badly designed piece of your database – RiggsFolly Dec 02 '15 at 13:41
  • If you can replace the `
    ` with a single character then [Can I resolve this with pure mysql? joining on ';' separated values in a column](http://stackoverflow.com/a/33806675/3184785) maybe interesting. You could change the functions to use `
    ` as the separator. It will give the output you want.     – Ryan Vincent Dec 02 '15 at 13:50
  • What @RiggsFolly said - time to refactor and fix things. Whoever designed the first model had no clue how databases are used. You can either hack around and try to get it working, facing numerous problems along the way or you can simply create a table that stores the info like you've shown in the output example. Good luck, I do hope you decide to do it the right way because that will be the least amount of work. – Mjh Dec 02 '15 at 13:56
  • I agree with @Mjh. While Ryan Vincent points you in the right direction in terms of selecting/adjusting data in a query, the next problem you then face is to turn your selected data into an `INSERT` query. It's just going to get **very** complex and messy very quickly. – Byson Dec 02 '15 at 14:04
  • Just to make it clear. I always use normalized tables. The only reason I did that answer was to find some 'standard way' of dealing with these 'pesky' `foreign keys in delimited strings`. The functions are companions of `FIND_IN_SET` ;-/ – Ryan Vincent Dec 02 '15 at 14:30
  • There are other solutions. They are not better. – Strawberry Dec 02 '15 at 14:32
  • @Strawberry I think the answer of *Simhachalam Gulla* can be fine. (it is under my question) – Shafizadeh Dec 02 '15 at 14:37

2 Answers2

2

Try this solution through query

SELECT @row := @row + 1 as row, t.word, t.number FROM (
    SELECT
      word,
      SUBSTRING_INDEX(SUBSTRING_INDEX(numbers, '<br>', n.digit+1), '<br>', -1) number
    FROM split_rec
    INNER JOIN 
        (SELECT 0 digit UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3) n
        ON LENGTH(REPLACE(numbers, '<br>' , '')) <= LENGTH(numbers)-n.digit
    ORDER BY id, n.digit
) AS t, (SELECT @row := 0) r 
WHERE t.number <> ''

Output

row word    number
1   hello   1
2   hello   2
3   hello   3
4   how     12
5   how     15
6   are     453
7   are     1
8   you     3
9   you     33
10  you     453
Simhachalam Gulla
  • 996
  • 7
  • 16
0

While I think that it might be possible using stored procedures, MySQL variables and a liberal use of LIKE "%...%"statements, it would be an order of magnitude more complex than a PHP solution.

Furthermore, I think it is not possible at all if you don't have access to stored procedures as you would need dynamically create new INSERT statements based on what you find in that last column.

TL;DR: No, make yourself happy and use PHP.

Byson
  • 540
  • 4
  • 19