Grouping Arrays

[karenn1]

Hey everyone,

 

I have a database for projects and each entry has one or more project type category that it falls into. The values are separated with a semi-colon to make the distinction. I've used the following code to output the data onto the page:

 

$sql = "SELECT project_type FROM projects ORDER BY project_type";
$result = mysql_query($sql);


while ($rs = mysql_fetch_array($result)) {
$database_value = $rs['project_type'];
$array = explode(';', $database_value);

foreach($array as $key => $value){
	echo " ".$value."<br />";  
}


}

 

This outputs the following:

 

Agriculture
Agriculture
Agriculture
Agriculture
Agriculture
Agriculture
Agriculture
Agriculture
Export Development
Agriculture
Impact Assessments
Impact Studies
Agriculture
M & E
Agriculture
Private Sector Development
Corporate and Public Governance
Corporate and Public Governance
Corporate and Public Governance

 

In PHP, how can I group these values together, similar to SQL's GROUP BY method? I've tried grouping it in the query but some of the data goes lost because it must be exploded first.

 

Can anybody help?

 

 

Thanks in advance!

 

Karen

[Muddy_Funster]

by group together do you meen display each one only once?

[ManiacDan]

As much as I hate sounding like a jerk for saying so: if your database was designed properly you wouldn't have run into this problem.  You should have made this into a cross-reference table so you can do your many-to-many relationships without resorting to storing a delimited string (which as you've discovered is difficult to maintain and display).

 

To solve your immediate problem: put all these items into a temporary array then call array_count_values

 

-Dan

[karenn1]

Thanks for the reply. @dan - this is not my database. I inherited it from someone else so I need to make it work in its current state. There is no time or budget to rebuild.

 

@muddy_funster - correct. I would like each one to only display once. Any ideas?

 

 

Regards,

Karen

[Muddy_Funster]

well if it's just a display problem and not an actual grouping issue, conditionalise the display

$filter = '';
foreach($array as $key => $value){
if ($filter != $value){		
  echo " ".$value."<br />";  
  $filter = $value;
}
}

[karenn1]

Thanks, I tried your code but I'm still getting this:

 

Agriculture
Agriculture
Agriculture
Agriculture
Agriculture
Agriculture
Agriculture
Agriculture
Export Development
Agriculture
Impact Assessments
Impact Studies
Agriculture
M & E
Agriculture
Private Sector Development
Corporate and Public Governance
Corporate and Public Governance
Corporate and Public Governance

 

Am I doing something wrong?

[Psycho]

ManiacDan already gave you a solution. Although I would think array_unique() would be a better fit for this scenario

 

Not tested:

$sql = "SELECT project_type FROM projects ORDER BY project_type";
$result = mysql_query($sql);

//Dump results into array
$project_types = array();
while ($rs = mysql_fetch_array($result))
{
    $project_types = array_merge($project_types, explode(';', $rs['project_type']));
}

//Filter out duplicates
$project_types = array_unique($project_types);

//Display results
foreach($project_types as $type)
{
    echo " {$type}<br />\n";  
}

[ManiacDan]

If you want only unique items, array_unique() is the solution.

 

If you want the items grouped by count, array_count_values() is the solution.

 

Since you said you wanted it similar to GROUP BY (which implies a count) rather than UNIQUE (which implies uniqueness), I gave you the GROUP BY solution.

 

-Dan

[karenn1]

@mjdamato - that's exactly what I was looking for! Works great!

 

Thanks to all for your input!

 

 

Karen