Results 1 to 15 of 15

Thread: Search feature for essay site (and advice from Chris)...

  1. #1
    Junior Registered
    Join Date
    Apr 2004
    Posts
    4

    Exclamation Search feature for essay site (and advice from Chris)...

    I'm having a little problem...

    I have a couple of thousand essays in a mysql database. How can I allow users to efficiently search for them? Chris, would you mind sharing any advice on how you did it on your literature site?

    PS. I want users to be able to search by the content of the essay (not just the name of the essay) -- i.e. search within the essay.

    Thanks in advance.

    -The Little Guy

  2. #2
    Administrator Chris's Avatar
    Join Date
    Feb 2003
    Location
    East Lansing, MI USA
    Posts
    7,055
    The simplest way to do this is a MySQL query like this

    select * from essays where essay_content like '%$searchterm%'

    % is the SQL wildcard.

    This will return essays where the exact (and I do mean exact) search phrase is found.

    For more complex searches you can use a FULLTEXT index. That is what forums like this one use. I don't use a FULLTEXT index on my literature site as people are often looking for exact phrases. On an essay site where people are looking for general topics, I would use one.

    http://dev.mysql.com/doc/mysql/en/Fulltext_Search.html
    Chris Beasley - My Guide to Building a Successful Website[size=1]
    Content Sites: ABCDFGHIJKLMNOP|Forums: ABCD EF|Ecommerce: Swords Knives

  3. #3
    Administrator Chris's Avatar
    Join Date
    Feb 2003
    Location
    East Lansing, MI USA
    Posts
    7,055
    Chris Beasley - My Guide to Building a Successful Website[size=1]
    Content Sites: ABCDFGHIJKLMNOP|Forums: ABCD EF|Ecommerce: Swords Knives

  4. #4
    Junior Registered
    Join Date
    Apr 2004
    Posts
    4
    Chris,

    Do you break up the search term and search for each individual one? ie LIKE '%keyword1%' OR '%keyword2%'?

    Thanks for your advice

  5. #5
    Registered Member incka's Avatar
    Join Date
    Aug 2003
    Location
    Wakefield, UK, EU
    Posts
    3,801
    I think I will use this, Mike will too I'm sure...

  6. #6
    Senior Member chromate's Avatar
    Join Date
    Aug 2003
    Location
    UK
    Posts
    2,348
    Originally posted by LittleGuy
    Do you break up the search term and search for each individual one? ie LIKE '%keyword1%' OR '%keyword2%'?
    It depends if you want an exact match for a search term or not. breaking it up will obviously match any word, where as if it's all one string then it will only match if it finds the exact phrase.

  7. #7
    Junior Registered
    Join Date
    Oct 2004
    Posts
    2
    hi,
    The search like
    select * from table name where column like "%search-string%"
    will result the one which the word is found anywhere in the string.

    But is it possible to check in a string for each word for exact match or even starting with?

    I mean
    -> " This string should return if I query_for the term querystarting with" ;
    select * from tablename where column like "query%";

    -> "This string should return when i query for this term when i match for this anywhere";
    select * from tablename where column like "%this%";

    ->"This string should return for exact match for a word";
    select * from tablename where column like "exact"

  8. #8
    Senior Member AndyH's Avatar
    Join Date
    May 2004
    Location
    Australia
    Posts
    553
    I suggest giving an option to search just the essay titles and also the essay bodies. If the site is, or gets very popular, the searches could be very process consuming for the server.

    Or you could just make the change when the time comes.
    New website released. ya rly!

  9. #9
    Administrator Chris's Avatar
    Join Date
    Feb 2003
    Location
    East Lansing, MI USA
    Posts
    7,055
    Yes Rani.

    For instance to get everyone who has a name that starts with A

    select * from people where name like 'A%'
    Chris Beasley - My Guide to Building a Successful Website[size=1]
    Content Sites: ABCDFGHIJKLMNOP|Forums: ABCD EF|Ecommerce: Swords Knives

  10. #10
    i tried adding fulltext like that article said... after about 3 minutes i got this message:

    MySQL said:

    #3 - Error writing file '/tmp/STPALyTf' (Errcode: 28)

  11. #11
    Junior Registered
    Join Date
    Oct 2004
    Posts
    2
    Quote Originally Posted by Chris
    Yes Rani.

    For instance to get everyone who has a name that starts with A

    select * from people where name like 'A%'
    This works fine if the word start with the A.
    For eg:
    1) Andrew_smith was at the door.
    2) I think Andrew_smith is at the door.
    3) Andrew is right at the door
    if we query like
    select * from tablename where column like "Andrew%"; the first and third will return and the second will not.
    what one can do for such thing.

    regards,
    rani

  12. #12
    Senior Member AndyH's Avatar
    Join Date
    May 2004
    Location
    Australia
    Posts
    553
    Code:
    SELECT * FROM `names` WHERE `name` LIKE '%A%';
    New website released. ya rly!

  13. #13
    4x4
    Join Date
    Oct 2004
    Posts
    1,043
    Is this

    Code:
    SELECT * FROM `names` WHERE `name` LIKE '%A%';
    Just as fast as using a new column for only the first letter of their name (or last name).. you just grab the first char. when you add the names to the database and write it to a different column. Then search with

    Code:
    SELECT * FROM names WHERE name= "A"
    Just curious.

  14. #14
    Senior Member AndyH's Avatar
    Join Date
    May 2004
    Location
    Australia
    Posts
    553
    Well, if you want to make it as fast as possible then make another column that holds a value from 1-26 (TINYINT(2) UNSIGNED) and index it. Then use:

    Code:
    SELECT * FROM names WHERE first_letter= "7"
    If the first letter is a G.
    New website released. ya rly!

  15. #15
    4x4
    Join Date
    Oct 2004
    Posts
    1,043
    Quote Originally Posted by AndyH
    Well, if you want to make it as fast as possible then make another column that holds a value from 1-26 (TINYINT(2) UNSIGNED) and index it. Then use:

    Code:
    SELECT * FROM names WHERE first_letter= "7"
    If the first letter is a G.
    Well thought idea

Similar Threads

  1. Advice for further promotion of niche sports site.
    By Dan Morgan in forum General Promotion
    Replies: 15
    Last Post: 03-30-2004, 05:18 AM

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
  •