Sort array from a Query

[bettina@coaster.ch]

I have 2 tables (simplified):

coasters (ID,BREWERY_COD E, etc)
breweries (BREWERY_CODE, BREWERY)

For each brewery I want to count how many coasters are there in table
"coasters" (up to here it works fine) and then I want to show them
ordered by quantity (that's my problem):

The following is the piece of code.
......
$search_breweri es = mysql_query("SE LECT BREWERY_CODE, BREWERY FROM
breweries");
$i = 0;
while ($row = mysql_fetch_arr ay($search_brew eries, MYSQL_NUM)) {
$breweries[$i][0] = $row[0];
$breweries[$i][1] = $row[1];
$key_brewery = $row[0];
$count_coasters _brewery = mysql_query("SE LECT COUNT(*) FROM coasters
WHERE BREWERY_CODE = $key_brewery");
$breweries[$i][2] = mysql_result($c ount_coasters_b rewery,0,0);
$i = $i + 1;
}

I suppose I have to assign everything to an array and then sort it, but
I don't know how.

Any help will be welcomed.
Bettina

[Ewoud Dronkert]

Re: Sort array from a Query

bettina@coaster .ch wrote:[color=blue]
> coasters (ID,BREWERY_COD E, etc)
> breweries (BREWERY_CODE, BREWERY)
>
> For each brewery I want to count how many coasters are there in table
> "coasters" (up to here it works fine) and then I want to show them
> ordered by quantity (that's my problem):[/color]

Better ask database questions in comp.databases. mysql























































select brewery, count(coasters. id) as cid from breweries inner join
coasters using(brewery_c ode) group by brewery order by cid

--
E. Dronkert

[Sean]

Re: Sort array from a Query

Follow this up in the sql forums, but you want something like this:

SELECT b.BREWERY_CODE, b.BREWERY, COUNT(c.id) as num_coaster
FROM breweries b LEFT JOIN coasters c ON b.BREWERY_CODE =
c.BREWERY_CODE
Group by b.BREWERY_CODE
order by COUNT(c.id) DESC

UNTESTED!

This will get you what you want in one pass. (i think)

[bettina@coaster.ch]

Re: Sort array from a Query

I've tried the following:

$search_breweri es = mysql_query("SE LECT b.BREWERY_CODE, b.BREWERY,
COUNT(c.ID) FROM breweries as b JOIN coasters as c ON
b.BREWERY_CODE = c.BREWERY_CODE Group by b.BREWERY_CODE order by
COUNT(c.ID) DESC ");
$i = 0;
while ($row = mysql_fetch_arr ay($search_brew eries, MYSQL_NUM))
{
$mybreweries[$i][0] = $row[0];
$mybreweries[$i][1] = $row[1];
$mybreweries[$i][2] = $row[2];
$i = $i + 1;
}
......
And I get the following message:
Warning: mysql_fetch_arr ay(): supplied argument is not a valid MySQL
result resource in....


Sean schrieb:
[color=blue]
> Follow this up in the sql forums, but you want something like this:
>
> SELECT b.BREWERY_CODE, b.BREWERY, COUNT(c.id) as num_coaster
> FROM breweries b LEFT JOIN coasters c ON b.BREWERY_CODE =
> c.BREWERY_CODE
> Group by b.BREWERY_CODE
> order by COUNT(c.id) DESC
>
> UNTESTED!
>
> This will get you what you want in one pass. (i think)[/color]

[bettina@coaster.ch]

Re: Sort array from a Query

Thank you. Now it works. I wrote like that:

$search_breweri es = mysql_query("SE LECT b.BREWERY_CODE, b.BREWERY,
COUNT(c.ID) as cant FROM breweries as b JOIN coasters as c ON
b.BREWERY_CODE = c.BREWERY_CODE Group by b.BREWERY_CODE order by cant
DESC ");

Sean schrieb:
[color=blue]
> Follow this up in the sql forums, but you want something like this:
>
> SELECT b.BREWERY_CODE, b.BREWERY, COUNT(c.id) as num_coaster
> FROM breweries b LEFT JOIN coasters c ON b.BREWERY_CODE =
> c.BREWERY_CODE
> Group by b.BREWERY_CODE
> order by COUNT(c.id) DESC
>
> UNTESTED!
>
> This will get you what you want in one pass. (i think)[/color]