| 9:58 pm on Jan 15, 2007 (gmt 0)|
I'm not sure about mySQL, but you should be able to do something like:
WHERE question IN (SELECT question, count FROM FAQ ORDER BY count DESC LIMIT 20)
ORDER BY Question
By the way, you don't really have a field named "count" do you?
| 10:02 pm on Jan 15, 2007 (gmt 0)|
Thanks for your reply,
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.
| 10:08 pm on Jan 15, 2007 (gmt 0)|
|but then how do I resort this short list into alphabetical order? |
That would be the the "ORDER BY Question" part (I assumed the Question field was what you wanted to sort alphabetically).
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.
| 11:25 pm on Jan 15, 2007 (gmt 0)|
Sorry, I completely misread your post!
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.
| 12:05 am on Jan 16, 2007 (gmt 0)|
What part doesn't seem to be working?
| 12:23 am on Jan 16, 2007 (gmt 0)|
I don't get a direct error, but I get an error on the next line where I have:
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.
| 12:48 am on Jan 16, 2007 (gmt 0)|
I don't know enough about mySQL. Can you dump out all the results to see if you're getting what you ecpect?
| 1:04 am on Jan 16, 2007 (gmt 0)|
Wherever possible, use the database to do database type work.
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.