SitePoint Sponsor

User Tag List

Results 1 to 17 of 17
  1. #1
    SitePoint Guru
    Join Date
    Feb 2006
    Location
    Chepstow, South Wales
    Posts
    910
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    ORDER BY not ordering alphabetically

    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.

    PHP Code:
    $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

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,322
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    please post the results of a SHOW CREATE TABLE, and please show some sample rows
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Guru
    Join Date
    Feb 2006
    Location
    Chepstow, South Wales
    Posts
    910
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.

  4. #4
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,506
    Mentioned
    163 Post(s)
    Tagged
    4 Thread(s)
    You are ordering by Nom_Rsrt. Maybe you should be ordering by region instead?

  5. #5
    SitePoint Guru
    Join Date
    Feb 2006
    Location
    Chepstow, South Wales
    Posts
    910
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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

  6. #6
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,506
    Mentioned
    163 Post(s)
    Tagged
    4 Thread(s)
    Quote Originally Posted by multichild View Post
    I want the regions to order alphabetically, but its not doing it for some reason.
    Quote Originally Posted by multichild View Post
    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?

  7. #7
    SitePoint Guru
    Join Date
    Feb 2006
    Location
    Chepstow, South Wales
    Posts
    910
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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

  8. #8
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,506
    Mentioned
    163 Post(s)
    Tagged
    4 Thread(s)
    So the column 'Nom_rsrt' contains the name of the region?
    Quote Originally Posted by r937 View Post
    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.

  9. #9
    SitePoint Guru
    Join Date
    Feb 2006
    Location
    Chepstow, South Wales
    Posts
    910
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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?

  10. #10
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,506
    Mentioned
    163 Post(s)
    Tagged
    4 Thread(s)
    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.
    Code:
    <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
    PHP Code:
    $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.

  11. #11
    SitePoint Guru
    Join Date
    Feb 2006
    Location
    Chepstow, South Wales
    Posts
    910
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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

  12. #12
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,506
    Mentioned
    163 Post(s)
    Tagged
    4 Thread(s)
    I'll move this to the javascript forum now.

  13. #13
    SitePoint Guru
    Join Date
    Feb 2006
    Location
    Chepstow, South Wales
    Posts
    910
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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>

  14. #14
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,506
    Mentioned
    163 Post(s)
    Tagged
    4 Thread(s)
    Quote Originally Posted by multichild View Post
    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:
    Code:
    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.

  15. #15
    SitePoint Guru
    Join Date
    Feb 2006
    Location
    Chepstow, South Wales
    Posts
    910
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.

  16. #16
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,506
    Mentioned
    163 Post(s)
    Tagged
    4 Thread(s)
    Try adding this line to the top of indexScript2.js
    Code:
    // JavaScript Document
    var country = new Array();
    var region = new Array();
    var countryregion = new Array(); 
    var regionid = new Array();

  17. #17
    SitePoint Guru
    Join Date
    Feb 2006
    Location
    Chepstow, South Wales
    Posts
    910
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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


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
  •