Results 1 to 16 of 16
  1. #1
    Join Date
    Jan 2004
    Location
    Mobile, AL
    Posts
    138

    Array Order + Other Problem

    Ok, I'm printing out results (states) from a database, and counting all the records associated with each one.

    PHP Code:
    $statearr = array(AL=>'Alabama',.......LA=>'Louisiana',.......NC=>'North Carolina',.......WY=>'Wyoming');

    $states mysql_query("select distinct(BisState), count(BisName) as cnt from golf_courses group by BisState asc");
    while(
    $r=mysql_fetch_array($states)){

    //print out all 50 states + count


    It works, sort of. First off, Alaska is missing. Not sure why that is. Records exist for it. It seems weird 49 of 50 print but one doesn't. I've triple-checked all the data, and it's right.

    Secondly, although the list is in first letter order, it seems to not go alphabetical with multiple states beginning with the same letter (Iowa before Idaho and so forth). Any fix for that? I've tried various sorts, none work.

    travel.skaboodle.com/golf/
    Shawn Kerr

  2. #2
    Join Date
    Mar 2005
    Location
    Hattiesburg, MS
    Posts
    160
    Can you give a little more code? Hard to tell where the error might be without seeing more.
    InsanelyMacintosh - Macintosh Software Repository Listings

  3. #3
    Join Date
    Jan 2004
    Location
    Mobile, AL
    Posts
    138
    That's it, other than the simple little HTML table I'm putting the data into.

    That's all the PHP, other than the echoing of the values.

    I mean, here's the full array, if it matters (who knows).

    PHP Code:
    $statearr = array(
    AL=>'Alabama',
    AK=>'Alaska',
    AZ=>'Arizona',
    AR=>'Arkansas',
    CA=>'California',
    CO=>'Colorado',
    CT=>'Connecticut',
    DE=>'Delaware',
    FL=>'Florida',
    GA=>'Georgia',
    HI=>'Hawaii',
    ID=>'Idaho',
    IL=>'Illinois',
    IN=>'Indiana',
    IA=>'Iowa',
    KS=>'Kansas',
    KY=>'Kentucky',
    LA=>'Louisiana',
    ME=>'Maine',
    MD=>'Maryland',
    MA=>'Massachusetts',
    MI=>'Michigan',
    MN=>'Minnesota',
    MS=>'Mississippi',
    MO=>'Missouri',
    MT=>'Montana',
    NE=>'Nebraska',
    NV=>'Nevada',
    NH=>'New Hampshire',
    NJ=>'New Jersey',
    NM=>'New Mexico',
    NY=>'New York',
    NC=>'North Carolina',
    ND=>'North Dakota',
    OH=>'Ohio',
    OK=>'Oklahoma',
    ORE=>'Oregon',
    PA=>'Pennsylvania',
    RI=>'Rhode Island',
    SC=>'South Carolina',
    SD=>'South Dakota',
    TN=>'Tennessee',
    TX=>'Texas',
    UT=>'Utah',
    VT=>'Vermont',
    VA=>'Virginia',
    WA=>'Washington',
    WV=>'West Virginia',
    WI=>'Wisconsin',
    WY=>'Wyoming'); 
    Shawn Kerr

  4. #4
    Join Date
    Mar 2006
    Posts
    984
    Let's try this:

    PHP Code:
    $statearr = array( 
    AL=>'Alabama'
    AK=>'Alaska'
    AZ=>'Arizona'
    AR=>'Arkansas'
    CA=>'California'
    CO=>'Colorado'
    CT=>'Connecticut'
    DE=>'Delaware'
    FL=>'Florida'
    GA=>'Georgia'
    HI=>'Hawaii'
    ID=>'Idaho'
    IL=>'Illinois'
    IN=>'Indiana'
    IA=>'Iowa'
    KS=>'Kansas'
    KY=>'Kentucky'
    LA=>'Louisiana'
    ME=>'Maine'
    MD=>'Maryland'
    MA=>'Massachusetts'
    MI=>'Michigan'
    MN=>'Minnesota'
    MS=>'Mississippi'
    MO=>'Missouri'
    MT=>'Montana'
    NE=>'Nebraska'
    NV=>'Nevada'
    NH=>'New Hampshire'
    NJ=>'New Jersey'
    NM=>'New Mexico'
    NY=>'New York'
    NC=>'North Carolina'
    ND=>'North Dakota'
    OH=>'Ohio'
    OK=>'Oklahoma'
    ORE=>'Oregon'
    PA=>'Pennsylvania'
    RI=>'Rhode Island'
    SC=>'South Carolina'
    SD=>'South Dakota'
    TN=>'Tennessee'
    TX=>'Texas'
    UT=>'Utah'
    VT=>'Vermont'
    VA=>'Virginia'
    WA=>'Washington'
    WV=>'West Virginia'
    WI=>'Wisconsin'
    WY=>'Wyoming');

    $exploded_statearr = (is_array($statearr)) ? explode(", "$statearr) : "";

    if (!empty(
    $statearr)) {
      
      foreach (
    $exploded_statearr as $key => $val) {
      if (isset(
    $key) && isset($val)) {
      echo 
    "State: ".$val."<br />";
      }
     }


    Last edited by horizon; 11-22-2006 at 09:42 PM.

  5. #5
    horizon, your code doesn't work..as usual. Please refrain from posting unusable and blown up code, I told you already to learn something first and then help others.

    mobilebadboy, can you post database data? It seems more like a mysql weird behavior rather than anything else..I believe you're not trying to print out that array contents since it seems you know how to do that. distinct() causes problems sometimes. Which mysql version are you using?
    Dyslexics Have More Fnu

  6. #6
    Join Date
    Mar 2006
    Posts
    984
    I told you already to learn something first and then help others.
    And I already told you that I do not respond pretty well to critism.

  7. #7
    Join Date
    Dec 2003
    Location
    Vancouver BC, eh?
    Posts
    571
    Quote Originally Posted by horizon
    And I already told you that I do not respond pretty well to critism.
    If I may chime in here for a second. I've been reading several threads in here lately and I will agree that most of what you post, example-wise, is rubbish. And I am not trying to be a dick, its just it doesn't work.

    Looking at your example you posted. explode(); is for putting strings INTO arrays. Not making an array into an array with some delimiter. Capiche?

    So it appears you didn't try the code you showed as an example. Which if I were to give any advice I would say this, "test your code" ... we're all learning. Some just have been learning longer and I would take their advice with some humbleness.

    What you showed as an example could have just been:

    PHP Code:
    foreach ($statearr as $key => $val
    {  
      echo 
    "State: ".$val."<br />";


    But that doesn't really address his problem, does it?

    So Mobilebadboy, you need to post all your code since its missing something where the error is happening.

  8. #8
    Join Date
    Jan 2004
    Location
    Mobile, AL
    Posts
    138
    I've narrowed it down to the table column code. When I isolate that and print everything line by line it works fine.

    Trying to come up with a DB sample, having a hard time pulling out one row for each state, all at one time.

    PHP Code:
    <? 
    $statearr 
    = array( 
    AL=>'Alabama',
    AK=>'Alaska'
    AZ=>'Arizona'
    AR=>'Arkansas'
    CA=>'California'
    CO=>'Colorado'
    CT=>'Connecticut'
    DE=>'Delaware'
    FL=>'Florida'
    GA=>'Georgia'
    HI=>'Hawaii'
    ID=>'Idaho'
    IL=>'Illinois'
    IN=>'Indiana'
    IA=>'Iowa'
    KS=>'Kansas'
    KY=>'Kentucky'
    LA=>'Louisiana'
    ME=>'Maine'
    MD=>'Maryland'
    MA=>'Massachusetts'
    MI=>'Michigan'
    MN=>'Minnesota'
    MS=>'Mississippi'
    MO=>'Missouri'
    MT=>'Montana'
    NE=>'Nebraska'
    NV=>'Nevada'
    NH=>'New Hampshire'
    NJ=>'New Jersey'
    NM=>'New Mexico'
    NY=>'New York'
    NC=>'North Carolina'
    ND=>'North Dakota'
    OH=>'Ohio'
    OK=>'Oklahoma'
    ORE=>'Oregon'
    PA=>'Pennsylvania'
    RI=>'Rhode Island'
    SC=>'South Carolina'
    SD=>'South Dakota'
    TN=>'Tennessee'
    TX=>'Texas'
    UT=>'Utah'
    VT=>'Vermont'
    VA=>'Virginia'
    WA=>'Washington'
    WV=>'West Virginia'
    WI=>'Wisconsin'
    WY=>'Wyoming');

    $states mysql_query("select distinct(BisState), count(BisName) as cnt from golf_courses group by BisState asc");
    while(
    $r=mysql_fetch_array($states)){
    ?>

    <table width="100%" cellpadding="3" cellspacing="0" border="0">
    <? 
    // isolate
    $numcols 5;  
    $numcolsprinted 0;  // no of columns so far
    while($r=mysql_fetch_array($states)){ 
    if (
    $numcolsprinted == $numcols) {
    print 
    "</tr>\n<tr>\n";
    $numcolsprinted 0;
    }
    // isolate
    ?>
    <td class="basictext" align="left">
    <? if($r['BisState'] == "OR") { $r['BisState'] = "ORE"; } ?>
    <a href="#"><?=$statearr[$r["BisState"]];?></a> <span class="golfsmall">(<? echo $r["cnt"] ;?>)</span>
    </td>
    <?
    // isolate
    $numcolsprinted++;
    }
    $colstobalance $numcols $numcolsprinted;
    for (
    $i=1$i<=$colstobalance$i++) {

    print 
    "</tr>\n";
    // isolate
    ?> 
    </table>
    <? ?>
    Shawn Kerr

  9. #9
    Join Date
    Feb 2005
    Location
    Australia
    Posts
    5,849
    Quote Originally Posted by mobilebadboy
    It works, sort of. First off, Alaska is missing. Not sure why that is. Records exist for it. It seems weird 49 of 50 print but one doesn't. I've triple-checked all the data, and it's right.

    Secondly, although the list is in first letter order, it seems to not go alphabetical with multiple states beginning with the same letter (Iowa before Idaho and so forth). Any fix for that? I've tried various sorts, none work.
    Iowa before Idaho because IA < ID

    Alaska? select * from golf_courses where BisState='AK' limit 1;
    Chris

    "Some problems are so complex that you have to be highly intelligent and well informed just to be undecided about them." - Laurence J. Peter

  10. #10
    Join Date
    Aug 2002
    Location
    Canada
    Posts
    667
    The problem is that your DISTINCT doesn't guarantee any proper ordering since the GROUP is done after the rows are fetched (and GROUP doesn't guarantee ordering outside of DISTINCT). I could add for the sake of keyboard longevity, that writing ORDER BY xxxx ASC is redundant - ORDER BY will do

    This writ, try altering your query a bit - it'll return 0-entry states as well (is this wanted?). I'm assuming you're using the GA release of MySQL, or 4.1 at the least


    Code:
    SELECT G.BisState, (SELECT COUNT(X.BisName) FROM golf_courses X WHERE X.BisState = G.BisState) AS count
    FROM golf_courses G
    ORDER BY BisState
    Make sure for search efficiency, that BisState is an index in the table.

    Hope this helps!
    Alex
    circlical - hosting software development
    forums * blog

  11. #11
    Quote Originally Posted by horizon
    And I already told you that I do not respond pretty well to critism.
    The word is criticism. Also, as you can see - people don't have problem with trivial code, but with real code that actually does something. Frankly, I couldn't care less what you told me, it's time for you to stop waisting other peoples time with unusable, blown-up code that doesn't work. There are lots of sites offering tutorials for small price or free. I strongly suggest you visit them and *read* what they say.
    Dyslexics Have More Fnu

  12. #12
    Join Date
    Jan 2004
    Location
    Mobile, AL
    Posts
    138
    MYSQl is 4.1.10.

    Iowa before Idaho because IA < ID
    Yeah, it eventually hit me that it was sorting the abbreviation.

    Alex, I tried that and all I got was a "mysql_fetch_array(): supplied argument is not a valid MySQL result resource " message. I guess I'm a PHP dummy because I have no idea what the letters/periods are for in front of the column name.
    Shawn Kerr

  13. #13
    Join Date
    Aug 2002
    Location
    Canada
    Posts
    667
    Sure it's 4.1? Do it like this, perhaps we can find out what the error might be:

    Code:
    $res = mysql_query( 
    "SELECT G.BisState, (SELECT COUNT(X.BisName) FROM golf_courses X WHERE X.BisState = G.BisState) AS cnt
    FROM golf_courses G
    ORDER BY G.BisState" ) or die( mysql_error() );
    
    while( $row = mysql_fetch_assoc( $res ) )
        echo $row['BisState'].'=>'.$row['cnt'].'<br>';
    circlical - hosting software development
    forums * blog

  14. #14
    Join Date
    Jan 2004
    Location
    Mobile, AL
    Posts
    138
    That's what phpMyAdmin says, 4.1.10.

    You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'SELECT COUNT(X.BisName) FROM golf_courses X WHERE X.BisState =
    I can get them to all print out just fine (http://travel.skaboodle.com/golf/blah.php).

    It seems, for whatever reason, that when I include the code to make the multi-column table that it removed Alaska and Hawaii (Hawaii wouldn't show unless I added a record to it. Alaska has always had records, but never shows).
    Shawn Kerr

  15. #15
    Join Date
    Feb 2005
    Location
    Australia
    Posts
    5,849
    You're losing Alaska in the multi-column table because it's the first result - you're fetching and discarding it without printing:
    Code:
    while($r=mysql_fetch_array($states)){
    ?>
    
    <table width="100%" cellpadding="3" cellspacing="0" border="0">
    <? 
    // isolate
    $numcols = 5;  
    $numcolsprinted = 0;  // no of columns so far
    while($r=mysql_fetch_array($states)){
    Chris

    "Some problems are so complex that you have to be highly intelligent and well informed just to be undecided about them." - Laurence J. Peter

  16. #16
    Join Date
    Jan 2004
    Location
    Mobile, AL
    Posts
    138
    Brilliant!

    Thanks, it's all straight now. And thanks to everyone else who pitched in.
    Shawn Kerr

Posting Permissions

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