CODEWITHSUNDEEP
phpmysql
user2818537
user2818537

Reputation: 159

How to split row into multiple rows from the MySQL?

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

Upvotes: 0

Views: 1378

Answers (3)

webnoob
webnoob

Reputation: 15934

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

     NCT-ID           
     4534343654       
     5768788544       
     3i33i3i078

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.

Upvotes: 0

jazZRo
jazZRo

Reputation: 1608

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.

Upvotes: 0

Theox
Theox

Reputation: 1363

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

Upvotes: 1

Related Questions