1

I have this old posts database that looks like this

[id       title       content     tags]
---------------------------------------
[1      PHP Post    LoremIpsum      1 ]
[2      CSS Post    LoremIpsum      2 ]

The tags were made badly since i knew less about MYSQL and PHP, And the table tags is like this 

[id            tags     ]
-------------------------
[1       PHP, PDO, MYSQL]
[1       CSS, CSS3, LESS]

How can i turn this table from its form into this new form

[id      tag ]
--------------
[1       PHP ]
[1       PDO ]
[1      MYSQL]
[2       CSS ]
[2      CSS3 ]
[2      LESS ]
Calibur Victorious
  • 638
  • 1
  • 6
  • 20
  • 1
    Create the new table. select all from the old one. iterate over it and `explode()` the tags. insert each tag with the given id into the new table. – JustOnUnderMillions Mar 15 '17 at 10:45
  • 1
    http://stackoverflow.com/questions/17942508/sql-split-values-to-multiple-rows – ewcz Mar 15 '17 at 10:46
  • @ewcz nice link (really the same question), but the answers there are way to complex (huge SQLs). its better done in php. – JustOnUnderMillions Mar 15 '17 at 10:48
  • @JustOnUnderMillions I didn't get it, Do i have to type the tags manually again? And if i used `explode()` wouldn't that make only the first row has the id but the others contain null id? – Calibur Victorious Mar 15 '17 at 11:00
  • 1
    If you `SELECT *` each `$row` has e.g. `['id'=>1,'tags'=> 'PHP, PDO, MYSQL']`, there you do `explode(',',$row['tags'])` to `['PHP', 'PDO', 'MYSQL']`. now you can `INSERT` each tag with the `id=>1` into the new table. It is not so hard. – JustOnUnderMillions Mar 15 '17 at 11:03
  • @JustOnUnderMillions Well, that cleared it up, Thanks. – Calibur Victorious Mar 15 '17 at 11:06
  • Tip: use `trim()` on the single tag to remove spaces before insert. – JustOnUnderMillions Mar 15 '17 at 11:07
  • @JustOnUnderMillions This isn't your first rodeo. You know your solutions should be posted as answers, but you always only comment. Would you mind consolidating your comments into an answer so Calibur can award it a green tick and move this question off of the unanswered pile? – mickmackusa Mar 15 '17 at 12:23
  • @mickmackusa `always only comment` There is no code (tryout) from the OP, normally this question should be closed. Nobody codes for free, aspecialy on SO! But at this point i will give most of the time hints in the comment before it get closed. If then the OP itself ask me via comment something, what should id do? Ignore him. And by the way what should i post in the answer?Full ready useable code or typical scream of: dont use mysql because its deprecated? By the way im not here for points. – JustOnUnderMillions Mar 15 '17 at 12:39

1 Answers1

0

To give an answer

  • create the new table
  • select all entries from the old table SELECT *
  • iterate over the rows and do explode(',',$row['tags'])
  • insert each tag with the given id into the new table ['id'=>1,'tags'=> 'PHP']

Thats it, now make code from that.

JustOnUnderMillions
  • 3,741
  • 9
  • 12