dachshund Posted July 5, 2009 Share Posted July 5, 2009 Hi, I'm currently trying to give a list of the five most viewed pages on my website. There are 3 main areas on the site - interviews, features and reviews. Currently I have a 'most viewed' section for each one individually. Which for interviews, looks like this. <?php $today = date('Y-m-d'); $sql="SELECT * FROM interviews ORDER BY views DESC LIMIT 5"; $result=mysql_query($sql); ?> But now I want to combine all the tables (interviews, features, reviews). There is a row called 'views' in each. I hope that makes sense. Can anyone help? Quote Link to comment https://forums.phpfreaks.com/topic/164862-views-row-from-multiple-tables/ Share on other sites More sharing options...
waynew Posted July 5, 2009 Share Posted July 5, 2009 Could you give us a screenshot/rundown of your table setup? Quote Link to comment https://forums.phpfreaks.com/topic/164862-views-row-from-multiple-tables/#findComment-869362 Share on other sites More sharing options...
dachshund Posted July 5, 2009 Author Share Posted July 5, 2009 tables: interviews features reviews fields in each of these tables: id, title, information, content, image, link, type, wordsby, VIEWS Quote Link to comment https://forums.phpfreaks.com/topic/164862-views-row-from-multiple-tables/#findComment-869381 Share on other sites More sharing options...
dachshund Posted July 5, 2009 Author Share Posted July 5, 2009 im not sure why this was moved to Mysql help? I just need something like $sql="SELECT * FROM interviews, features, reviews ORDER BY views DESC LIMIT 5"; that wouldn't work though right? Quote Link to comment https://forums.phpfreaks.com/topic/164862-views-row-from-multiple-tables/#findComment-869390 Share on other sites More sharing options...
Zane Posted July 5, 2009 Share Posted July 5, 2009 SELECT MAX(views) FROM interviews i , features f, reviews r you may get an "ambiguous" error.. if so you'll have to do this SELECT MAX(i.views), MAX(f.views), MAX(r.views) FROM interviews i , features f, reviews r Quote Link to comment https://forums.phpfreaks.com/topic/164862-views-row-from-multiple-tables/#findComment-869406 Share on other sites More sharing options...
dachshund Posted July 5, 2009 Author Share Posted July 5, 2009 like this? $sql="SELECT MAX(i.views), MAX(f.views), MAX(r.views) FROM FROM interviews i , features f, reviews r ORDER BY views DESC LIMIT 5"; i can't get it to work. thanks for all help! Quote Link to comment https://forums.phpfreaks.com/topic/164862-views-row-from-multiple-tables/#findComment-869409 Share on other sites More sharing options...
Zane Posted July 5, 2009 Share Posted July 5, 2009 tables: interviews features reviews fields in each of these tables: id, title, information, content, image, link, type, wordsby, VIEWS If all of those tables have the same schema...why don't you just put them all in one table like .... articles id, title, information, content, image, link, type, wordsby, VIEWS, article_type where article_type could be the......well....article type.... interview, feature, or review. Or you could use numbered values where article_type could be like 0 for interview, 1 for feature, 2 for review, etc,etc,etc Quote Link to comment https://forums.phpfreaks.com/topic/164862-views-row-from-multiple-tables/#findComment-869410 Share on other sites More sharing options...
dachshund Posted July 5, 2009 Author Share Posted July 5, 2009 ah yeah that is a good idea. although there are some different fields in each of the tables which i didn't include in my post here. Quote Link to comment https://forums.phpfreaks.com/topic/164862-views-row-from-multiple-tables/#findComment-869414 Share on other sites More sharing options...
Zane Posted July 5, 2009 Share Posted July 5, 2009 well....... just add them as NULL columns then. Quote Link to comment https://forums.phpfreaks.com/topic/164862-views-row-from-multiple-tables/#findComment-869417 Share on other sites More sharing options...
dachshund Posted July 5, 2009 Author Share Posted July 5, 2009 ok. i might work on that tomorrow. what are the advantages of having it all in one table (other than having views all in the same place)? Quote Link to comment https://forums.phpfreaks.com/topic/164862-views-row-from-multiple-tables/#findComment-869424 Share on other sites More sharing options...
Zane Posted July 5, 2009 Share Posted July 5, 2009 what are the advantages of having it all in one table (other than having views all in the same place)? well for one....it's less complicated and you don't need a JOIN for every single thing. you can easily select all the interviews, reviews, or features with a WHERE clause. Quote Link to comment https://forums.phpfreaks.com/topic/164862-views-row-from-multiple-tables/#findComment-869431 Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.