SitePoint Sponsor

User Tag List

Results 1 to 6 of 6
  1. #1
    SitePoint Addict aniltc's Avatar
    Join Date
    Nov 2006
    Location
    INDIA
    Posts
    399
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    efficent serach technique using php and mysql

    Hi all

    I need to implement a keyword search in my page using php and mysql.

    This is a dynamic search fieldwhere the users can enter anything they like, examples are company names or product types, such as paint, refrigerators,
    concrete forms, etc.
    I want to display all the information related to that particular keyword.

    I have created a product table and around 8 tables associated with (related
    with products by applying foreign key) this.

    How can I apply keyword search in an efficient way. Can I use 'Like' operator in mysql or 'FULLTEXT' method.I am not getting an idea how to implement .please give me a solution.

  2. #2
    SitePoint Wizard silver trophybronze trophy Cups's Avatar
    Join Date
    Oct 2006
    Location
    France, deep rural.
    Posts
    6,869
    Mentioned
    17 Post(s)
    Tagged
    1 Thread(s)
    It largely depends on the type of datafields you are searching amongst.

    Is the content like this:

    brands
    ======
    Electrolux
    Whirlpool
    Indesit

    OR is it like this:

    " ... however I don't think the Electrolux toaster does what it claims, unlike their freezer with which I was particularly impressed, although the toast emerged white and very cold."

    (bets with self: you going to say 'both' )

  3. #3
    SitePoint Addict aniltc's Avatar
    Join Date
    Nov 2006
    Location
    INDIA
    Posts
    399
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    thanks for your reply

    Contents can be both.It may be a sentence or just a word in the database

  4. #4
    SitePoint Wizard silver trophybronze trophy Cups's Avatar
    Join Date
    Oct 2006
    Location
    France, deep rural.
    Posts
    6,869
    Mentioned
    17 Post(s)
    Tagged
    1 Thread(s)
    Sigh, well, for the first type of search you'd use LIKE and for the second you'd use FULLTEXT depending on what you actually want to return.

    You may well end up finally doing what I term a "layered search" where you try and present the user with an intelligent guess at what you think they want.

    e.g. if they only enter a single word, then you could divine that they want a product so do that search first.

    If that finds NO results (or just n results), do the FULLTEXT search.

    OR

    Just do both:

    "We found x results in products <list> but you might be interested in these too ...."

    You have many options.

    The final effectiveness of you search, and future of your site, will be influenced by these decisions and will to a greater degree will now become intertwined with you GUI-building abilities.

    You can make a fantastic 'layered search', but if nobody can understand it, or see what their next options are, you are dead.

    The effectiveness of a complex 'layered search' is something you have to test on real people, but if it works properly, it can be immensely powerful.

    Don't forget about using Google/Bing/Yahoo 's search APIs either.

  5. #5
    SitePoint Addict aniltc's Avatar
    Join Date
    Nov 2006
    Location
    INDIA
    Posts
    399
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks for your comments

    My database contains both type of data.I mean a single keyword and a sentence.I will have to search around 8 associated tables where these words are existing.So if I use 'Like' operators in my query,Do you think whether it will produce all the matching result and do you think is this a good method ?
    I think the user will always use a single keyword.but in my database I will have both keyword and a sentence.How can I show a match from the sentence.



    Or should i go head with fulltext for keyword.Still I am on confusion.

    please help me.

  6. #6
    SitePoint Wizard silver trophybronze trophy Cups's Avatar
    Join Date
    Oct 2006
    Location
    France, deep rural.
    Posts
    6,869
    Mentioned
    17 Post(s)
    Tagged
    1 Thread(s)
    If you really want to nail this issue, why don't you show us a chopped down schema of say, 2, or even 3 of your tables, change the values, obfuscate anything that identifies the owner, and chop out the stuff we don't need to scroll through (dates etc).

    Show a typical row output from each table, and lets take it from there.


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
  •