Adding Provinces and States by country

[cflo]

Here is what I found:

function getStates()
{
global $db;
$sSQL="SELECT name FROM ".PREFIX."states ORDER BY name ASC";
$result=$db->query($sSQL);
while($rs=$result->fetch()){
$states[]=$rs;
}
$result->freeResult();
return $states;
}

Modified:

$sSQL="SELECT name FROM ".PREFIX."ORDER BY abbrev, name ASC";

Is this correct?
Thanks!

Originally Posted by Mike-N-Tosh

I guess actually the proper syntax would be "ORDER BY abbrev, name ASC"

I think that should do it.

-Mike

[Mike-N-Tosh]

That's exactly what you're looking for. Just add the abbrev to the ORDER by like this:

$sSQL="SELECT name FROM ".PREFIX."states ORDER BY abbrev, name ASC";

Always keep a back up! Always comment your modifications, so you know what you did later on!

-Mike

[cflo]

I just added Hawaii and it came up 3rd on the list. The code did not change anything. Am I doing something wrong?

Originally Posted by Mike-N-Tosh

That's exactly what you're looking for. Just add the abbrev to the ORDER by like this:

$sSQL="SELECT name FROM ".PREFIX."states ORDER BY abbrev, name ASC";

Always keep a back up! Always comment your modifications, so you know what you did later on!

-Mike

[Mike-N-Tosh]

The suggested modification was based on the assumption of what you stated earlier in that you entered "CAN" and "USA" in the abbrev fields for the states. It should work as it is sorting by the abbreviation first then by the name.

-Mike

[cflo]

I added the states abbrev USA and CAN but I does not seem to work.
It still picks up the state name before the abbreviation.

Originally Posted by Mike-N-Tosh

The suggested modification was based on the assumption of what you stated earlier in that you entered "CAN" and "USA" in the abbrev fields for the states. It should work as it is sorting by the abbreviation first then by the name.

-Mike

[Mike-N-Tosh]

Hmmm, the only thing I can think is that you may need to also select the abbrev in order to ORDER BY it.

Try this:
$sSQL="SELECT name, abbrev FROM ".PREFIX."states ORDER BY abbrev, name ASC";

-Mike

[cflo]

I tried that now, but no luck. Same thing.
Should this reflect in my "/administration/settingsstates.php" admin view?

Originally Posted by Mike-N-Tosh

Hmmm, the only thing I can think is that you may need to also select the abbrev in order to ORDER BY it.

Try this:
$sSQL="SELECT name, abbrev FROM ".PREFIX."states ORDER BY abbrev, name ASC";

-Mike

[Mike-N-Tosh]

$sSQL="SELECT name FROM ".PREFIX."states ORDER BY abbrev ASC, name ASC";

[cflo]

That dont work either.

Originally Posted by Mike-N-Tosh

$sSQL="SELECT name FROM ".PREFIX."states ORDER BY abbrev ASC, name ASC";

[Mike-N-Tosh]

Well, I don't understand that. Perhaps someone smarter than I can help out. Sorry.

-Mike