PHP MySQL Query从两个不同的表中最受欢迎 [英] PHP MySQL Query to get most popular from two different tables

问题描述

我创建了两个表afro​​starprofiles和afrostarvvideos，如下所示. afrostarvideos中的artistid字段是afrostarprofiles的主要ID.我想要一个查询，该查询将基于视图和afrostarprofiles的总和为我返回最受欢迎的afrostarprofile.我基本上需要一种算法或查询，该算法或查询将基于来自afrostarvideos的视频和来自afrostarprofiles的视图的总观看次数对afrostarprofiles进行排名.这可能吗.我知道我可以用php处理sql结果来做到这一点，但我认为mysql有一种更有效的方法.任何帮助表示赞赏

I have two tables afrostarprofiles and afrostarvvideos created as shown below. artistid field in afrostarvideos is the primary id of afrostarprofiles. I want a single query that will return for me most popular afrostarprofile based on sum of views(afrostarvideo) and views of afrostarprofiles. i basically need an algorithm or query that will rank afrostarprofiles based on total views of videos from afrostarvideos and views from afrostarprofiles. Is this possible. i know i can do this with php processing sql results but i think there is a more efficient way with mysql. any help is appreciated

    $ct_members="CREATE TABLE `afrostarprofiles` (".
                "`id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY ,".
                "`name` TEXT NOT NULL ,".
                "`country` TEXT NOT NULL ,".
                "`piclocation` TEXT NOT NULL ,".
                "`views` INT NOT NULL ,".
                "`date` INT NOT NULL );";
    mysql_query($ct_members);  

        $ct_members="CREATE TABLE `afrostarvideos` (".
                    "`id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY ,".
                    "`name` TEXT NOT NULL ,".
                    "`url` TEXT NOT NULL ,".
                    "`views` INT NOT NULL ,".
                    "`artistid` INT NOT NULL ,".                        
                    "`date` INT NOT NULL );";
        mysql_query($ct_members);

霜冻剖面表结构示例

4 Jose Chameleon Uganda afrostarpics/4.jpg 10 1287082754
3 Ziggy Dee Uganda afrostarpics/3.jpg 44 1286494407

afrostarvideo个人资料的示例

examples of afrostarvideo profile

12 Tebamatila www 11 3 1287136275
13 Mamba Mtu www 14 3 1287136524
14 TECHNOLOGY www 15 4 1287170779
125 Jamila    www 14 4 1287387760

推荐答案

我认为您需要类似的东西

I think you need something like

SELECT `p`.*, SUM(`v`.`views`)+`p`.`views` AS `totalViews` FROM `afrostarprofiles` `p` 
LEFT OUTER JOIN `afrostarvideos` `v` ON `p`.`id` = `v`.`artistid`
GROUP BY `p`.`id`
ORDER BY SUM(`v`.`views`)+`p`.`views` DESC

这篇关于PHP MySQL Query从两个不同的表中最受欢迎的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！