(two) queries and jquery ui accordion

[Tanja]

I have a dog database with two tables: dog and owner. In owner there are also all breeders with kennelname.

 

I want to select all breeders from each country (here country_short) and count them.

I do with

mysql_query("SELECT country_short, COUNT(*) AS zahl FROM owner WHERE kennelname !='' GROUP BY country_short ");

with this i will have all countrys and all breeders (also the old inaktive breeders)

 

Next step: show me all breeder with litters in last 9 years (to call them active). Therefore i join the dog-table and read the youngest birthday from dog /breeder.

mysql_query("
SELECT dog.breeder_id, dog.date_of_birth, owner.kennelname, owner.country, owner.country_short,
max(dog.date_of_birth) as datmax,
MAX(YEAR(dog.date_of_birth)) AS new
FROM dog
INNER JOIN owner ON dog.breeder_id = owner.id
WHERE country_short='de' AND dog.date_of_birth >= DATE_SUB(CURDATE(), INTERVAL 9 YEAR)
GROUP BY kennelname
ORDER BY datmax DESC
");

 

My Problem:

i want to show these data in a jquery ui accordion - for each country (in headline) all breeders (in text).

How can I do?

[Tanja]

done by myself:

function country()
{
$activebreeder = "
SELECT dog.breeder_id, dog.date_of_birth, owner.kennelname, owner.country, owner.country_short,
max(dog.date_of_birth) as datmax,
MAX(YEAR(dog.date_of_birth)) AS new

FROM dog
INNER JOIN owner ON dog.breeder_id = owner.id

WHERE dog.date_of_birth >= DATE_SUB(CURDATE(), INTERVAL 9 YEAR)

GROUP BY owner.country_short, kennelname

ORDER BY country_short, datmax DESC
";
$result = mysql_query($activebreeder);

$grouped = array();
while ($row = mysql_fetch_object($result)) {
	 $grouped[$row->country_short][] = $row;
}
return $grouped;
}

$country = country();
print("<pre>");
	 print_r($country);
	 print("</pre>");
foreach ($country as $country_short => $entries)
{
echo '<h2>' . $country_short . '</h2>';
foreach ($entries as $entry) {
echo '<p>' . $entry->kennelname. '</p>';	    
echo '<p>' . $entry->breeder_id. '</p>';
echo '<p>' . $entry->new. '</p>';
}
}

 

Now is must only put into accordion