[SOLVED] From numerical to alphabetical order - Help

[tqla]

The script below will pull all company names and list them in alphabetical order along with A-Z links across the top. It works fine. The problem is that some of the company names are numerical, like "812 Films" for example. Companies with numerical names show up above the rest.

 

I am thinking that I can assign the letter "O" to the number "1", "T" to the number "2" and so on so that companies with numerical names will get placed in alphabetical order along with the rest. Is this possible? How? Is there a better way? Thanks.

 

<?php
$alpha = array("[[:digit:]]",A,B,C,D,E,F,G,H,I,J,K,L,M,N,O,P,Q,R,S,T,U,V,W,X,Y,Z);
$alphalist = "| ";
$catlist = "";

for($i=0; $i<27; $i++){
$letter = $alpha[$i];
$rs=$conn->Execute("SELECT * FROM $CompaniesTableName WHERE Year='$DirYear' AND Publish='Y' AND CompanyName REGEXP '^$letter' ORDER BY CompanyName");

$numrows=$rs->recordcount();

	if($numrows > 0){
		if($letter <> '[[:digit:]]'){
			$alphalist.= "<a href=\"#" . $letter . "\">" . $letter . "</a> | ";
			$catlist.="<HR><a name=\"" . $letter . "\">" . $letter . "<BR><a href=\"#top\">back to top</a><BR><HR>";
		} else {
			$catlist.="<HR>";
		}


		$catlist.="<blockquote>";
		while (!$rs->EOF) {
			if($rs->Fields(CompanyName) <> $TmpName){
				$catlist.="<a href=\"list.php?ID=" . $rs->Fields(ID) . "\">" . $rs->Fields(CompanyName) . "</a><BR>";
			}
				$TmpName = $rs->Fields(CompanyName);
		$rs->MoveNext();
		}
		$catlist.="</blockquote>";

	}
}

echo "<div align=\"center\">" . $alphalist . "</div><BR>";
echo "<BR>";
echo $catlist;
?>

[Maq]

Try:

 

ORDER BY CompanyName REGEXP '[0-9]', CompanyName

[tqla]

Thanks Maq I tried as you suggested but it doesn't do the trick. What I want to do is mix the companies with names that start with a number with the companies that start with a letter and put them in alphabetical order where 2 becomes T and 4 becomes F, and so on. Hmmmm. I can't figure it out..

[premiso]

Thanks Maq I tried as you suggested but it doesn't do the trick. What I want to do is mix the companies with names that start with a number with the companies that start with a letter and put them in alphabetical order where 2 becomes T and 4 becomes F, and so on. Hmmmm. I can't figure it out..

 

To do it, I doubt it is possible with MySQL, unless you converted the numbers to the actual numbers. It may be possible in the array form, but I think it would be way too much trouble.

 

If you want it done I would suggest whenever you enter a number into the DB that you extract the first character with substr then match that to an item in your array (You would have to set an array of indexes up where 0 would contain Z etc.) And append that character to the first part of the title, then when displaying, you would just remove that part of it. The test if it is a numerical index would be tricky. But that would be the only "easy" way to do it that I could think of.

[tqla]

Oh well, that's what I was afraid of. Thanks premiso.