Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 5 of 5
  1. #1
    New Coder
    Join Date
    Jul 2011
    Posts
    52
    Thanks
    4
    Thanked 0 Times in 0 Posts

    Get the highest views from all tables

    Hello
    I have 2 tables and both of them has "views" column.
    I want to use PHP and run a query that will show the highest views content from this two tables, mixed.

    How I can do such a query?
    Need domain? Buy Cheap Domain at Rapid Host :)

  • #2
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    29,267
    Thanks
    83
    Thanked 4,892 Times in 4,853 Posts
    Code:
    SELECT MAX(U.views) FROM (
        SELECT views FROM table1
        UNION 
        SELECT views FROM table2
    ) AS U
    You *might* get better performance from:
    Code:
    SELECT MAX(U.maxview) FROM (
        SELECT MAX(views) AS maxview FROM table1
        UNION 
        SELECT MAX(views) AS maxview FROM table2
    ) AS U
    You can try it both ways.
    Be yourself. No one else is as qualified.

  • #3
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    29,267
    Thanks
    83
    Thanked 4,892 Times in 4,853 Posts
    And it has nothing to do with PHP, per se. The query will do what you want by itself and could be used standalone, with PHP, with ASP, with JSP, etc., etc.
    Be yourself. No one else is as qualified.

  • #4
    New Coder
    Join Date
    Jul 2011
    Posts
    52
    Thanks
    4
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by Old Pedant View Post
    Code:
    SELECT MAX(U.views) FROM (
        SELECT views FROM table1
        UNION 
        SELECT views FROM table2
    ) AS U
    You *might* get better performance from:
    Code:
    SELECT MAX(U.maxview) FROM (
        SELECT MAX(views) AS maxview FROM table1
        UNION 
        SELECT MAX(views) AS maxview FROM table2
    ) AS U
    You can try it both ways.
    It will show only one reslt, doesnt it?
    And if I want to show the the top 3?

    edit:
    I found out how to do it and it works,
    PHP Code:
    $getcontent "SELECT .. FROM
    ( SELECT .. FROM moviesdb
    UNION ALL
    SELECT  ... FROM gamesdb ORDER BY views DESC LIMIT 3) views"

    but I got anothing problem.
    I need to check from what databse I got the result, and I have no idea how to do it and where to place the "AS" or these things.
    help?
    Last edited by topdown.me; 11-09-2012 at 01:10 PM.
    Need domain? Buy Cheap Domain at Rapid Host :)

  • #5
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    29,267
    Thanks
    83
    Thanked 4,892 Times in 4,853 Posts
    Code:
    SELECT U.views, U.fromTable
    FROM ( 
        SELECT views, 'table1' AS fromTable FROM table1 
        UNION 
        SELECT views, 'table2' FROM table2 
    ) AS U
    ORDER BY views DESC
    LIMIT 3
    Be yourself. No one else is as qualified.


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •