Page 1 of 1

sorting

Posted: Mon Nov 08, 2004 8:08 am
by gurjit
i have two arrays which i merge

1.
array_push($domain_array,"j".$primary_id . "p?" . "<strong>". $dp_hostname. "</strong>" . "?" . $dp_next_renewal);


2.
array_push($domain_array,"k".$secondary_id . "s?" .$ds_hostname . "?" . $ds_next_renewal);

the $dp_hostname and $ds_hostname are domain name strings, for example like
http://www.msn.com
http://www.msn.co.uk
etc

I want to be able to sort the array by the .com, co.uk, .net etc in ascending order. how can i do this?

or alternatively can i do it in the query
select * from tbl_domains order by ???????

thanks for any help

Posted: Mon Nov 08, 2004 9:09 am
by Weirdan
assuming you have the table named 'countries' with all the ISO country codes listed:

Code: Select all

select 
   domains.* 
from 
   domains 
left join 
   countries 
on 
   substring_index(domain,'.',-1) = countries.sname 
order by 
   sname is null desc, 
   substring_index( domain,  '.',  if(sname is null, -1, -2) ) asc

Posted: Mon Nov 08, 2004 9:18 am
by gurjit
i tried this because the domains are in one table and it never worked

Code: Select all

<?php
select primary_id, dp_hostname, dp_next_renewal from tbl_domains_primary where dp_status = 1 order by substring_index( dp_hostname,  '.',  if(dp_hostname is null, -1, -2) ) asc
?>

Posted: Mon Nov 08, 2004 9:34 am
by Weirdan
My query requires additional country table.

Posted: Mon Nov 08, 2004 9:38 am
by gurjit
i changed it to read as below and it worked.....thanks....

best website to look at for example is:
http://carneeki.net/index.php/docs/mysq ... ress_sort/

Code: Select all

select primary_id, dp_hostname, dp_next_renewal from tbl_domains_primary where dp_status = 1 order by SUBSTRING_INDEX(SUBSTRING_INDEX(dp_hostname,".",2),".",-1)
thanks for your help Weirdan....you the man....