Professional Web Applications Themes

Sort array from a Query - MySQL

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. #1

    Default 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 Guest

  2. #2

    Default Re: Sort array from a Query

    [email]bettinacoaster.ch[/email] 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.
    [email]jstucklexattglobal.net[/email]
    ==================
    Jerry Stuckle Guest

  3. #3

    Default Re: Sort array from a Query

    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:
    > [email]bettinacoaster.ch[/email] 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.
    > [email]jstucklexattglobal.net[/email]
    > ==================
    bettina@coaster.ch Guest

  4. #4

    Default Re: Sort array from a Query

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

    bettina@coaster.ch Guest

Similar Threads

  1. Sort Array
    By DuLaus in forum Coldfusion - Advanced Techniques
    Replies: 0
    Last Post: March 3rd, 09:54 PM
  2. sort array by key
    By Boefje in forum PHP Development
    Replies: 4
    Last Post: June 10th, 04:52 PM
  3. Sort a 2D array
    By Denis -esp2008- in forum PHP Development
    Replies: 1
    Last Post: June 9th, 05:31 PM
  4. sort w/o using an array
    By John Fisher in forum PERL Beginners
    Replies: 2
    Last Post: October 14th, 12:21 PM
  5. Help me sort a two - d array
    By Pejo in forum ASP
    Replies: 2
    Last Post: September 6th, 12:28 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139