PHP Programming - Sort array from a Query

Author

Sort array from a Query

bettina@coaster.ch

2005-11-29, 6:57 pm

I have 2 tables (simplified):

coasters (ID,BREWERY_CODE, 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_breweries = mysql_query("SELECT BREWERY_CODE, BREWERY FROM
breweries");
$i = 0;
while ($row = mysql_fetch_array($search_breweries, MYSQL_NUM)) {
$breweries[$i][0] = $row[0];
$breweries[$i][1] = $row[1];
$key_brewery = $row[0];
$count_coasters_brewery = mysql_query("SELECT COUNT(*) FROM coasters
WHERE BREWERY_CODE = $key_brewery");
$breweries[$i][2] = mysql_result($count_coasters_brewery,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

2005-11-29, 6:57 pm

bettina@coaster.ch wrote:
> coasters (ID,BREWERY_CODE, 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):

Better ask database questions in comp.databases.mysql

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

--
E. Dronkert

Sean

2005-11-29, 6:57 pm

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

2005-11-29, 6:57 pm

I've tried the following:

$search_breweries = mysql_query("SELECT 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_array($search_breweries, 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_array(): supplied argument is not a valid MySQL
result resource in....

Sean schrieb:

> 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

2005-11-29, 6:57 pm

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

$search_breweries = mysql_query("SELECT 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:

> 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)

Sponsored Links