Welcome to WebmasterWorld Guest from 23.20.230.24

Forum Moderators: open

Message Too Old, No Replies

Order List by two Parameters

Pull top X from database, then sort alphabetically

     
9:17 pm on Jan 15, 2007 (gmt 0)

5+ Year Member



I have a large MySQL database of "Frequently Asked Questions".

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.

9:58 pm on Jan 15, 2007 (gmt 0)

WebmasterWorld Administrator lifeinasia is a WebmasterWorld Top Contributor of All Time 5+ Year Member Top Contributors Of The Month



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?

10:02 pm on Jan 15, 2007 (gmt 0)

5+ Year Member



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)

WebmasterWorld Administrator lifeinasia is a WebmasterWorld Top Contributor of All Time 5+ Year Member Top Contributors Of The Month



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)

5+ Year Member



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)

WebmasterWorld Administrator lifeinasia is a WebmasterWorld Top Contributor of All Time 5+ Year Member Top Contributors Of The Month



What part doesn't seem to be working?
12:23 am on Jan 16, 2007 (gmt 0)

5+ Year Member



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.

12:48 am on Jan 16, 2007 (gmt 0)

WebmasterWorld Administrator lifeinasia is a WebmasterWorld Top Contributor of All Time 5+ Year Member Top Contributors Of The Month



I don't know enough about mySQL. Can you dump out all the results to see if you're getting what you ecpect?

Woz

1:04 am on Jan 16, 2007 (gmt 0)

WebmasterWorld Senior Member woz is a WebmasterWorld Top Contributor of All Time 10+ Year Member



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

 

Featured Threads

Hot Threads This Week

Hot Threads This Month