0

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!!!!????

teame
  • 1
  • 6
  • Why are you only fetching one row from each of the result sets? – David Aman May 18 '16 at 02:33
  • Because I didn't know that I was doing that. What makes me only fetch a single row? $row is just a variable name, it shouldn't be specifying that only one row be fetched... – teame May 18 '16 at 02:35

4 Answers4

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  ";
}
?>
David Aman
  • 281
  • 1
  • 8
  • Please excuse me, but how does that fit in with the rest of the code? Do I nest another while for $raw = mysql_fetch_array($rs2);? Would the foreach then be needed? – teame May 18 '16 at 02:47
  • what do you mean by //whatever processing? – teame May 18 '16 at 03:02
  • Yes, you would use a while loop for $raw. I put "whatever processing" thinking that you would know what to do once you realized you had to get all the rows. If you need help with the rest of the code, let us know what you are hung up on. – David Aman May 18 '16 at 03:09
  • I appreciate your help -- I'm not very good at placing foreach and whiles! – teame May 18 '16 at 03:51
  • The current hangup is taking ksimpson's code below but exploding the comma-seperated keywords into array_unique set of single keywords – teame May 18 '16 at 03:53
0

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);
KSimpson
  • 46
  • 3
  • The problem is that $rows will not be comma-separated values, but $raws is comma-seperated keywords, so I have to address stripping out the comma's later by adding them in first so both arrays will match – teame May 18 '16 at 02:56
  • I'm trying to form a comma-separated string because otherwise I have to deal with multi-dimensional arrays when I try to explode the keywords from 'issues' (which was frying my brain by returning "array array array" into the dropdown) – teame May 18 '16 at 02:59
  • Use mysql_fetch_row instead of mysql_fetch_array. mysql_fetch_array returns a hybrid hash/indexed array. Or you could do this $keywordvaluesstring = $rows['category'] .', '. $raws['issues']; – KSimpson May 18 '16 at 03:07
0

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  ";
}
?>
Pang
  • 9,564
  • 146
  • 81
  • 122
KSimpson
  • 46
  • 3
  • I've been trying to implement your suggestions in different ways... but it still isn't working. Does this address that the issue column has keywords that are comma-separated? – teame May 18 '16 at 03:40
  • Oooh! Well now it works (was missing the $rs and $rs2 variables... but it DOESN'T look like it is splitting out the comma-seperated keywords in the 'issues' column... what to do? – teame May 18 '16 at 03:45
  • Where is the right place to do the explode in while ($row = mysql_fetch_row($rs2)) { array_push($allvalues, $row[0]); } – teame May 18 '16 at 03:57
  • I tried placing an explode in the middle of the $row and the array_push but it broke the page... what do you think? – teame May 18 '16 at 03:58
  • Look at the second loop. I added the csv explode inside of it. I think that's what you re looking for. – KSimpson May 19 '16 at 02:58
0

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  ";
}
?>
teame
  • 1
  • 6