Splitting MySQL column that contains comma-separated list

[rvanderh]

I’m working on an application that includes a form with multiple checkboxes (over 400) for one variable (a list of journal titles). When the form is submitted the items that are checked get stored in a MySQL 5.x table column as a comma-separated list. I want to output the data in a report that lists the checked values as a vertical list rather than comma-separated. So instead of this:

Item 2,Item 6,Item 124

It would look like this:

Item 2
Item 6
Item 124

I have searched a lot on StackOverflow.com and have found questions that are similar to mine, but the solutions aren’t working. For example, one posting suggested using the MySQL function GROUP_CONCAT() but from what I can tell that function does the opposite of what I’m trying to do. Another suggestion was to use a MySQL stored procedure but from the examples I’ve seen I can’t make sense of how to proceed. I also tried the explode() function but I don’t know PHP well enough to make it work.

My usual environment is Drupal but I didn’t think it could handle the reporting requirements for this application, so I ventured into trying it out in PHP. Could someone point me in the right general direction for what I’m trying to do? Thanks.

[WolfShade]

Do a preg_replace(), find the commas, replace with line breaks.

(If I remember correctly, explode() turns a comma delimited list into an array that you can iterate through.)

HTH,

[rvanderh]

Thanks for the reply; much appreciated.

I’m getting a warning, “preg_replace(): No ending delimiter ‘,’ found…”. Here is the code I’m using in the reporting template:

$num=mysqli_num_rows($result);

for ($i=0; $i<$num; $i++) {
$row = mysqli_fetch_assoc ($result);

$jtitle = preg_replace(‘,’,‘
’,‘$jtitle’);

    echo "<tr><td width=\"30%\">$row[jtitle]</td></tr>";

}

Where $result is the SQL query result from the table storing the form submissions. Am I placing the preg_replace() in the correct part of the template?

[rvanderh]

Sorry, HTML br element didn’t display correctly. That should be $jtitle = preg_replace(‘,’,‘<br>’,‘$jtitle’); before the echo statement.

[Lemon_Juice]

$jtitle = str_replace(',', '<br>', $jtitle);

[rvanderh]

Thanks.

I’m not getting any PHP warnings with str_replace, but the output is still comma-delimited just like it is in the MySQL table.

[myesain84]

After you have made your adjustment, you should be echoing out the new variable, rather than the $row value:

for ($i = 0; $i < $num; $i++) {
    $row = mysqli_fetch_assoc($result);
    $jtitle = preg_replace("/\,/", "<br>", $row['title']);
    
    echo "<tr><td width='30%'>$jtitle</td</tr>";
}

We haven’t made any adjustment to the value held in $row['title'], which is why it is still formatted as a comma separated list.

FYI, the sooner you move the values out of the comma separated list into a separate table, the better. One of the core tenets I remember when learning about database design is that if you are trying to store more than one value in a single DB field, your design is probably wrong.

A better option would be to have a separate table that listed the id of the entity you are reporting on and the values you want stored for that entity:

+----------------------------------------+
| entity_value_id | entity_id | value    |
|-----------------|-----------|----------|
| 1               | 1         | Item 2   |
| 2               | 1         | Item 6   |
| 3               | 1         | Item 124 |
+----------------------------------------+

These values could then be requested for your reporting entities. Having your data stored this way can also lead to better performance and allow you to perform some more robust reporting.

[rvanderh]

Thanks for the reply myesain84! This solution works. I agree with you about the database design. Ideally I would report out the data in a way where each $jtitle was listed with the number of “votes” (checks) it had gotten. I wish I had more opportunities to work with MySQL and PHP than I do now.

Thanks to everyone who responded to my questions.