...

View Full Version : Combine multiple where queries?



Krtoffel
11-18-2011, 07:08 PM
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:



$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!

Krtoffel
11-18-2011, 07:21 PM
this is the way I currently do it btw... it somewhat works, but it's not exactly what i have in mind:



$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.

Krtoffel
11-19-2011, 12:59 PM
Bump... anyone with any suggestions?



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum