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
    Regular Coder GSimpson's Avatar
    Join Date
    Aug 2006
    Location
    New Zealand
    Posts
    268
    Thanks
    9
    Thanked 0 Times in 0 Posts

    Question Related database entries, selecting entry only once?

    Ok so basically, I'm trying to create a "tagging" system for my database entries, so that I can find related media. So far I have a database table setup like this:

    Code:
    CREATE TABLE media (
      id int(11) NOT NULL AUTO_INCREMENT,
      title varchar(255) NOT NULL,
      `group` int(2) NOT NULL,
      `type` int(2) NOT NULL,
      `source` varchar(255) NOT NULL,
      `user` int(11) NOT NULL,
      `timestamp` varchar(255) NOT NULL,
      thumbnail varchar(255) NOT NULL,
      tags varchar(255) NOT NULL,
      PRIMARY KEY (id)
    ) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=latin1;
    I've been inserting tags in with my media entries, in the format of
    "tag1, tag2, tag3, tag4"

    So the first thing I did was get the variable with the tags and explode it into an array. Then "foreach" tag, I ran a mysql query to get other entires containing the tag set as $current.

    PHP Code:
    <?php
        $related 
    explode(", ",$media["tags"]);

        foreach(
    $related as $current) {
        
    $get_related mysql_query("SELECT * FROM `media` WHERE id != '$_GET[id]' AND tags LIKE '%" $current "%'");
        while(
    $found mysql_fetch_array($get_related)) {
            echo 
    $found["title"] . "<br/>";
        } }

    ?>
    The problem I have is here. When I use this structure, it finds every video with the first tag in it, and then repeats for each tag. So if my first entry has 3 tags similar to entry 2, entry 2 would show 3 times in my list.

    I don't know how to go about this, (This is why I'm asking) but I want to have it output each video found, only once, and order them by amount of tags similar.

    This should theoretically achieve a "Related media" list. I had the idea to use a multi-dimensional array, but I couldn't get it right.

    Does anyone have any idea on how I can finish this script, I googled a couple of similar scripts, but haven't found one using a "related entries" module on their pages.

    Thanks in advance,
    GSimpson.
    Last edited by GSimpson; 08-22-2009 at 09:18 AM.
    The internet is my Sandbox, and notepad is my Spade n' Bucket.

  • #2
    UE Antagonizer Fumigator's Avatar
    Join Date
    Dec 2005
    Location
    Utah, USA, Northwestern hemisphere, Earth, Solar System, Milky Way Galaxy, Alpha Quadrant
    Posts
    7,691
    Thanks
    42
    Thanked 637 Times in 625 Posts
    Normalize your design; store only one tag on a row, in a new separate table, that relates back to your first table. You'll then be able to work with the data as you describe.

  • #3
    Regular Coder GSimpson's Avatar
    Join Date
    Aug 2006
    Location
    New Zealand
    Posts
    268
    Thanks
    9
    Thanked 0 Times in 0 Posts

    Smile

    Ahh, that makes a bit more sense to do it that way. Thanks.

    One newb question about Mysql, say we were doing this on a project the size of YouTube, Metacafe, Photobucket, Last.fm - would there be any problem with the table handling that many tags?

    Once again, Thank You.
    The internet is my Sandbox, and notepad is my Spade n' Bucket.


  •  

    Posting Permissions

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