help needed: related videos script

[ashsumo]

Hi,

I'm currently developing a video site, and am looking for suggestions for the best way to have a "Related Videos" feature for each video, displaying similar videos from the database based on tag etc.

my current database structure is as follows:

CREATE TABLE `video` (
  `video_id` int(11) NOT NULL auto_increment,
  `video_title` varchar(255) NOT NULL default '',
  `video_desc` text NOT NULL,
  `embed_code` text NOT NULL,
  `video_timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP,
  `video_views` int(11) NOT NULL default '0',
  `user_id` int(11) NOT NULL default '0',
  `active` int(11) NOT NULL default '0',
  `video_thumb` text NOT NULL,
  `ip_address` varchar(255) NOT NULL default '',
  `video_level` int(1) NOT NULL default '0',
  `num_comments` int(11) NOT NULL default '0',
  PRIMARY KEY  (`video_id`)
)

CREATE TABLE `video_tags` (
  `videotag_id` int(11) NOT NULL auto_increment,
  `video_id` int(11) NOT NULL default '0',
  `tag_name` varchar(255) NOT NULL default '0',
  `user_id` int(11) NOT NULL default '0',
  `videotag_timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP,
  PRIMARY KEY  (`videotag_id`)
)

so each video is assigned tags, each tag being placed in the video_tags table with video_id linking them to the video.

could you please advise which would be the best way to display say 6 related videos from the database? these could be random, most viewed results, latest uploaded results etc.

Thanks!

[atmorell]

I believe you could do it with only one table! You could create a category field in your video table - insert eg. cars, funny etc. If you want to display 10 orhter videos related to cars you do a query for videos where category = cars LIMIT 10. If you wan't to display the videos with the most views, you create a query that returns 10 videos: LIMIT 10, sort by video_views. Each time a user click a video you +1 to the int that is contained in video_views. Put a field uploaddate and so on. If you need to specify that e.g 20 specific videos is related to the current video, then you need to create a second table with relations:

video relations:
relation_id (primery key)
video_id (current video)
relation (the video id that is related.)

This way you could link all the video files you want. etc.

Best regards.
Asbjørn Morell.