Separating MySQL concatenated rows, while maintaining relationships to $row.

[ryandward]

This is a LINK to the page -> http://shoutkey.com/carriage

 

I have a problem of separating MySQL concatenated info, yet still maintaining their relationship to other arrays in the same order. First off here is the MySQL code:

 

$query ="SELECT report,";
$query.="GROUP_CONCAT(DISTINCT docID) AS docIDs, ";
$query.="GROUP_CONCAT(DISTINCT analyst) AS analysts, ";
$query.="GROUP_CONCAT(DISTINCT region) AS regions, ";
$query.="GROUP_CONCAT(DISTINCT country) AS countries, ";
$query.="GROUP_CONCAT(DISTINCT topic) AS topics, ";
$query.="GROUP_CONCAT(DISTINCT date) AS dates, ";
$query.="GROUP_CONCAT(DISTINCT event) AS events, ";
$query.="GROUP_CONCAT(DISTINCT link) AS links, ";
$query.="GROUP_CONCAT(DISTINCT province) AS provinces ";
$query.="FROM reports GROUP BY report ORDER BY dates DESC, docIDs DESC";

 

The most important thing is to get all attributes about each report. The thing is, each report can have as many records as it must, in order to store various types of information, like, if multiple analysts are working on the report, then each one saves their own record, but when retrieving these records MYSQL concatenates the records, to make it look to the analysts like this

 

Report 1 --- Josh, Rebecca --- Philippines

 

However, my problem is trying to display the reports by country... as you can see http://shoutkey.com/carriage. I have no idea about how to split up the countries, while maintaining their relationship to the $rows pulled from MySQL. I am gonna post the entire code, and let me know if anybody has any ideas.

 

$result = mysql_query($query);

$numrows = mysql_affected_rows();


$current = false;                  
while($row=mysql_fetch_assoc($result)) {
$dates 		= $row['dates'];
$analysts 	= $row['analysts'];
$provinces 	= $row['provinces'];
$topics 		= $row['topics'];
$regions 	= $row['regions'];
$countries 	= $row['countries'];
$reports		= $row['report'];
$links 		= $row['links'];
$events     = $row['events'];

if (!$current){ //first call
	echo 	"<h3><a href=\"#\">{$countries}</a></h3>"; //echo first call
		$current = $countries;
	echo "<div>" ;//open content div
}
elseif($current != $countries){ // country has changed
	echo "</div>"; //close previous content div
	echo "<h3><a href=\"#\">{$countries}</a></h3>"; //echo subsequent countries
   		$current = $countries;
   	echo "<div><p>" ;//open content div
}	


echo $analysts." ----- ".$provinces." ----- ".$reports;
echo "<br /><br />";	

} 
echo "</div>";
?>
    </div> 

 

[PFMaBiSmAd]

Because you are using DISTINCT, you cannot guarantee any relation between any particular piece of data. The only way what you are currently attempting to do would maintain any relationship would be if all the grouped data was the same length so that as you were looping through one string of values you could get the corresponding value from the same position in the other groups.

 

However, you are doing this the hardest way possible (you would need to add the country column to the GROUP BY term to get data per country.) You should simply query for the rows you want, in the order that you want them (no GROUP BY and GROUP_CONCAT involved) and then output the information the way you want in your php code.