How to split row into multiple rows from the MySQL?

Question

I have a MySQL data table, in which I have more than 2 columns. First column has a unique value clinical trial value whereas second column has disease information. There are, in most of the cases, more than 2 disease names in one cell for a single id. I want to spilt those rows which cell contains two or more than two diseases. There is a pattern for searching also, i.e. small character is immediately followed by capital character., e.g. MalariaDengueTuberculosis like this. Suppose for these three diseases there is unique id, it should show like the following:

      NCT-ID           disease
     4534343654       Maleria
     4534343654       Dengue
     4534343654       Tubercoulsosis

Where do the NCT-ID's come from? Could you show what the data looks like at the moment? — Samuel O'Malley
– Samuel O'Malley, Commented Oct 30, 2013 at 9:19
This is the row with three columns, "|" is the separator : NCT01766830 | Visceral LeishmaniasisHuman African TrypanosomiasisEntericFeverMelioidosisBrucellosisLeptospirosisRelapsing FeverRickettsial DiseasesHIVTuberculosisMalariaAmoebic Liver Abscess | N/A — user2818537
– user2818537, Commented Oct 30, 2013 at 9:35
So each of those diseases would have the same ID of "01766830"? — Samuel O'Malley
– Samuel O'Malley, Commented Oct 30, 2013 at 9:41
Where did the data come from before they were in your database? I think the original data would probably be easier to work with. Those disease names are a little messy and would be difficult to split. — Samuel O'Malley
– Samuel O'Malley, Commented Oct 30, 2013 at 10:14

Theox · Accepted Answer · 2013-10-30 09:21:02Z

1

If you want to store one or more data in one String column, you could use JSON data formatting.

answered Oct 30, 2013 at 9:21

Theox

1,3639 silver badges20 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

jazZRo · Accepted Answer · 2013-10-30 09:50:29Z

0

It is not very clear to me what you're trying to achieve. Since you're using PHP you can try to read the tables using PDO and print the results. For example:

$sql = 'SELECT diseases FROM diseases_info_table';
foreach($pdo->query($sql) as $row) {
    $diseases = preg_split('/(?=[A-Z])/', $row['diseases']);
    $sql2 = 'SELECT * FROM diseases WHERE disease_name IN ("' . implode('","', $diseases) . '")';
    foreach ($pdo->query($sql2) as $row) {
       print $row['NCT-ID '] . "\t";
       print $row['disease'] . "\t";
    }
}

But this way you're generating a lot of queries. If it is possible for you to rethink the database structure than I would recommend doing that.

answered Oct 30, 2013 at 9:50

jazZRo

1,6081 gold badge16 silver badges20 bronze badges

2 Comments

Samuel O'Malley Over a year ago

Something you're missing is diseases can be two words. What actually separates the diseases is a lowercase letter followed by an upper case letter. [a-z][A-Z]

jazZRo Over a year ago

Actually the preg_split separates words by uppercase characters creating an array of the two diseases. Tested it and "MalariaDengueTuberculosis" becomes Array ( [0] => [1] => Malaria [2] => Dengue [3] => Tuberculosis ). The first could be skipped.

webnoob · Accepted Answer · 2013-10-30 10:08:13Z

To answer your question:

If you insist on multi data in one column you can use php explode:

$diseases = explode("|", $row['disease']); //Changing | to whatever separates your diseases.

$diseases is now an array of your diseases which you can do:

foreach ($diseases as $disease)
{
    echo $disease;
}

However

Personally I would normalise your database now before trying to hack around solutions. Use an ID against your table and then have a diseases table to link to it. ie:

Main table

Disease Table

disease_id  nct_id          disease
1           4534343654      Broken Wind
2           4534343654      Chronic Nosehair
3           4534343654      Corrugated Ankles
4           5768788544      Discrete Itching
5           3i33i3i078      Gastric Ejections
6           3i33i3i078      Bloaty Head

This allows multiple diseases against one nct-id. disease_id would be the primary key.

Collectives™ on Stack Overflow

How to split row into multiple rows from the MySQL?

3 Answers 3

Comments

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related