CODEWITHSUNDEEP
phpmysqljoin
Sir Lojik
Sir Lojik

Reputation: 1429

PHP MySQL Query to get most popular from two different tables

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);

example of table structure of afrostarprofiles

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

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

Upvotes: 1

Views: 406

Answers (2)

Ben Rowe
Ben Rowe

Reputation: 28711

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

Upvotes: 1

OMG Ponies
OMG Ponies

Reputation: 332541

Use:

  SELECT t.*
    FROM AFROSTARPROFILES t
    JOIN (SELECT p.id,
                 SUM(p.views) + COALESCE(SUM(v.views), 0) AS total_views
            FROM AFROSTARPROFILES p
       LEFT JOIN AFROSTARVIDEOS v ON v.artistid = p.id
        GROUP BY p.id) x ON x.id = t.id
ORDER BY x.total_views DESC

Upvotes: 1

Related Questions