Data from Multiple Tables Help Please

Discussion in 'Databases' started by ozone1, Aug 11, 2009.

  1. #1
    Hello guys i need some help to show some data from multiple tables.

    I have two tables videos and vids

    in videos i have

    id
    category
    view
    title

    in vids i have

    id
    video_id
    views
    title
    code

    ok what i want is to show most viewed videos (from vids table) of current day on my index page from a particular category (on videos table)

    i made a code and its shows videos from all categories i just want videos from category 14

    <?php
       
    $result[0] = mysql_query("SELECT vids. * FROM vids, videos WHERE videos.category='14' ORDER BY id DESC LIMIT 10");
    
       for($i=0;$i<count($result);$i++) {
          echo '<td style="width:190px;font-size:12px;text-align:left;" valign="top">';
          while($row=mysql_fetch_array($result[$i])) {
             $id = $row['id'];
             $cat = $row['cat'];
             $title = $row['title'];
             $numviews = $row['views'];
       $max_length = 30;
    $title = ( strlen($title) > $max_length ? 
    substr($title,0,$max_length)."..." : $title );
             
    
    echo '<li><a href="videos.php?id=' . $id . '" class="forceRight">'. $title . '<a class="numbers">' . $numviews . ' views</a></a></li>';
    }
    
    }
    
    
    ?>
    PHP:
    and right now there are 0 views for all videos but still its appearing according to id on my index page i want it to show empty space if the view count is zero

    Any help much appreciated !!!

    Thanks
     
    ozone1, Aug 11, 2009 IP
  2. DaiLaughing

    DaiLaughing Peon

    Messages:
    3
    Likes Received:
    0
    Best Answers:
    0
    Trophy Points:
    0
    #2
    I don't really understand what you are saying but I do know that your HTML is a mess. You seem to have a table but then your data is in a list. You have nested A tags in the list. Paste the page content from your browser View Source into the w3c validator and fix the mark-up.

    If you are looking to only list videos with a view value over 0 then you could just add to the WHERE to says that. You seem to be saying you want an empty space for those videos in which case replace your current echo of the LI with an IF statement which echos a blank LI if the value is 0.
     
    DaiLaughing, Aug 12, 2009 IP
  3. sacx13

    sacx13 Active Member

    Messages:
    438
    Likes Received:
    10
    Best Answers:
    0
    Trophy Points:
    58
    #3
    I think the most simple approach for what you want

     
    sacx13, Aug 17, 2009 IP
  4. jobinjjp

    jobinjjp Peon

    Messages:
    42
    Likes Received:
    0
    Best Answers:
    0
    Trophy Points:
    0
    #4
    why didn't you try INNER JOIN...???????
     
    jobinjjp, Aug 23, 2009 IP