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 3 of 3
  1. #1
    New Coder
    Join Date
    Oct 2011
    Posts
    24
    Thanks
    6
    Thanked 0 Times in 0 Posts

    Combine multiple where queries?

    I've got this thing in my mind, but I'm not sure on how to accomplish it. I'm working on a video site and when a visitor watches a video I want to show 5 related vids on the side (like youtube). I'd like to order them by how many tags of the current video match the tags of the related video.

    One video contains the following tags: "federer, nadal, wimbledon" and there's one more video that contains the exact same tags.

    I'd like that video to be the top related video, the second one for example a video that contains the tags "federer, nadal, australian open" and the third one for example a video that contains the tags "federer, djokovic, roland garros".

    Here's the code I have so far:

    PHP Code:
        $keywords preg_split('/,\s*/'$getvideos['tags']); 
        
    $where 'WHERE (';
        
    $where2 'WHERE (';
        
        foreach (
    $keywords as $keyword) {   
        
    $where .= "tags LIKE '%" mysql_real_escape_string($keyword) . "%'"
        
    $where .= ' AND ';
        
    $where2 .= "tags LIKE '%" mysql_real_escape_string($keyword) . "%'"
        
    $where2 .= ' OR '
        
    $tags .= "<a href='tags.php?tags=" mysql_real_escape_string($keyword) . "'>" mysql_real_escape_string($keyword) . "</a>";
        
    $tags .= ", ";
        }
        
    $where rtrim($where" AND "); //remove the last "AND" 
        
    $where2 rtrim($where2" OR "); // remove the last "OR"
        
    $tags rtrim($tags", "); // remove the last ", "
        
    $where $where ")";
        
    $where2 $where2 ")";
        
     if (
    $where) { 
      
    $where "$where AND id != '".intval($getvideos['id'])."'"
     } 
     else { 
      
    $where "id != '".intval($getvideos['id'])."'"
     }

     if (
    $where2) { 
      
    $where2 "$where2 AND id != '".intval($getvideos['id'])."'"
     } 
     else { 
      
    $where2 "id != '".intval($getvideos['id'])."'"
     } 
    Now I have to find a way to make a mysql query that starts with the most relating videos and so on. I'm a little clueless on how to proceed from here on... any suggestions?

    Thanks!

  • #2
    New Coder
    Join Date
    Oct 2011
    Posts
    24
    Thanks
    6
    Thanked 0 Times in 0 Posts
    this is the way I currently do it btw... it somewhat works, but it's not exactly what i have in mind:

    PHP Code:
    $relatedvideos "SELECT * FROM videos " $where;
    $query2 mysql_query($relatedvideos);
    $gevideos mysql_fetch_array($query2);
    $id $gevideos['id'];

    if(
    $id == ""// check to see if there's a video that contains the exact same tags
    {
    $relatedvideos "SELECT * FROM videos " $where2 "LIMIT 5";
    echo 
    $relatedvideos;
    $query mysql_query($relatedvideos);
    }

    else
    {
    $relatedvideos "SELECT * FROM videos " $where "LIMIT 5";
    $query mysql_query($relatedvideos);

    The problems with this code:

    If there is only one video with the exact same tags, it only shows that entry. Instead I would like it to show the same entry and 4 entries that are less related.

  • #3
    New Coder
    Join Date
    Oct 2011
    Posts
    24
    Thanks
    6
    Thanked 0 Times in 0 Posts
    Bump... anyone with any suggestions?


  •  

    Posting Permissions

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