LifeinAsia
msg:3220261 | 9:58 pm on Jan 15, 2007 (gmt 0) |
I'm not sure about mySQL, but you should be able to do something like:
SELECT *
FROM FAQ
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?
|
Jeremy_H
msg:3220263 | 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.
|
LifeinAsia
msg:3220272 | 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.
|
Jeremy_H
msg:3220371 | 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.
|
LifeinAsia
msg:3220417 | 12:05 am on Jan 16, 2007 (gmt 0) |
What part doesn't seem to be working?
|
Jeremy_H
msg:3220427 | 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: while($row=mysql_fetch_array($result)) {...} 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.
|
LifeinAsia
msg:3220452 | 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?
|
Woz
msg:3220461 | 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. Onya
Woz
|
|
