I am working on a site that displays all possible words from letters entered. There are words in the database up to 15 in length. Approximately 170,000 words. When I enter up to 8 letters, everything works fine. it displays all the 2 letter words in alpha order then 3 letter and 4 letter etc. up to 8 letters. But as soon as I eneter 9 letters, it still correctly displays 2 letter then 3 letter and etc, but no longer in alphabetical order. I worked on this site for this person years ago developing this code and everything worked fine. A couple of years later, this issue appeared and I'm pretty sure I fixed it by re importing the .csv file into the database at that time. But that's not working now. It's clearly not the code because I created a french version that works fine using the same code, but of course a different word database. I can't share the code because I signed an NDA that prohibits sharing that. But as I stated, it's clearly not the code. If I change the table in the code of the bad site to French, it works fine. So it's a data base issue. The bad site is letterunscramble.com and the french site that works is french.letterunscramble.com. The code is html and php with some small bit of js for controls. And, again, is the same code on both sites.
Here is my query statement ...
$result = mysql_query("SELECT * FROM word WHERE LENGTH(word) <= $wordlength order by CHAR_length(word) ASC; ")
or die(mysql_error());
Does anyone have any ideas based on a similar experience or understanding of what may be causing this?
You aren't ordering the query in alphabetical order. The fact that for some inputs you do get it sorted is purely conincidental.
You should explicitly add the word itself as a term to order by:
SELECT *
FROM word
WHERE LENGTH(word) <= $wordlength
ORDER BY CHAR_length(word) ASC, word ASC
-- Here ------------------------^