homepage Welcome to WebmasterWorld Guest from 54.161.175.231
register, free tools, login, search, pro membership, help, library, announcements, recent posts, open posts,
Pubcon Platinum Sponsor 2014
Home / Forums Index / Code, Content, and Presentation / Databases
Forum Library, Charter, Moderators: physics

Databases Forum

    
Order List by two Parameters
Pull top X from database, then sort alphabetically
Jeremy_H




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

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.

 

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

Global Options:
 top home search open messages active posts  
 

Home / Forums Index / Code, Content, and Presentation / Databases
rss feed

All trademarks and copyrights held by respective owners. Member comments are owned by the poster.
Home ¦ Free Tools ¦ Terms of Service ¦ Privacy Policy ¦ Report Problem ¦ About ¦ Library ¦ Newsletter
WebmasterWorld is a Developer Shed Community owned by Jim Boykin.
© Webmaster World 1996-2014 all rights reserved