Results 1 to 22 of 22
  1. #1
    Newbie
    Join Date
    April 24th, 2005
    Posts
    14
    PHP & MySQL Search Question
    Hey Guys-

    So... I've set up a search form on my website which uses php and mysql. It works great but if someone searches for example "sony camcorder" it will only find products with the words "sony camcorder" adjacent to eachother in the title... how could i set it up so it breaks it up and brings results that aren't exact phrase matches (ex. Sony TRV25 Camcorder) ? Here's the code i'm using...

    $srch="%".$search."%";
    $query = "SELECT * FROM `products` WHERE `Product Name` LIKE '$srch'";

    $srch being the search term in this case...

    Thanks,
    Kyle

  2. #2
    Great Open Source
    Kyle, not sure how much you are into using open source apps but there is a great "site search engine that i have integrated into several sites. Check out:
    http://www.tsep.info/index.php

    Some Great Features.

    Have fun

    -Monk
    Earn 15% Commision: 100% All Natural Supplements From The Amazon Rainforest
    Lifexpand, Inc Boca Raton, FL Phone: (561) 487-3650 www.lifexpand.com

  3. #3
    Member
    Join Date
    January 17th, 2005
    Location
    Brooklyn
    Posts
    118
    Smile I hope this helps
    Quote Originally Posted by fish4017
    Hey Guys-

    So... I've set up a search form on my website which uses php and mysql. It works great but if someone searches for example "sony camcorder" it will only find products with the words "sony camcorder" adjacent to eachother in the title... how could i set it up so it breaks it up and brings results that aren't exact phrase matches (ex. Sony TRV25 Camcorder) ? Here's the code i'm using...

    $srch="%".$search."%";
    $query = "SELECT * FROM `products` WHERE `Product Name` LIKE '$srch'";

    $srch being the search term in this case...

    Thanks,
    Kyle
    I am in no shape nor form a php/mysql expert, but this is what I do to get around the problem you're having.

    $field = 'fieldname LIKE ';
    $srch = '';

    $num = count( $array1 );
    $i = 0;

    foreach ( $array1 as $item ) {
    $srch .= $field;
    $srch .= "'%$srch%'";
    $i++;
    if ( $i == $num ) {
    $srch .= " LIMIT 45 ";
    } else {
    $srch .= " AND ";
    }

    Each word the user inputs is separated in the array and then I construct it so that a mysql statement is built that will search for each term separately.

    So, if the user inputs "corner cabinet" as their search term, $srch ends up looking like this: fieldname LIKE '%corner%' AND fieldname LIKE '%cabinet%' LIMIT 45

    No doubt there is a much better way to do this, but this works quite well.

    Webdiva

  4. #4
    ABW Ambassador
    Join Date
    January 18th, 2005
    Posts
    4,423
    There was just another post on this subject. Like's are terrible for performance reasons and should always be avoided. Check the fulltext search information on mysql.

    http://dev.mysql.com/doc/mysql/en/fulltext-search.html

  5. #5
    Affiliate Marketer Rogi's Avatar
    Join Date
    January 18th, 2005
    Location
    Melbourne
    Posts
    415
    fulltext search on mysql is the way to go. It can do everything you want. What version of MySQL are you running though? I think only the newer one (is it 4?) that supports boolean search also (like: "sony or camcorder" etc.
    But fulltext search does what you want it to do. Here's some info:
    http://www.phpfreaks.com/tutorials/129/0.php

    Or alternatively you could do your own search and algorithm etc... split the words up, etc etc.

    Also, there's some good posts on webmasterworld in one thread ( i wont post the url) but you can google for "can a php search function find near-misses" (with the quotes)
    to find it.

  6. #6
    Moderator BurgerBoy's Avatar
    Join Date
    January 18th, 2005
    Location
    Kentucky
    Posts
    9,618
    Talking
    Quote Originally Posted by Monk_LifeXpand
    Kyle, not sure how much you are into using open source apps but there is a great "site search engine that i have integrated into several sites. Check out:
    http://www.tsep.info/index.php

    Some Great Features.

    Have fun

    -Monk
    I use the Fluid Dynamics search engine on my web sites. It is free and will do anything that you want it to do.

    Not only will it search the site that you have it installed on - but if you have more than one site - you can tell it to index all of your sites and it will give a combined search results from all of your sites on each one of your sites.

    It will install itself - all you do is give it the url of where you want it installed.

    The Admin page will let you control what it gives in search results without you having to know any programming at all.

    It is located at http://www.xav.com/ if you would like to check it out.

    Any time you change your site just tell it to rebuild the index and in about 30 minutes it is current - even if you have thousands of pages on your site.

    Vietnam Veteran 1966-1970 USASA
    ABW Forum Rules - Advertise At ABW

  7. #7
    Wow, that app is pretty cool. I like the self install. The rebuilding of the site index is a must, especially wit hthe amount of fluid content needed t okeep aflot in the serps!!

    -Monk
    Earn 15% Commision: 100% All Natural Supplements From The Amazon Rainforest
    Lifexpand, Inc Boca Raton, FL Phone: (561) 487-3650 www.lifexpand.com

  8. #8
    Member
    Join Date
    January 17th, 2005
    Location
    Brooklyn
    Posts
    118
    Great info! Thanks!

    I'm now off to rebuild my search function. See ya in three or four days.

  9. #9
    Newbie
    Join Date
    April 24th, 2005
    Posts
    14
    Thanks guys
    Thanks for all the input i'm gonna give a few things a try and i'll let you know how it goes!

    -Kyle

  10. #10
    lurk
    Join Date
    March 25th, 2005
    Posts
    355
    It may be wise to drop the SQL search and build a search that works of an index. It is less server intensive and much faster.

  11. #11
    ABW Ambassador
    Join Date
    January 18th, 2005
    Posts
    4,423
    Quote Originally Posted by jrod805
    It may be wise to drop the SQL search and build a search that works of an index. It is less server intensive and much faster.
    Fulltext seach does work on an index, not sure what you are saying.

    While i understand using a 3rd party app because you technically can't handle the coding- think of what you lose.

    If you had your own you could:

    Capture what people are searching on to see what people are really looking for.

    Check the rerfer of the search, where are people coming from then searching - so where did they get the wrong idea about your page, what did they think they were going to find.

    Learn how people are looking for items to see if you should change your categories.

    Create "mini-stores" inside your site to promote commonly searched on items.


    Etc, etc, etc.

    Chet

  12. #12
    Moderator BurgerBoy's Avatar
    Join Date
    January 18th, 2005
    Location
    Kentucky
    Posts
    9,618
    Talking
    Quote Originally Posted by chetf
    Fulltext seach does work on an index, not sure what you are saying.

    While i understand using a 3rd party app because you technically can't handle the coding- think of what you lose.

    If you had your own you could:

    Capture what people are searching on to see what people are really looking for.

    Check the rerfer of the search, where are people coming from then searching - so where did they get the wrong idea about your page, what did they think they were going to find.

    Learn how people are looking for items to see if you should change your categories.

    Create "mini-stores" inside your site to promote commonly searched on items.


    Etc, etc, etc.

    Chet
    The Fluid Dynamics search engine has an Admin panel that tracks all search phrases and key words searched for built right into it.

    It will show all of the searchs by % - will build you a graph.

    It will do all of the thing you are saying.

    It will also Create "mini-stores" inside your site to promote commonly searched on items.

    You can create realms and tell it what realm to return for your mini store - and all of this without having to know about programming.

    We're not all programmers remember - but we can do the same things you can do with this search engine without having to be a programmer.

    Vietnam Veteran 1966-1970 USASA
    ABW Forum Rules - Advertise At ABW

  13. #13
    ABW Ambassador
    Join Date
    January 18th, 2005
    Posts
    4,423
    While I am glad it works for you, it can hardly do many of the things I mention. And in fact the owners of the product suggest it is not a good tool for searching a database or database-driven content,

    http://demo.nickname.net/latest/en/s...2=ManageRealms

    Chet

  14. #14
    Internet Cowboy
    Join Date
    January 18th, 2005
    Posts
    4,662
    Fluid Dynamics is good. I have used it before and still do on some sites. MySQL and php leave it in the dust in every way, but it works for those of us sharpening our coding skills


  15. #15
    Member
    Join Date
    January 17th, 2005
    Location
    Brooklyn
    Posts
    118
    I'm happy to say I'm almost done rebuilding my site search using fulltext in MySql. Now I just have to figure out how to make the boolean search default to "this word must be present in every row returned". Compared to the specialized LIKE searches that I created, the fulltext search returns far too many irrelevant rows without the + signs, and I'm sure most of my site visitors don't know boolean from lean bull.

  16. #16
    Newbie
    Join Date
    April 24th, 2005
    Posts
    14
    Now I just have to figure out how to make the boolean search default to "this word must be present in every row returned". Compared to the specialized LIKE searches that I created, the fulltext search returns far too many irrelevant rows without the + signs, and I'm sure most of my site visitors don't know boolean from lean bull.
    Thats exactly my problem also. i've switch to fulltext but the search results have to many irrelavent products. if anyone figures out how to make the boolean search default to "this word must be present in every row returned" please post a reply!

    Thanx

  17. #17
    ABW Ambassador
    Join Date
    January 18th, 2005
    Posts
    4,423
    Post your sql select statement.

  18. #18
    Member
    Join Date
    June 23rd, 2005
    Posts
    77
    Quote Originally Posted by fish4017
    $srch="%".$search."%";
    $query = "SELECT * FROM `products` WHERE `Product Name` LIKE '$srch'";
    Take that code and change it to:


    PHP Code:
    $sql "SELECT `products` WHERE `Product Name`";

    //remove trailing whitespaces
    $search trim($search);

    //see if the string contains more than one word
    if(strstr($search" "))
    {
            
    //seperate the string so that each word can be searched by itself
            //@ prevents the function from returning an error if the string contains no spaces or is empty
            
    $search = @explode(" "$search);

            
    //loop through each word to ensure it's added to the query properly
            
    foreach($search as $key => $value)
                    
    $sql .= " LIKE '%".$value."%' OR ";

            
    //remove the last OR on the end to prevent errors
            
    $sql substr(strlen($sql), (strlen($sql)-4));
    }
    else
    {
            
    $sql .= " LIKE '%".$search."%'";
    }

    mysql_query($sql) or die(mysql_error()); 
    That will take the search string and search the database for each word.
    Use all the above code - it will fix your issue ^_^

    If you provide me the rest of your query, I'll fix it so you can still search for "sony camcorder" and have it search for those words adjacent to each other.

  19. #19
    ABW Ambassador
    Join Date
    January 18th, 2005
    Posts
    4,423
    Fish, I was asking if you had changed it to use with full text search.

    I would suggest not doing what webnet suggests as it defeats the purpose of the fulltext search and would be very slow on a large table.

    Chet

  20. #20
    Internet Cowboy
    Join Date
    January 18th, 2005
    Posts
    4,662
    What is the best way to search all text in the db?


  21. #21
    Member
    Join Date
    June 23rd, 2005
    Posts
    77
    Full-text searches are supported for MyISAM tables only.

    PHP Code:
    SELECT `productsFROM `tableWHERE MATCH (Product NameAGAINST ($search); 
    If you read the fulltext documentation on mysql.com it's not that great of an option and is heavy on the server.

    http://dev.mysql.com/doc/mysql/en/fulltext-search.html

  22. #22
    ABW Ambassador
    Join Date
    January 18th, 2005
    Posts
    4,423
    Webnet, on a large table? Especially veruses likes? Yes, it is a great option. Results returned in ms, versus seconds.

    And for boolean mode to work, you need to call it in boolean mode, and you then have other options, which is why i asked him to post the example.

    An example of what i have done previously.

    SELECT id, title, adcode, MATCH (urldesc,title,keywords) AGAINST ('$searchtext' in boolean mode ) AS score FROM mytable WHERE active!='0000-00-00 00:00:00' and MATCH (urldesc,title,keywords) AGAINST ('$searchtext' in boolean mode) HAVING score > 0.2 ORDER BY score DESC limit 100" ;

+ Reply to Thread

Similar Threads

  1. I think I found the best "Free" HTML Editor on the planet- PHP & MySQL Tags; Plugins
    By HumbleFish in forum Programming / Datafeeds / Tools
    Replies: 17
    Last Post: April 15th, 2010, 01:43 AM
  2. Search engine Fulltext with PHP & Mysql
    By clutax in forum Programming / Datafeeds / Tools
    Replies: 1
    Last Post: June 1st, 2005, 03:27 PM
  3. Php Mysql - Repeating..??
    By AddHandler in forum Programming / Datafeeds / Tools
    Replies: 13
    Last Post: May 7th, 2005, 02:25 PM
  4. PHP/MySQL syntax question?
    By AcuraImport in forum Programming / Datafeeds / Tools
    Replies: 8
    Last Post: April 18th, 2004, 05:25 PM
  5. PHP & mySQL but....
    By perfectG in forum Programming / Datafeeds / Tools
    Replies: 1
    Last Post: November 6th, 2002, 05:58 AM

Posting Permissions

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