0

I'm working on a sort of video archive, wfsu.org/dimensions and am trying to come up with a good query/algorithm combination for related videos using mysql and php. The database has title, keywords, description, category and another normalized 1:m table for producers. I have a simple algorithm but if anyone with any wit looks at it they'll see that it produces a very poor set of 'related' videos. Any ideas or help would be appreciated!!

Per request here's the simplistic algorithm that I'm using:

//if the segment isnt a generic dimensions use this query that makes sure they're in the same category
if($segType != 2)
{
    $query = "SELECT `title`, `description`, `air_date`, `keywords`, `post_id`, `img_filename`     
    FROM `archive_post` 
    WHERE `segment_type` = $segType 
    AND `post_id` != $id 
    AND NOW() > ADDTIME(`air_date`, '20:0:0') 
    ORDER BY `air_date` DESC LIMIT 5";
}
else //otherwise we want a query that checks to see if there are any similar keywords
{
    $query = "SELECT `title`, `description`, `air_date`, `keywords`, `post_id`, `img_filename` 
    FROM `archive_post` 
    WHERE (";
            $kwArray = preg_split("/[\s,-]+/", mysql_real_escape_string($keywords));

            foreach($kwArray as $kw)
            {
                $query .= "`keywords` LIKE '%$kw%' OR";
    }
    $query = substr($query, 0, -3);
    $query .=  ")
    AND `post_id` != $id 
    AND NOW() > ADDTIME(`air_date`, '20:0:0') 
    ORDER BY `air_date` DESC LIMIT 5";
}

$result = $dbConnection->runQuery($query);
if(mysql_num_rows($result) == 0) //if we can't find any 'related' videos what do?
{

}
else
{
    while($row = mysql_fetch_array($result))
    {
            $moreTitle = $row['title'];
    $moreID = $row['post_id'];
            $moreDescription = cleanDescription($row['description']);
            $moreDescription = substr($moreDescription, 0, 50).'...';
            $moreDate = strtotime( $row['air_date'], time() );  
            $moreDate = date( "F j, Y" , $moreDate );

            $relatedVideos .= "<li> <a href='viewvideo.php?num=$moreID'></a><h3>$moreTitle</h3>
                        <div class='featuredStory'><span class='featuredDate'>$moreDate</span> &sdot; $moreDescription</div></li>";
    }
}
Mike-Marshall
  • 76
  • 7
  • What code do you have so far? What have you tried? – tadman Sep 06 '13 at 19:23
  • I was looking for more of a generic concept or idea but I put in some code to help you get what I'm doing. – Mike-Marshall Sep 06 '13 at 19:52
  • Take a look at the concepts covered in the top answers for http://stackoverflow.com/questions/2323768/how-does-the-amazon-recommendation-feature-work. This is the sort of concept you'll be looking in it. – jdphenix Sep 06 '13 at 19:55
  • Great reading, I'm now fairly certain I'm in over my head! – Mike-Marshall Sep 06 '13 at 20:06
  • I may have found the answer to my own question via the mysql fulltext and natural language searching functionality. If I come up with a solution I'll be sure to post it up for anyone else thats interested. – Mike-Marshall Sep 06 '13 at 20:32
  • The `mysql_*` functions are **no longer maintained** and shouldn't be used in any new codebase. It is being phased out in favor of newer APIs. Instead you should use [**prepared statements**](https://www.youtube.com/watch?v=nLinqtCfhKY) with either [PDO](http://php.net/pdo) or [MySQLi](http://php.net/msqli). – tereško Sep 06 '13 at 22:41

1 Answers1

0

I'm going to answer my question so that anyone who's in a similar predicament, will have a bread crumb. I looked into mysql's fulltext capabilities. First I discovered it only was used for myISAM which after reading I found to be less than desirable in comparison to the current innodb.

Fulltext Search with InnoDB

I ended up doing a fulltext query on an index of the 3 key parts of what I wanted to search for, and it worked out perfectly for my needs!

Community
  • 1
  • 1
Mike-Marshall
  • 76
  • 7