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? 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? 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 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? 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 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! 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 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. 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. 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)? 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. 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
Archived
This topic is now archived and is closed to further replies.