SitePoint Sponsor

User Tag List

Results 1 to 14 of 14
  1. #1
    Non-Member
    Join Date
    Feb 2005
    Posts
    737
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Related stories?

    Hi Guys,

    Wondering if I could ask for some advise.

    On my CMS I output story records on my live site ie:

    http://www.sloughactivate.co.uknews/...hp?story_id=12
    http://www.sloughactivate.co.uk/news...hp?story_id=13
    etc

    Now with each one of these records I want to create a related story feature which shows links to other stories similar to this in my database.

    To do this I currently have a field in my CMS called related_term where when Iím adding a story I input something like joe bloggs

    I then on my live site have written a statement which asks the system to look through all the records in my CMS and output the headline as a link where related_term is like (in this case Joe Bloggs).

    PHP Code:
    <? 
    $SQL 
    "select * from cms_stories where headline like '%$related_term%' or body_text like '%$related_term%'"
    $result mysql_query($SQL) OR die(mysql_error()); 
    $row mysql_fetch_array($resultMYSQL_ASSOC); 
    echo 
    "<a href='story.php?story_id={$row[story_id]}'>{$row[headline]}</a>"
    ?>
    Currently it could also provide a link to the current story as well if Joe Bloggs appears in the body text which would look silly. How can I get around this?

    And also anyone think of a better way to do related stories?

    Many Thanks

    Chris
    Last edited by 7724; Jul 18, 2006 at 06:30.

  2. #2
    SitePoint Wizard silver trophy Jelena's Avatar
    Join Date
    Feb 2005
    Location
    Universum, 3rd Corner
    Posts
    3,000
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    I would create one more table in database which will contain pairs related story IDs
    -- Jelena --

  3. #3
    Follow Me On Twitter: @djg gold trophysilver trophybronze trophy Dan Grossman's Avatar
    Join Date
    Aug 2000
    Location
    Philadephia, PA
    Posts
    20,578
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by 7724
    Currently it could also provide a link to the current story as well if Joe Bloggs appears in the body text which would look silly. How can I get around this?
    Add another condition to the WHERE clause of your query.. AND ID != $story_id

  4. #4
    Evil Genius MilchstrabeStern's Avatar
    Join Date
    Nov 2003
    Location
    Arizona
    Posts
    1,131
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I wouldn't make a new table that matches id's of similar storys. This would be complicated and maybe a little confusing. Would you have a seperate row for each relation? You would aslo need code to build these relations. In the end, it might make your queries faster, but I don't think it is most practicle.

    You could aslo do multiple keywords, not just one.
    ]

  5. #5
    Non-Member
    Join Date
    Feb 2005
    Posts
    737
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi Guys,

    Thanks for the ideas. AND ID != $story_id would work, but I have four textbox entries to find related stories ie:

    related_term
    related_term_two
    related_term_three
    related_term_four

    So if if I entered the term Joe Bloggs in related_term and Joe in related_term_two (for example) it would return the same record in related_term and related_term_one (see what I mean?) Unless I could add some other condition. but not like related_term != $related_term_one OR related_term != $related_term_two is this possible/suitable?

    With multiple keywords i'm keen to know more, could someone please elaborate on how this works please?

    Many Thanks

  6. #6
    Follow Me On Twitter: @djg gold trophysilver trophybronze trophy Dan Grossman's Avatar
    Join Date
    Aug 2000
    Location
    Philadephia, PA
    Posts
    20,578
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    You're filtering the IDs of the story rows returned so that they're not equal to the current story's ID. It doesn't matter how many "related term" columns you've made up. They're not related.

  7. #7
    Non-Member
    Join Date
    Feb 2005
    Posts
    737
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi Dan,

    So if this is my live page:

    http://sloughactivate.co.uk/news/news.php?story_id=12

    In my first related_term, where in my CMS for this story_id I have put Joe Bloggs and set story_id != $story_id it returns the link:

    http://sloughactivate.co.uk/news/news.php?story_id=13

    Fine, but...

    In my second related_term_one, where in my CMS I have put Joe and set story_id != $story_id but surely it could also return the link:

    http://www.sloughactivate.co.uk/news...hp?story_id=13

    So I am getting duplicate data?

    See what I mean? Or am I not understanding or explaining myself well?

    Many Thanks - appreciate your advise

    Chris
    Last edited by 7724; Jul 18, 2006 at 06:30.

  8. #8
    Follow Me On Twitter: @djg gold trophysilver trophybronze trophy Dan Grossman's Avatar
    Join Date
    Aug 2000
    Location
    Philadephia, PA
    Posts
    20,578
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Am I misunderstanding? Was the question not that you didn't want to display a link to the current article when listing related articles to the one being viewed?

    You're at http://www.mysite.com/news/news.php?story_id=12. $story_id is equal to 12. Your query looks for rows where relater_term* is equal to "Joe" and gets back rows 12, 13, 14. But you put "AND story_id != $story_id" in the query, which becomes "AND story_id != 12" before it's sent to MySQL, so it only actually returns rows 13 and 14.

    It doesn't matter what matched or didn't match whatever other parts of the query. "AND story_id != 12" tells MySQL DON'T RETURN STORY NUMBER 12. It will obey! Is this not exactly what you asked for?

  9. #9
    Non-Member
    Join Date
    Feb 2005
    Posts
    737
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi Dan,

    You are correct It is what I asked for (at the beginning)

    But that was with one related_term as an example in the first post, what I'm getting at now is I have four related term columns.

    Ie in related_term I enter Joe Bloggs and it does what you say:

    You're at http://www.mysite.com/news/news.php?story_id=12. $story_id is equal to 12. Your query looks for rows where relater_term* is equal to "Joe" and gets back rows 12, 13, 14. But you put "AND story_id != $story_id" in the query, which becomes "AND story_id != 12" before it's sent to MySQL, so it only actually returns rows 13 and 14.
    Now I do it again but this time entering my keyword in related_term_one where I enter the word Mike Jones

    Now this also happens to return results 13 and 14.

    So on my live site where I have box saying related stories I have rows 13 and 14 returned twice?

    See what I mean, or am I confusing you and the whole of Sitepoint?

    Thanks

    Chris

  10. #10
    Follow Me On Twitter: @djg gold trophysilver trophybronze trophy Dan Grossman's Avatar
    Join Date
    Aug 2000
    Location
    Philadephia, PA
    Posts
    20,578
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    SELECT DISTINCT story_id FROM table WHERE....

  11. #11
    Non-Member
    Join Date
    Feb 2005
    Posts
    737
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi Dan,

    Last one please. So If I have three SQL queries on my live site page:

    http://sloughactivate.co.uk/news/news.php?story_id=12

    PHP Code:
    <? 
    $SQL 
    "select * from cms_stories where headline like '%$related_term%' or body_text like '%$related_term%'"
    $result mysql_query($SQL) OR die(mysql_error()); 
    $row mysql_fetch_array($resultMYSQL_ASSOC); 
    echo 
    "<a href='story.php?story_id={$row[story_id]}'>{$row[headline]}</a>"
    ?>
    PHP Code:
    <? 
    $SQL 
    "select * from cms_stories where headline like '%$related_term_one%' or body_text like '%$related_term_one%'"
    $result mysql_query($SQL) OR die(mysql_error()); 
    $row mysql_fetch_array($resultMYSQL_ASSOC); 
    echo 
    "<a href='story.php?story_id={$row[story_id]}'>{$row[headline]}</a>"
    ?>
    PHP Code:
    <? 
    $SQL 
    "select * from cms_stories where headline like '%$related_term_two%' or body_text like '%$related_term_two%'"
    $result mysql_query($SQL) OR die(mysql_error()); 
    $row mysql_fetch_array($resultMYSQL_ASSOC); 
    echo 
    "<a href='story.php?story_id={$row[story_id]}'>{$row[headline]}</a>"
    ?>
    If I use DISTINCT then It will not return ANY of the same id's - nomatter that they are part of different queries?

    Many Thanks
    Last edited by 7724; Jul 18, 2006 at 06:30.

  12. #12
    Follow Me On Twitter: @djg gold trophysilver trophybronze trophy Dan Grossman's Avatar
    Join Date
    Aug 2000
    Location
    Philadephia, PA
    Posts
    20,578
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    You should have at most two queries on this page.

    SELECT DISTINCT story_id FROM cms_stories WHERE (headline LIKE '%related_term_one%' OR headline LIKE '%related_term_two%' OR body_text LIKE '%related_term_one%' OR body_text LIKE '%related_term_two%') AND story_id != $story_id

    Also, why do you keep linking us to "mysite.com" and calling it "live"?

  13. #13
    Non-Member
    Join Date
    Feb 2005
    Posts
    737
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks Dan,

    When I write mysite.com it's just being used as an example url.

    I generally call a CMS back-end which users can't see and my actual website 'live' that the user can see.

    Just my termonology!!

  14. #14
    is_empty(2); foofoonet's Avatar
    Join Date
    Mar 2006
    Posts
    1,000
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    http://www.sitepoint.com/forums/show...highlight=hoof

    A related thread concerning using keywords, links to yet another too.
    Upgrading to Mysql 5? Auto-increment fields now strict
    use NULL
    Or zero or leave the field name out completely.


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
  •