SitePoint Sponsor

User Tag List

Results 1 to 5 of 5
  1. #1
    SitePoint Enthusiast
    Join Date
    May 2007
    Location
    Oslo, Norway
    Posts
    74
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Variables for rows in query

    Hi folks,

    I'm trying to make a videolist for an artist that should display 3 thumbs next to each other and some text (videotitle, origin year, etc.) for each video.
    I have a query that gets 3 image filenames (only 3 can be uploaded with each video) from one table and a title, originyear, etc. from another table, sorted by a searchkey in table1 that is similar the id in table2.
    PHP Code:
        $videolist = @mysql_query(
        
    "select videoimg_filename, searchkey, video_id, title, originyear, date
        from videoimages, videos where searchkey=video_id"
    );
        if (!
    $videolist) {
        exit(
    '<p>Error performing query: ' mysql_error() . '</p>');
        }
        while (
    $videos mysql_fetch_array($videolist)) {
        
    $title $videos['title'];
        
    $date $videos['date'];
        
    $image $videos['videoimg_filename'];
        
    $id $videos['video_id'];
        
        
    //Displays video images (3) and title (once)
        
    echo "<p><span class='videoimg'><a href='video.php?id=$id'><img src='" "../videoimg/tb_$image"' border='0' ></a></span>" "<span class='videotitle'><a href='video.php?id=$id'>$title</a></span></p>";
        } 
    The problem is that it displays all images in a list with one image per row and the same text info for every 3 images. I want the 3 images next to each other and the text displayed only once.
    Is it possibleto get three image varibles ($img1[row1], $img2[row2], $img3[row3]) based on the three rows in the result array, and one $title variable (i.e first row in the array)?

    I have tried to GROUP BY searchkey, but then I only get one image.

    Or is there a better way?

    Thanks.

  2. #2
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,500
    Mentioned
    163 Post(s)
    Tagged
    4 Thread(s)
    Code PHP:
    $videolist = mysql_query("
      SELECT 
          videoimg_filename
        , searchkey
        , video_id
        , title
        , originyear
        , date
      FROM videoimages
      INNER JOIN videos   
      ON searchkey=video_id
      ORDER BY video_id
    ");
    if (!$videolist) {
      exit('<p>Error performing query: ' . mysql_error() . '</p>');
    }
    $id = '';
    while ($videos = mysql_fetch_array($videolist)) {
      // handle the first vid
      if ($id == '') {
        echo "<p>";
        $id = $videos['video_id'];
        $title = $videos['title'];
        $date = $videos['date'];
      }
     
      // handle a change of vids
      if ($id != $videos['video_id']) {
        echo "<span class='videotitle'><a href='video.php?id=$id'>$title</a></span></p><p>";
        $id = $videos['video_id'];
        $title = $videos['title'];
        $date = $videos['date'];
      }
     
      // display the vid thumbnail
      $image = $videos['videoimg_filename'];
      echo "<span class='videoimg'><a href='video.php?id=$id'><img src='" . "../videoimg/tb_$image" . "' border='0' ></a></span>";
    } 
    // if vids have been found, echo the last closing </p> tag
    if (id != '') echo "</p>";

  3. #3
    SitePoint Enthusiast
    Join Date
    May 2007
    Location
    Oslo, Norway
    Posts
    74
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks Guido,

    I tried this and it sorted out the list of images.
    But I only got the title visible on the first listing, the next one was blank.

    I tried to upload another video and then the title appeared on the second last one. So it seems like the title will be missing on the last entry.

  4. #4
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,500
    Mentioned
    163 Post(s)
    Tagged
    4 Thread(s)
    Oops
    Change the last line in
    Code PHP:
    // if vids have been found, echo the last vids title and the closing </p> tag
    if (id != '') {
        echo "<span class='videotitle'><a href='video.php?id=$id'>$title</a></span></p>";
    }

  5. #5
    SitePoint Enthusiast
    Join Date
    May 2007
    Location
    Oslo, Norway
    Posts
    74
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Great!
    Thanks a lot for sorting this out!


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
  •