DISTINCT Comma Separated SQL Table Rows

Question

I have a b_topics table with tags column in multiple rows

id | tags 
1  | Joshua, Janet, Hannah
2  | Glory, Jon,  Celina, Johanna
3  | Bridge,Terry, Sterling
4  | Daniel, Florence, Joanne

I want to check for the related tags with the input Jo so i have the below sql select

$query = Jo; 
$sql = mysql_query("SELECT DISTINCT tags FROM b_topics WHERE tags LIKE '%{$query}%'"); 

while ($row = mysql_fetch_array($sql)) { 
$array[] = array ( 'label' => $row['tags'], 'value' => $row['tags'], ); 
} 
echo json_encode ($array);

This is the output:

[{"label":"Joshua, Janet, Hannah","value":"Joshua, Janet, Hannah"},{"label":"Glory, Jon, Glory","value":"Glory, Jon, Glory"},{"label":"Daniel, Florence, Joanne","value":"Daniel, Florence, Joanne"}]

I want the matched words to be on foreach();

Expected output: [{"label":"Joshua","value":"Joshua"},{"label":"Jon","value":"Jon"},{"label":"Johanna","value":"Johanna"},{"label":"Joanne","value":"Joanne"}]

No, no, no, no! Never store multiple values in a single column! — juergen d, Jun 01 '17 at 11:43
[I'll ask this again](https://stackoverflow.com/questions/44306930/select-distinct-comma-separated-sql-table-rows#comment75618897_44306930); is this `$query = Jo;` your actual syntax used? You need to respond to comments and the answer given. — Funk Forty Niner, Jun 01 '17 at 12:04

Gordon Linoff · Answer 1 · 2017-06-01T12:30:35.087

1

Fix your data structure! You should not be storing multiple values in a string delimited list. This is simply not the right way to store the data. SQL has this great data structure for storing lists. It is not called "string". It is called "table". You want a table called TopicTags with one row per topic and per tag.

Although you can do nasty string functions to get what you want, this would be much simpler with the right data structure:

select topic_id, tag
from TopicTags tt
where tag like '%Jo%';

You can aggregate if you want the results in a particular format.

edited Jun 01 '17 at 12:30

answered Jun 01 '17 at 11:44

Gordon Linoff

1,242,037
58
646
786

He doesn't want `find_in_set`, he wants partial matches. So `Jo` should match `Joshua` and `Jon`. – Barmar Jun 01 '17 at 12:00
They reposted https://stackoverflow.com/q/44306930/ and I asked them if `$query = Jo;` was their actual syntax which they used here. If that is the case, then that needs to be wrapped in quotes. Otherwise, it would throw an undefined constant error. – Funk Forty Niner Jun 01 '17 at 12:01

score 0 · Answer 2 · answered Jun 01 '17 at 12:05

set your tables because when your query return a row then you can store that row not only a single tag. $array[] = array ( 'label' => $row['tags'], 'value' => $row['tags'], );

where $row['tags']=Joshua, Janet, Hannah so its gives result like 'label' ="Joshua, Janet, Hannah"

score 0 · Answer 3 · answered Jun 01 '17 at 13:08

$sql = "select * from test where tags like '%Jo%'";

$res = mysqli_query($con,$sql);

$string = '';

while($row=mysqli_fetch_array($res)){

$string .= ','.$row['tags']; }

$array = explode(',', $string);

$search = preg_quote('Jo', '~'); // don't forget to quote input string!

$result = preg_grep('~' . $search . '~', $array);

print_r($result);

DISTINCT Comma Separated SQL Table Rows

3 Answers3