MySQL Results as Comma Separated List

MySQL Results as comma separated list

You can use GROUP_CONCAT to perform that, e.g. something like

SELECT p.id, p.name, GROUP_CONCAT(s.name) AS site_list
FROM sites s
INNER JOIN publications p ON(s.id = p.site_id)
GROUP BY p.id, p.name;

Comma separated string of selected values in MySQL

Check this:

SELECT GROUP_CONCAT(id)
FROM table_level
WHERE parent_id = 4
GROUP BY parent_id;

Converting MySQL results into comma separated values

$tags = array();
while ($row = $stmt_tags->fetch(PDO::FETCH_ASSOC)) {
    $tags[] =htmlspecialchars( $row['name'], ENT_NOQUOTES, 'UTF-8' );
}
echo implode(',', $tags);

MySQL column data returned as comma delimited list

You want to use GROUP_CONCAT :

$sql = "SELECT GROUP_CONCAT(friend) FROM table__friends GROUP BY id HAVING id = ".$user_id;

Adjusted for correctness per the better answer.

How do I get a comma separated list of ten values and not all of them?

Use SUBSTRING_INDEX() with 10 as the 3d argument:

SELECT SUBSTRING_INDEX(GROUP_CONCAT(n.id), ',', 10) FROM tbl_names n

MySQL Order By Comma Separated List

You could use order by FIELD()

SELECT * FROM Characters WHERE CharacterID IN (30, 29, 1, 292, 51)
ORDER BY FIELD(CharacterID, 30, 29, 1, 292, 51)

FIELD() is a function that returns the index position of a comma-delimited list

How to run select query on columns having comma separated values

Use the operator LIKE:

SELECT USER 
FROM DETAILS 
WHERE ',' || DEPARTMENT || ',' LIKE '%,' || 'Admin' || ',%'
  AND ',' || DEPARTMENT || ',' LIKE '%,' || 'Finance' || ',%'
  AND ',' || DEPARTMENT || ',' NOT LIKE '%,' || 'Accounts' || ',%';

Or the function INSTR():

SELECT USER 
FROM DETAILS 
WHERE INSTR(',' || DEPARTMENT || ',', ',' || 'Admin' || ',') > 0
  AND INSTR(',' || DEPARTMENT || ',', ',' || 'Finance' || ',') > 0
  AND INSTR(',' || DEPARTMENT || ',', ',' || 'Accounts' || ',') = 0;

This will work if there are no spaces after each comma in the column DEPARTMENT.

See the demo.

MySQL: count comma separated values from field

Join the tables and aggregate:

select t.tag, count(p.id) counter
from tags t inner join posts p
on find_in_set(t.tag, p.tags)
group by t.tag

The function find_in_set() used in the ON clause will work if there are no spaces after the commas in the column tags of the table posts. If there are spaces then replace() must be used to remove them.

See the demo.

Results:

| tag     | counter |
| ------- | ------- |
| City    | 3       |
| College | 1       |
| School  | 1       |
| Work    | 1       |

---

Related Topics