Jump to content

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


Recommended Posts

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>";


Link to comment
Share on other sites

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.

Link to comment
Share on other sites

This thread is more than a year old.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.


  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.