php - How to Convert Comma Seperated Column into rows and add counter

Question

I am having a table with these values

Table : Documents

id  |       document
----|-------------
1   |   doc.txt , doc1.txt , doc2.rtf , doc3.docx , doc4.doc
2   |   doc.txt 
3   |   doc.txt , doc1.txt 
4   |   doc.txt , doc1.txt , doc2.rtf 
5   |   doc.txt , doc1.txt , doc2.rtf , doc3.docx , doc4.doc
6   |   doc.txt , doc1.txt , doc2.rtf , doc3.docx 
7   |   doc.txt , doc1.txt , doc2.rtf , doc3.docx , doc4.doc
8   |   doc.txt , doc1.txt , doc2.rtf 
9   |   doc.txt , doc1.txt , doc2.rtf , doc3.docx , doc4.doc
10  |   doc.txt , doc1.txt 

SQL FIDDLE SCHEMA

I need result like this. Where id = 5

Counter |   docs
    ----|-----------
    1   |   doc.txt
    2   |   doc1.txt
    3   |   doc2.rtf
    4   |   doc3.docx
    5   |   doc4.doc

Where id = 4

Counter |   docs
    ----|-----------
    1   |   doc.txt
    2   |   doc1.txt
    3   |   doc2.rtf

You see i need to explode comma seperated column and count how many values are there. I dont like this schema but i am working on an existing project and can not change it. I need counter to display in the user interface. So counter is necessary too. How can i do that? Also i can not do it on the php end because i am using pyrocms and i need to display it using pyrocms tage which does not allow me to use php in the views.

Answer 1

Look at this SQL FIDDLE , maybe it is that you want. You must use helper table sequence

SELECT * FROM
(SELECT S.Id Counter,
REPLACE(SUBSTRING(SUBSTRING_INDEX(document, ' , ', S.Id),
       LENGTH(SUBSTRING_INDEX(document, ' , ', S.Id - 1)) + 1),
       ' , ', '') docs
FROM documents D, sequence S
WHERE D.id = 3) A
WHERE A.docs <> ''