Group results by category

[Anon]

Hi!

I am a bit stumped with this problem, which I am sure is pretty easy to solve but when one doesn't know how, it seems impossible ... anyway..

I want to display results from my database like this:

COUNTRY1
link1
link2

COUNTRY2
link1
link2

etc...

My code is that below at the moment. This however displays the results like this:

COUNTRY1
link1
COUNTRY1
link2
COUNTRY2
link1
COUNTRY2
link2

<pre>
<?php

// Request the details of all the countries
$result = mysql_query(
"SELECT name, link, country " .
"FROM links, countries WHERE CID=countries.ID ORDER BY CID");

if (!$result) {

echo("<P>Error performing query: " .

mysql_error() . "</P>");

exit();

}
// Display the details of each country

while ($row = mysql_fetch_array($result))
{ echo("<p><b>" . $row["country"] . "</b>");

		echo("<br><a href='" . $row["link"] . "' target='_blank'> " . $row["name"] . "</a>");  		  
		}
		?>

</pre>

So how do I get it to display the category, COUNTRY, then the links associated with that country before moving on and doing the same for the rest of the countries in the database.
I've been trying to implement a relational database design as i am sure the solution is in there somewhere....

Any help appreciated.

s

[terje-s]

To be honest, I didn't read your code 🙂

However, the way I usually do this is quite simple, and I think its called a "break".

$res = mysql_query("select something from table order by country");

$currentCountry = "321dummy123";
while ($oneRes = mysql_fetch_array($res)) {
if ($currentCountry != $oneRes[country]) {
// this is the first hit with a new country
// print the name of the country and store it.
$currentCountry = $oneRes[country];
print $currentCountry;
}
print "data from oneRes..";
}

Hope that gave you some ideas 🙂

[Anon]

Thanks Terje!

Exactly what I wanted to do - workss liek a dream.

Thanks again and have a good Sunday!

s

[sir_tom_price]

There is another way, which takes a bit more setting up when you're grabbing the data, but makes the output to HTML neater.

You can create a hash (associative array) where the key is the field that you're grouping by:-

---------------------------- code ------------------------------------
$result = mysql_query("select boss,country from table order by country");

$data=array();
while ($row = mysql_fetch_array($result)){

// get the group for this row
$group = $row["country"];

// create a key for it if we haven't seen it before
if (!$data[$group]) $data[$group]=array();

// put the data into the array under the key for this group
array_push($data[$group], $row);

}

So you've now got a data structure which looks like (pseudocode):-

$data["uk"] = array("tony", "david", "estelle")
$data["usa"] = array("george", "donald", "colin")
$data["france"] = array("chirac?", "le pen?")

... which you can now output like this:-

---------------------------- code ------------------------------------
foreach($data as $group) {

// heading
printf("<h4>%s</h4>", $group[0]["country"]);

// contents of the group
foreach ($group as $row) {
	printf("%s<br>", $row["boss"]);
}

}

Hope this helps

Tom