Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 8 of 8
  1. #1
    New Coder
    Join Date
    Dec 2003
    Location
    Kelowna, British Columbia
    Posts
    60
    Thanks
    0
    Thanked 0 Times in 0 Posts

    keyword searching

    I've created a web site using MySQL and PHP to interact with the database and with several days of blood, sweat, and many tears I have been successful in being able to set up the database and tables with several search options.

    However, I can't seem to get a "keyword" search to pull up the results based on entering a keyword.

    I thought this would work but it doesn't:

    $get_prods = "select id, prod_name, city, prod_brief, prod_desc from products where prod_keywords and prod_desc like 'prod_keywords%' order by prod_name, city";

    Can anyone tell me if I'm close or far away from success?

    Thank you kindly for any responses!

  • #2
    raf
    raf is offline
    Master Coder
    Join Date
    Jul 2002
    Posts
    6,589
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Take a look at:

    http://www.mysql.com/doc/en/Fulltext_Search.html


    Your query will throw an error because of improper use of 'and'.

    Your query should look like
    $get_prods = "select id, prod_name, city, prod_brief, prod_desc from products where prod_keywords LIKE '%" . $keyword . "%' and prod_desc like '%" . $prod_keywords . "%' order by prod_name, city";

    where $keyword is a PHP variable, containing one keyword from the searchstring --> if your searchstring contains multiple keywords, then you need to explode it on spaces and build a collection with it, excluding words like 'the', 'a', 'like', 'then' + validating that the string doesn't contain wildcards (like %, _ etc) + taking logical operators into account etc.

    But there i no point in reinventing all this. The fulltext search will probably deliver whatyour after.

  • #3
    New Coder
    Join Date
    Dec 2003
    Location
    Kelowna, British Columbia
    Posts
    60
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Thank you raf... this looks like it will indeed do what I require. I am trying to get it to work and am stumbling on how to make the against value a constant when I am passing the user's inputted keyword as a variable from a previous page...

    can I create a constant from a variable? The reason for this is that in order for the match() against() function to work, the value of against must be a constant.

    eg. can i define the constant by doing this?
    define("KEYWORD", "$prod_keywords");

    also, I use PHPMyAdmin and I simply went into the table and made the necessary columns "fulltext" but when I test my query I get the message "Can't find FULLTEXT index matching the column list"

    any help would be hugely appreciated

  • #4
    raf
    raf is offline
    Master Coder
    Join Date
    Jul 2002
    Posts
    6,589
    Thanks
    0
    Thanked 0 Times in 0 Posts
    It's just

    ... MATCH (yourfultextcol1, yourfulltextcol2) AGAINST ('" . $_POST['searchbox'] . "') ...


    (assuming that the textfield on your searchform has name="searchbox" and that the action="post" inside the formtag)

    To create the index: never done it throug phpMyAdmin. But just open the sql tab and enter this

    ALTER TABLE yourtablename ADD FULLTEXT (yourfultextcol1, yourfulltextcol2);

    replace the bold by your tablename and the columnnames that the index needs to be created on. (maybe the semicolon isn't needed)

  • #5
    New Coder
    Join Date
    Dec 2003
    Location
    Kelowna, British Columbia
    Posts
    60
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Thanks again raf.... too many late nights trying to get this thing to work. I have all kinds of books here on PHP and MySQL and I've searched the net, but this site, with your help, is by far saving me a ton of time!

  • #6
    raf
    raf is offline
    Master Coder
    Join Date
    Jul 2002
    Posts
    6,589
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Thats the idea

    We all needed a few good pointers when we started out (or take on another language) and if we can save you a few hours by spending a few minutes, then we'll gladly do so.

  • #7
    New Coder
    Join Date
    Dec 2003
    Location
    Kelowna, British Columbia
    Posts
    60
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I discovered this site just a couple of weeks ago and it is the BEST resource I have found... I just have to learn how to get involved more.

    I am a graphic designer trying to expand my web skills and I will be spending time in other areas of this site hopefully I will be able to help others out with graphic stuff. I do believe that giving is just as important as receiving... and obviously the people in this site do as well... otherwise it would not work.

    It's pretty exciting when I put in the time (tons of it) and when I hit "enter" it works!

  • #8
    raf
    raf is offline
    Master Coder
    Join Date
    Jul 2002
    Posts
    6,589
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Good attitude.

    I hope you stick around and pick some stuff up. If you wanna get involved, then just jump in. There are plenty of eople around here needing help on design issues.

    Have fun!


  •  

    Posting Permissions

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