#1
  1. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jul 2005
    Posts
    125
    Rep Power
    10

    Decent site search for blog posts


    Hey guys,

    I've recently started to try whip together a decent mysql query for a search page. The problem I have is I've been asked to include another tables data in query and I'm not exactly sure the best way to do it.

    What I have is the following tables.

    BlogPostsTable
    id, title, description

    BlogPostsCommentsTable
    id, blog_id, textWhat i need to do is run the query string through

    Its a one to many relationship with n amount of comments. BlogPostsTable.title, BlogPostsTable.description and BlogPostsCommentsTable.text and return * from the BlogPostsTable in order of highest rank.

    Is this possible to do in one query? Only thing I can think of is 2 querys. One to check the comments and get ids and then a seperate to test the others eg title, description.

    Biggest problem is ranking them after that. Or for search would you give priority to different items so if it find something in title that will come first, then description, then comments?

    I hope this makes sense.

    Thanks in advance
  2. #2
  3. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,262
    Rep Power
    4279
    how do you determine rank?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  4. #3
  5. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jul 2005
    Posts
    125
    Rep Power
    10
    Originally Posted by r937
    how do you determine rank?
    Well I was going to just see how mysql LIKE would rank them. Im guessing its on number of occurrences.

    I did find this post very useful (PHP) http://www.iamcal.com/publish/articles/php/search/

    Ive adapted it a little to work with title and description but I'm not sure what I should do in regards to adding comments into the search. I was sort of trying to achieve this in one sql search. But looks like ill need to do some filtering and ranking php side.

    Would you think that's the best option?

    Edit: So if I do a search using the method in that post the SQL will be

    SELECT * FROM BlogPostsTable WHERE title RLIKE '[[:<:]]word1[[:>:]]' OR description RLIKE '[[:<:]]word1[[:>:]]' OR title RLIKE '[[:<:]]word2[[:>:]]' OR description RLIKE '[[:<:]]word2[[:>:]]'

    That's with the query word1 word2

    I was thinking would it be better to do the calcs in SQL so add up all the occurrences of the word in the comments title and description and return the id, count?
    Last edited by doush.; May 1st, 2013 at 07:51 AM.
  6. #4
  7. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,262
    Rep Power
    4279
    Originally Posted by doush.
    I did find this post very useful (PHP) http://www.iamcal.com/publish/articles/php/search/
    sorry, i don't do php

    however, i did scan the article, and it looks like he's doing the ranking in php

    i think you'll get better answer to your questions in the php forum, so i've moved this thread over there
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  8. #5
  9. No Profile Picture
    Lost in code
    Devshed Supreme Being (6500+ posts)

    Join Date
    Dec 2004
    Posts
    8,316
    Rep Power
    7170
    Well I was going to just see how mysql LIKE would rank them. Im guessing its on number of occurrences.
    LIKE does not actually rank the results at all. MATCH_AGAINST is the only MySQL function I'm aware of that does relevancy ranking. Beyond that, you either have to do the ranking yourself in PHP or switch to a system that's designed more for relevancy searching, like SOLR. Doing the ranking manually in the MySQL query would be difficult and probably not worthwhile.

    The code in the article you linked to is outdated and not completely secure, you should use caution if you're copying and pasting any of it into your application.

    To search the comments, you could either use a union, a separate query or a separate search-cache table which contains the searchable text from both of your other tables.
    PHP FAQ

    Originally Posted by Spad
    Ah USB, the only rectangular connector where you have to make 3 attempts before you get it the right way around
  10. #6
  11. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jul 2005
    Posts
    125
    Rep Power
    10
    The code in the article you linked to is outdated and not completely secure, you should use caution if you're copying and pasting any of it into your application.
    What parts are insecure? Are you talking about how it makes the db calls? I have swapped it out for my own db class. What were the other things?

    To search the comments, you could either use a union, a separate query or a separate search-cache table which contains the searchable text from both of your other tables.
    Are you suggesting a union on all the comments with the same id? Or a union between the blog post table and the blogpostcomments table?

    The search-cache table idea is pretty cool. Would doing this be a bad idea? is it common? Just thinking it would mean double the data. But search may be greatly improved.

    Thanks for the tips
  12. #7
  13. No Profile Picture
    Lost in code
    Devshed Supreme Being (6500+ posts)

    Join Date
    Dec 2004
    Posts
    8,316
    Rep Power
    7170
    What parts are insecure? Are you talking about how it makes the db calls? I have swapped it out for my own db class. What were the other things?
    The DB calls were the only part I noticed.

    Are you suggesting a union on all the comments with the same id? Or a union between the blog post table and the blogpostcomments table?
    It would be a union of the blog post table and the blog post comments table. I'm not really sure what you mean by a union on comments with the same id.

    The search-cache table idea is pretty cool. Would doing this be a bad idea? is it common? Just thinking it would mean double the data. But search may be greatly improved.
    No it's not a bad idea. Yes it's common. Yes it would double the data. If you had any more tables to search than just those 2 then it would definitely be a better solution than using a union.
    PHP FAQ

    Originally Posted by Spad
    Ah USB, the only rectangular connector where you have to make 3 attempts before you get it the right way around

IMN logo majestic logo threadwatch logo seochat tools logo