SitePoint Sponsor

User Tag List

Results 1 to 3 of 3
  1. #1
    SitePoint Member
    Join Date
    Apr 2010
    Posts
    1
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Question please help me with this mysql query

    Hi, I'm new to Mysql database and I'm in the process of creating a CMS using PHP and mysql. I came across this challenge when I decided to display the related contents of this video like for example youtube when you click on a particular video you get related videos on the site similar to that I want to display related video using similar tags. Or another popular example would be a blog article and it displays similar related article on the page.



    In my table it has the following fields Id, title, description, tags...... and all other information about each video including specific tags.

    The tags are determined by the title of the video EX: the title of the video is "Today is a holiday in Canada 05/04/2010" and then I used the php code strtolower to lower case the title and then I used explode(" ",$tags); to separate the title into each word and using a loop I inserted those separated words into the tag field of the table. So now the tags field contains "today is a holiday in canada 05/04/2010 ".

    Now that you know about my database and the table

    On the video display page, I want to show the related videos on the side.

    I tired the following but it does not adequate my goal.

    $sql6 = "SELECT * FROM $tbl_name WHERE tags LIKE '%$tags%' ORDER BY id DESC";


    $result6=mysql_query($sql6);

    echo "<table border='1'>";
    echo "<tr> <th>Video</th></tr>";
    // keeps getting the next row until there are no more to get
    while($row5 = mysql_fetch_array( $result6 )) {
    // Print out the contents of each row into a table
    echo "<tr><td>";
    echo $row5['title'];
    echo "</td></tr>";
    }

    echo "</table>";
    Please write up a mysql query to suit my needs and please give your feedback to improve the table/website or any other suggestion that may help me.

    Thank you

  2. #2
    SitePoint Wizard guelphdad's Avatar
    Join Date
    Oct 2003
    Location
    St. Catharines, ON Canada
    Posts
    1,708
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    look up database normalization, storage of multiple values in a single field violates first normal form.

  3. #3
    SitePoint Wizard
    Join Date
    Jul 2003
    Location
    Kent
    Posts
    1,921
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    And even if you realise how to normalise it, do you think tags like "is", "a" and "the", will be of much use???

    And even if normalisation didn't exist/matter, the use of explode is totally redundant in your version, as all you have done is put the title into the tag field in lowercase.

    And if you did a search on the title field using LIKE "&#37;canada%" it would find Canada anyway.

    So, overall, a bit of a rethink is needed perhaps.


Bookmarks

Posting Permissions

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