Results 1 to 16 of 16
Thread: Array Order + Other Problem
-
11-22-2006, 07:52 PM #1WHT Addict
- 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
}
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
-
11-22-2006, 08:03 PM #2WHT Addict
- 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
-
11-22-2006, 08:34 PM #3WHT Addict
- 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
-
11-22-2006, 09:38 PM #4Web Hosting Master
- 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.
-
11-22-2006, 10:39 PM #5Web Hosting Evangelist
- Join Date
- Apr 2005
- Posts
- 522
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
-
11-22-2006, 10:41 PM #6Web Hosting Master
- Join Date
- Mar 2006
- Posts
- 984
I told you already to learn something first and then help others.
-
11-22-2006, 10:58 PM #7Web Hosting Master
- Join Date
- Dec 2003
- Location
- Vancouver BC, eh?
- Posts
- 571
Originally Posted by horizon
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.
-
11-22-2006, 11:39 PM #8WHT Addict
- 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
-
11-22-2006, 11:54 PM #9Retired Moderator
- Join Date
- Feb 2005
- Location
- Australia
- Posts
- 5,849
Originally Posted by mobilebadboy
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
-
11-23-2006, 03:09 AM #10Web Hosting Master
- 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
Hope this helps!
Alex
-
11-23-2006, 05:08 PM #11Web Hosting Evangelist
- Join Date
- Apr 2005
- Posts
- 522
Originally Posted by horizonDyslexics Have More Fnu
-
11-23-2006, 06:43 PM #12WHT Addict
- Join Date
- Jan 2004
- Location
- Mobile, AL
- Posts
- 138
MYSQl is 4.1.10.
Iowa before Idaho because IA < ID
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
-
11-23-2006, 07:17 PM #13Web Hosting Master
- 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>';
-
11-23-2006, 08:47 PM #14WHT Addict
- 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 =
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
-
11-23-2006, 08:58 PM #15Retired Moderator
- 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
-
11-23-2006, 11:40 PM #16WHT Addict
- 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