Splitting SQL data by specific character

Question

I have a value in a (single column) mySQL database which reads similar to this.

Entry1,Entry2,Entry3

I need to echo them in an unordered list. I had a look possible how can I query the database. Tried following (referencing an answer here) but keep getting and SQL error which essentially seems like an issue with the query.

 $SQL = "SELECT mytableColumn(SUBSTR(mytableColumn, 0, INSTR(mytableColumn, ',')-1), mytableColumn) AS output
  FROM mytableColumn";

Can you help?

Why don't you just split it in php? `$list = explode(',', $column); foreach ($list as $item) { echo $item, '
'; }`? — Blue, Jan 27 '18 at 16:05
Having comma-separated items in a database column is normally an indicator that you actually need a separate table with a list of those items, one per row. — Matt Gibson, Jan 27 '18 at 16:13
@MattGibson make sense to have multiple locations for store data. Actually I'm gonna explore it now. — Mohan Wijesena, Jan 27 '18 at 16:45
Possible duplicate of [Can Mysql Split a column?](https://stackoverflow.com/questions/1096679/can-mysql-split-a-column) — mickmackusa, Jan 27 '18 at 19:52

score 0 · Answer 1 · answered Jan 27 '18 at 16:06

I would simplify the query to just SELECT columnName FROM tableName and get the result as a string.

You can then use the PHP explode function like:

$resultsArray = explode(',', $stringResult);

and from there:

<ul>
    <?php foreach($resultsArray as $item): ?>
        <li><?= $item ?></li>
    <?php endforeach; ?>
</ul>

Splitting SQL data by specific character

1 Answers1