ORDER BY not ordering alphabetically

[multichild]

I have a some code that is drawing out regions when a country a selected.

I want the regions to order alphabetically, but its not doing it for some reason.

$q=mysql_query("select * from tbl_resorts where Id_show=1 ORDER BY Nom_Rsrt") or die (mysql_error());
while($r=mysql_fetch_row($q)) 
{
echo "<script type='text/javascript'>";
echo "region[$r[0]]='$r[1]';";
echo "countryregion[$r[0]]='$r[3]';";
echo "</script>";
 } 

Not sure what the problem is as its working fine, and doing whats asked of it, but its just not being ordered alphabetically

[r937]

please post the results of a SHOW CREATE TABLE, and please show some sample rows

[multichild]

You can see the results on the site if you like.

checksafetyfirst . com and the best one is to select Spain and then look at the drop down. There no alphabetical ordering at all.

I'm gutted in honesty I have left it until Friday, as I cant do it over the weekend now, so will try and give answers if I can.

[guido2004]

You are ordering by Nom_Rsrt. Maybe you should be ordering by region instead?

[multichild]

Hi Guido,

No the ordering by Nom_Rsrt is correct.

That row contains all the resort names, so should be ordering correctly, but for some reason they dont.

Silly I left it until late Friday to post the message, but its a new week now, so hopefully get it sorted asap.

Cheers

[guido2004]

I want the regions to order alphabetically, but its not doing it for some reason.

No the ordering by Nom_Rsrt is correct.
That row contains all the resort names, so should be ordering correctly, but for some reason they dont.

If you order by resort name, the result will be ordered by resort name.
You said you wanted the result ordered by region. If you order by resort name, the result won't be ordered by region.

Unless I'm missing something?

[multichild]

Hi Guido,

Nom_Rsrt contains all the regions eg.

Cancun, Majorca, London, Paris, Barcelona, Rome etc.

They are what populate the drop down after the country is selected, and it's those that I want ordering alphabetically, and thats why its ORDER BY Nom_Rsrt

[guido2004]

So the column 'Nom_rsrt' contains the name of the region?

please post the results of a SHOW CREATE TABLE, and please show some sample rows

Please do what Rudy has asked you. It will help us understand what we're talking about.

[multichild]

Apologies.

CREATE TABLE `tbl_resorts` (
`Id_Rsrt` bigint(20) NOT NULL auto_increment,
`Nom_Rsrt` text NOT NULL,
`Desc_Rsrt` text NOT NULL,
`IdCntry_Rsrt` bigint(20) NOT NULL default '0',
`Id_show` bigint(20) NOT NULL,
PRIMARY KEY (`Id_Rsrt`)
) ENGINE=MyISAM AUTO_INCREMENT=347 DEFAULT CHARSET=latin1

Is that enough data?

[guido2004]

Ok, I understand now. The regions are stored in the resort table.
I took a look at the HTML code of your site, and the regions are present in alphabetical order.

<script type='text/javascript'>region[198]='Abu Simbel';countryregion[198]='9';</script>
<script type='text/javascript'>region[205]='Acapulco';countryregion[205]='6';</script>
<script type='text/javascript'>region[150]='Alexandria';countryregion[150]='9';</script>

The problem I think is, as you can see, that you put them in an array using the Id_Rsrt as array key. So in the end, in the array, they are sorted on Id_Rsrt again.

Try something like

$q=mysql_query("select * from tbl_resorts where Id_show=1 ORDER BY Nom_Rsrt") or die (mysql_error());
$counter = 0;
while($r=mysql_fetch_row($q)) 
{
  echo "<script type='text/javascript'>";
  echo "regionid[$counter]='$r[0]';";
  echo "region[$counter]='$r[1]';";
  echo "countryregion[$counter]='$r[3]';";
  echo "</script>";
  $counter++;
} 

And then adapt your javascript fillregion() function to work with this change as well.

[multichild]

Thank you again Guido,

I can see what you mean so will try this out.

Are you able to help me with the change to the fillregion() function too.

Could you show me what you mean

[guido2004]

I'll move this to the javascript forum now.

[multichild]

Thank you Guido,

With the javascript all I see is this, and its very confusing, as I am expecting to see a lot more, but maybe I'm wrong.

<select name="selectCountry" class="anywhere" onchange="fillregion(this.value)">
<option value="0">anywhere</option>
<?php
$r=mysql_query("select Id_Cntry, Nom_Cntry from tbl_countries order by Nom_Cntry");
while($q=mysql_fetch_assoc($r)){
?>
<option value="<?=$q['Id_Cntry']?>">
<?=$q['Nom_Cntry']?>
</option>
<?php } ?>
</select>

[guido2004]

Thank you Guido,

With the javascript all I see is this, and its very confusing, as I am expecting to see a lot more, but maybe I'm wrong.

<select name="selectCountry" class="anywhere" onchange="fillregion(this.value)">

It means that when the value of selectCountry changes (because the user selects a country) the js function fillregion() will be called, passing it the chosen country value.

The fillregion() function is found in this javascript file: indexScript.js

Let me have a try at modifying it:

function fillregion(countryid)
{
	var i=0;
	document.search.selectRegion.options.length=0;
	if(countryid==0 || i==0 ){
					document.search.selectRegion.options[0] = new Option()
					document.search.selectRegion.options[0].value=0;
					//document.search.selectRegion.options[0].innerText="anywhere";
					document.search.selectRegion.options[0].text="anywhere";
					 i++;
					}

	if(countryid!=0 && i!=0 ){
			for(j=0;j<=countryregion.length;j++)
				{			
					if(countryregion[j]==countryid)
					{
						document.search.selectRegion.options[i] = new Option()
						document.search.selectRegion.options[i].value=regionid[j];
						//document.search.selectRegion.options[i].innerText=region[j];
						document.search.selectRegion.options[i].text=region[j];
					   i++;
					}
				}
	}
swap();
}

The only changed line is indicated in red.

[multichild]

Hi Guido,

Sorry no that didnt work...

The regions didnt show when the country was selected.

www checksafetyfirst com / index2.php

this is where I'm working now.

[guido2004]

Try adding this line to the top of indexScript2.js

// JavaScript Document
var country = new Array();
var region = new Array();
var countryregion = new Array(); 
var regionid = new Array();

[multichild]

Oh Guido youve done it again, thank you very much.

I was concentrating so much on the jscript that I didnt think about that.

Thanks again, it works brilliant.

Cheers