PHP - Using implode, explode, and array_unique to

Question

I have an SQL database with a "category" keyword (only one allowed) and "issues" keywords (multiple comma-separated words). I am trying to make a auto-populating drop-down keyword select menu by selecting all the keywords from the "category" and "issues" columns, turning both returned arrays into comma-separated strings with implode, then combining the strings and exploding the comma-separated strings into an array, while removing duplicate entries with array_unique.

But it's not working. I've tried several approaches. Here is my latest. It is returning SOME values from the column but not all, and I can't figure out why. Perhaps array_unique isn't working the way I want it to work, or I am messing up the conversion to strings and back into an array? Is there a simpler way to do this? I have searched all over and can't find a good example anywhere.

Here is the code I have working now...

<?
$dropdownsql = "SELECT DISTINCT category FROM database";
$keywords = mysql_query($dropdownsql);
while($row = mysql_fetch_array($keywords))
{ 
  echo "<option value=\"".$row['category']."\">".$row['category']."</option>\n  ";
}
?>

While this works for the one-word category keywords, it obviously can't handle multiple SQL columns or comma-separated keywords within those columns. Here's my attempt to do that in the most straightforward way:

<?
$dropdownsql = "SELECT DISTINCT category FROM database";
$dropdownsql2 = "SELECT DISTINCT issues FROM database";

//run sql queries separately.  Ideally they would be combined into one right?
$rs = mysql_query($dropdownsql);
$rs2 = mysql_query($dropdownsql2);
$row = mysql_fetch_array($rs);
$raw = mysql_fetch_array($rs2);

//then implode the resulting arrays, placing commas & spaces so they'll match
$rows = implode(", ", $row);
$raws = implode(", ", $raw);

//try to concatenate the strings of comma-separated keywords
$keywordvaluesstring = $rows.$raws;

//then explode the concatenated string back into array
$keywordvalue = explode(", ",$keywordvaluesstring);

//then keep only one copy of duplicated keywords
$values = array_unique($keywordvalue, SORT_REGULAR);

//and finally echo the keywords into a dropdown 
foreach($values as $value){ 
  echo "<option value=\"".$value."\">".$value."</option>\n  ";
}
?>

WHAT AM I DOING WRONG!!!!????

Answer 1

<?
$dropdownsql = "SELECT DISTINCT category FROM database";
$dropdownsql2 = "SELECT DISTINCT issues FROM database";

//run sql queries separately.  Ideally they would be combined into one right?
$rs = mysql_query($dropdownsql);
$rs2 = mysql_query($dropdownsql2);
$keywords = array();
while ($row = mysql_fetch_array($rs)) {
    $keywords[] = $row[0];
}
while($raw = mysql_fetch_array($rs2)) {
    $keywords = array_merge($keywords, explode(', ', $raw[0]));
}

$values = array_unique($keywords, SORT_STRING);

//and finally echo the keywords into a dropdown 
foreach($values as $value){ 
  echo "<option value=\"".$value."\">".$value."</option>\n  ";
}
?>

Answer 2

I believe this is what you want.

<?
$dropdownsql = "SELECT DISTINCT category FROM database";
$dropdownsql2 = "SELECT DISTINCT issues FROM database";

$allvalues = array();
while ($row = mysql_fetch_row($rs)) {
    array_push($allvalues, $row[0]);
}

while ($row = mysql_fetch_row($rs2)) {
    $keywords = explode(',', $row[0]);
    foreach($keywords as $word) {
        array_push($allvalues, $word);
    }
}

//then keep only one copy of duplicated keywords
$values = array_unique($allvalues, SORT_REGULAR);

//and finally echo the keywords into a dropdown 
foreach($values as $value){ 
    echo "<option value=\"".$value."\">".$value."</option>\n  ";
}
?>

Answer 3

Try replacing this

//then implode the resulting arrays, placing commas & spaces so they'll match
$rows = implode(", ", $row);
$raws = implode(", ", $raw);

//try to concatenate the strings of comma-separated keywords
$keywordvaluesstring = $rows.$raws;

With This

$keywordvalue = array_merge($rows, $raws);

Answer 4

FOR OTHERS WHO FACE THIS SAME PROBLEM, HERE IS THE FINAL WORKING CODE: IN THE DATABASE, 'CATEGORY' IS A SINGLE KEYWORD, AND 'ISSUE' AND 'RELATEDISSUES' ARE COMMA-SEPERATED KEYWORDS. Thanks to Ksimpson

<form method="GET" action="#">
<select name="keywords"> 
<OPTION selected><? echo $keyword1; ?></OPTION> 
<?

//define the query (the database connection is accomplished elsewhere btw)
$dropdownsql2 = "SELECT DISTINCT category FROM database";
$dropdownsql = "SELECT DISTINCT issue, relatedissues FROM database";

//runthequery
$rs = mysql_query($dropdownsql);
$rs2 = mysql_query($dropdownsql2);

//create an array to hold the keywords
$allvalues = array();    


//take the values from category and append to the array
while ($row = mysql_fetch_row($rs2)) {
    array_push($allvalues, $row[0]);
}


//loop again -- explode creates an array of arrays so we handle in the loop
while ($row = mysql_fetch_array($rs)) {

//for each comma separated string, explode it and append the results
    foreach($row as $str) {
    $exploded = explode(', ', $str);
    array_push($allvalues, $exploded[0]);      
       }
}

//then keep only one copy of duplicated keywords
$values = array_unique($allvalues, SORT_REGULAR);

//and finally echo the keywords into a dropdown 
foreach($values as $value){ 
  echo "<option value=\"".$value."\">".$value."</option>\n  ";
}
?>