Selecting from multiple tables to have a popular items table
I have three tables in a MySQL database (af_reviews,af_tabs,and af_faqs). On the main page is a table which is supposed to have the ten most popular (most views) and ten best rated from those three tables. However, I can't figure out the correct MySQL/PHP syntax to do this. I believe I'm supposed to use JOIN but I'm not 100% sure if I am or how it's used.
Table Structures:
af_tabs:
-id tinyint(6) null auto_increment primary
-song text not null
-band text not null
-author text not null
-instrument text not null
-content longtext not null
-views tinyint(6) not null
-rating tinyint(6) not null
af_reviews:
-id tinyint(6) null auto_increment primary
-title text not null
-author text not null
-instrument text not null
-content longtext not null
-views tinyint(6) not null
-rating tinyint(6) not null
af_faqs:
-id tinyint(6) null auto_increment primary
-title text not null
-author text not null
-instrument text not null
-content longtext not null
-views tinyint(6) not null
-rating tinyint(6) not nullWhat I need to do is for one draw out the 10 highest viewed ids from all three tables (could be 3 from one and 7 from another, as long as it's the highest viewed), then for the other it does the same but for rating.
Help? Thanks!
EDIT: I was looking at the MySQL Documentation. Is the proper syntax for JOIN like this:
SELECT views FROM af_faqs JOIN af_reviews JOIN af_tabs ORDER BY views DESC LIMIT 10'
?
[James Logsdon]
druagord posted this at 22:17 — 13th June 2003.
He has: 335 posts
Joined: May 2003
joined select will work when data on your tables are link together for exemple if you have a table for artist and a table for songs then you could get the top ten songs of the top ten artist. Now i might be wrong but i don't think there is a rellation between your tables so you have to do three querys to get the result you want here is the first one the others will be similar.
SELECT * FROM af_tabs ORDER BY views DESCENDING LIMIT 0,10IF , ELSE , WHILE isn't that what life is all about
necrotic posted this at 23:29 — 13th June 2003.
He has: 296 posts
Joined: May 2002
But then I'd have to do massive (I think) ammounts of PHP to get it to do what I want. I want only 10 from all 3 tables, a max of 10, not 30.
mairving posted this at 02:45 — 14th June 2003.
They have: 2,256 posts
Joined: Feb 2001
You still need to have a way that the tables link together, generally known as foreign key. It looks like you need to rewrite your tables. You could put most of your info into one table, then split out a separate table for ratings and include a ratings_id in the first table that links to the ratings table. Probably would also have a table for authors. Then you could do a pretty easy select.
Keep in mind that one of the main goals of a database (besides a place to store your stuff) is to avoid redundant data.
Mark Irving
I have a mind like a steel trap; it is rusty and illegal in 47 states
necrotic posted this at 03:20 — 14th June 2003.
He has: 296 posts
Joined: May 2002
Okay, guess I'll rewrite them. I've been thinking about doing it, but it's been in the back of my mind.
Want to join the discussion? Create an account or log in if you already have one. Joining is fast, free and painless! We’ll even whisk you back here when you’ve finished.