Sort array from a Query

I have 2 tables (simplified):

coasters (COASTER_CODE, 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 b.BREWERY_CODE, b.BREWERY,
COUNT(c.COASTER_CODE) FROM breweries as b JOIN coasters as c ON
b.BREWERY_CODE = c.BREWERY_CODE Group by b.BREWERY_CODE order by
COUNT(c.COASTER_CODE) 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;
}
......
I get the following message:
Warning: mysql_fetch_array(): supplied argument is not a valid MySQL
result resource in....

Any help will be welcomed!

bettina@coaster.ch wrote:
> I have 2 tables (simplified):
>
> coasters (COASTER_CODE, 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 b.BREWERY_CODE, b.BREWERY,
> COUNT(c.COASTER_CODE) FROM breweries as b JOIN coasters as c ON
> b.BREWERY_CODE = c.BREWERY_CODE Group by b.BREWERY_CODE order by
> COUNT(c.COASTER_CODE) 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;
> }
> .....
> I get the following message:
> Warning: mysql_fetch_array(): supplied argument is not a valid MySQL
> result resource in....
>
> Any help will be welcomed!
>

Your query failed. Always check the return value from mysql_query:

$search_breweries = mysql_query("SELECT b.BREWERY_CODE, b.BREWERY,
COUNT(c.COASTER_CODE) FROM breweries as b JOIN coasters as c ON
b.BREWERY_CODE = c.BREWERY_CODE Group by b.BREWERY_CODE order by
COUNT(c.COASTER_CODE) DESC ");
if ($search_breweries) {
$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;
}
else
echo mysql_error();




--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex@attglobal.net
==================

Thank you for your answer. I get the message: Invalid use of group
function
Hat something to do with the "count" function?

Jerry Stuckle schrieb:

> bettina@coaster.ch wrote:
> > I have 2 tables (simplified):
> >
> > coasters (COASTER_CODE, 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 b.BREWERY_CODE, b.BREWERY,
> > COUNT(c.COASTER_CODE) FROM breweries as b JOIN coasters as c ON
> > b.BREWERY_CODE = c.BREWERY_CODE Group by b.BREWERY_CODE order by
> > COUNT(c.COASTER_CODE) 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;
> > }
> > .....
> > I get the following message:
> > Warning: mysql_fetch_array(): supplied argument is not a valid MySQL
> > result resource in....
> >
> > Any help will be welcomed!
> >
>
> Your query failed. Always check the return value from mysql_query:
>
> $search_breweries = mysql_query("SELECT b.BREWERY_CODE, b.BREWERY,
> COUNT(c.COASTER_CODE) FROM breweries as b JOIN coasters as c ON
> b.BREWERY_CODE = c.BREWERY_CODE Group by b.BREWERY_CODE order by
> COUNT(c.COASTER_CODE) DESC ");
> if ($search_breweries) {
> $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;
> }
> else
> echo mysql_error();
>
>
>
>
> --
> ==================
> Remove the "x" from my email address
> Jerry Stuckle
> JDS Computer Training Corp.
> jstucklex@attglobal.net
> ==================

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