SitePoint Sponsor

User Tag List

Results 1 to 9 of 9
  1. #1
    SitePoint Enthusiast
    Join Date
    Sep 2008
    Location
    EAST COAST
    Posts
    60
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    How to make more than one thing display from a table?? Please help

    SQL Problem - Simple fix
    All I want to do is show the top 10 viewed videos in my admin of a porn site. I know the names of my tables. I know which content I want to show. I am learning PHP and have not learned SQL. I can make it show the top 10 Viewed videos in descending order but the titles do not display so I can know which video has been viewed the most.

    This -
    $result = mysql_query(' SELECT *
    FROM `content_views`


    ORDER BY views DESC
    LIMIT 0 , 10 ');


    Makes it show my top 10 views but it does not display the title of the video
    It displays Just like this

    Top 10 Videos
    118 views
    75 views
    71 views
    70 views
    66 views
    65 views
    65 views
    64 views
    61 views
    56 views

  2. #2
    ✯✯✯ silver trophybronze trophy php_daemon's Avatar
    Join Date
    Mar 2006
    Posts
    5,284
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    What you need is a join on your videos table. Something like this...

    Code sql:
    SELECT content_views.views
         , videos.title
    FROM content_views
    JOIN videos 
      ON videos.id = content_views.id
    ORDER BY views DESC 
    LIMIT 0 , 10

  3. #3
    SitePoint Enthusiast
    Join Date
    Sep 2008
    Location
    EAST COAST
    Posts
    60
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I did that and it disapeared all together.
    I even tried to change to the table names like below and it didnt work either.


    $result = mysql_query('SELECT content_views.views
    , content.title
    FROM content_views
    JOIN content
    ON content.id = content_views.id
    ORDER BY views DESC
    LIMIT 0 , 10');

    If it helps here is my SQL database


  4. #4
    ✯✯✯ silver trophybronze trophy php_daemon's Avatar
    Join Date
    Mar 2006
    Posts
    5,284
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    This is likely wrong:

    ON content.id = content_views.id

    How does the structure of 'content' table look like?

  5. #5
    SitePoint Enthusiast
    Join Date
    Sep 2008
    Location
    EAST COAST
    Posts
    60
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    that table looks like this

    title
    filename
    orig_filename
    thumbnail
    description
    paysite
    keywords
    pornstars
    scheduled_date
    encoded_date
    date_added
    views
    rating
    length
    submitter
    ip
    approved
    hotlinked
    record_num

  6. #6
    ✯✯✯ silver trophybronze trophy php_daemon's Avatar
    Join Date
    Mar 2006
    Posts
    5,284
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    Is record_num the unique identifier? Does it correspond to 'content' column in content_views? If so, then it should be:

    Code sql:
    SELECT content_views.views
         , content.title
    FROM content_views
    JOIN content
      ON content.record_num = content_views.content
    ORDER BY views DESC
    LIMIT 0 , 10

  7. #7
    SitePoint Enthusiast
    Join Date
    Sep 2008
    Location
    EAST COAST
    Posts
    60
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    That did the trick thank you very much

  8. #8
    SitePoint Enthusiast
    Join Date
    Sep 2008
    Location
    EAST COAST
    Posts
    60
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Should I make another thread for another problem on the same script

  9. #9
    ✯✯✯ silver trophybronze trophy php_daemon's Avatar
    Join Date
    Mar 2006
    Posts
    5,284
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    Yeah, unless it's about the same query...


Bookmarks

Posting Permissions

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