Welcome to WebmasterWorld Guest from 22.214.171.124
Forum Moderators: open
This database has a rows that contain a question, answer, and the number of times the question was asked.
I would like to grab the 20 most asked questions from this database, but to arrange this list alphabetically by question.
I can pull the 20 most asked questions, but they are not arranging themselves alphabetically.
This is what I have so far:
mysql_query("SELECT * FROM faq ORDER BY count DESC, question LIMIT 20");
Thanks for any help.
By the way, you don't really have a field named "count" do you?
It seems like that would only give my the top 20 most requested files, which is great, but then how do I resort this short list into alphabetical order?
You did bring up a good point. I really do not have a field called count, I just used that title for this question in an attempt to simplify the question.
but then how do I resort this short list into alphabetical order?
Actually, it's good that you do NOT have a field called Count. As a reserved word, it shouldn't work for most implementations of SQL. And even if it does, it's just bad design.
Per your advice I renamed count to asked in this example and tried the following format:
mysql_query("SELECT * FROM faq WHERE question IN (SELECT * FROM faq ORDER BY asked DESC LIMIT 20) ORDER BY question");
The format makes total sense, but unfortunately doesn't seem to be working for me, at least in the mySQL environment.
The error I get is "mysql_fetch_array(): supplied argument is not a valid MySQL result resoure"
This second line of code works fine when the line above was set to $result=mysql_query(...) of previous statements, but not of the latest statement.
Assuming you are using mySQL 5 or above, create a view to filter the top 20 using SELECT * FROM faq ORDER BY asked DESC LIMIT 20.
You could then use your scripting to pull SELECT * FROM viewFAQtop20 ORDER BY question.
When you get that sorted, you could take it the next step by creating a second view to reorder the initial results and simply pull the data from that second view all ready to go. Others more better than I at this could probably put all of that into a single view.