Need help with a query

[sks1024]

Got a column (department) in database with text values separated with comma

Row1

id
1

department
HR,Finance,Utilities

Row2

id
2

department
HR,Finance

I need a query that will display these only once if they are present.

I am getting these but they are being repeated. In my example Utilities shows up once, but HR and Finance show up twice in my list.

What I have now.

SELECT DISTINCT department FROM tbl_video ORDER BY department DESC;

while ($department_query_row = mysql_fetch_array($department_query_result)) {
	$dept_list_output = explode(",", $department_query_row['department']);

 foreach ($dept_list_output as $item) {

      <li><a href="search_dept.php?department=<?php print "$item"; ?>"><?php print "$item"; ?></a></li>

 }
}

Thanks

[bradgrafelman]

sks1024;11039743 wrote:

Got a column (department) in database with text values separated with comma

There's your first problem; you've got a database schema that hasn't been normalized to remove multi-valued columns.

"HR,Finance,Utilities" is a different value than "HR,Finance", thus the DISTINCT isn't going to do anything. Instead, you'll have to manually filter out the duplicates in your PHP code... or modify your schema such that you no longer have multi-valued columns.

[sks1024]

Any assistance would be helpful. How would I filter out any duplicates with code as a short term fix. I will normalize the DB after some reading on same.

[NogDog]

$departments = array();
while ($department_query_row = mysql_fetch_array($department_query_result)) {
	$dept_list_output = explode(",", $department_query_row['department']);
	foreach ($dept_list_output as $item) {
		$departments[$item] = 1;
	}
}
krsort($departments);
$departments = array_keys($departments);
// debug:
die("<pre>".var_export($departments, 1)."</pre>");

[sks1024]

Where would I insert the html to print out the <li> for the lists? They code works fine as tested with the debug code.

[NogDog]

sks1024;11039753 wrote:

Where would I insert the html to print out the <li> for the lists? They code works fine as tested with the debug code.

Just replace the die() with a foreach() loop on $departments that outputs the array value within the desired HTML, and you should be good to go.

[sks1024]

That did the trick. Thanks

[NogDog]

Or you could do one of my favorite little tricks:

echo "<ul>\n<li>".implode("</li>\n<li>", $departments)."</li>\n</ul>";

🙂