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 10 of 10
  1. #1
    Regular Coder
    Join Date
    Jan 2004
    Location
    Des Moines, Iowa
    Posts
    219
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Search MySQL and then display results

    Alright - I think I've programmed myself into insanity tonight... Need some help.

    I have a search page in which I can search by a keyword or by several "categories" plus the keyword for a more refined search.

    Scenario is:
    products.php (shows search if no product has been displayed)
    dosearch.php (function script that performs the search by criteria passed from products.php)

    I'm trying to search about 7 different columns for a match to the keyword and/or a match to the keyword + criteria.

    First part of dosearch says if user has only put in a keyword, and not selected any other criteria, then do a full text search of everything.


    PHP Code:
    if (!empty($search_text) && empty($category) && empty($mfg) && empty($subcat)){
        
    mysql_select_db($database);
        
    $search $search_text;
        
    $sql "SELECT * FROM products WHERE category LIKE '%".$search."%' OR mfg LIKE '%".$search."%' OR subCat LIKE '%".$search."%' OR name LIKE '%".$search."%' OR descr LIKE '%".$search."%' OR overview LIKE '%".$search."%' OR features LIKE '%".$search."%'";
        
    $result mysql_query($sql) or die(mysql_error());
        
    $rows mysql_num_rows($result); 
    And I'll stop there so not to have a 100 page post. Now - my question is, how do I sort by the "closest match" or "best match"...

    #2 - the search is bound to return more than 1 result. How do I iterate through those results back in products.php so that I can show all the products that the query returned?

    Do I have to run another query back in products.php or can I iterate the results and use an array... I'm kind of stuck.

    Any help?
    Rich S. Wyatt
    D3 Web Creations

  • #2
    raf
    raf is offline
    Master Coder
    Join Date
    Jul 2002
    Posts
    6,589
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I didn't understand your explanation.

    You are talking about a full text search, wut your query just uses a regular where clause with a like-operator.
    more info on full text search http://dev.mysql.com/doc/mysql/en/Fulltext_Search.html

    there is a big difference against keyword searching and 'contextual searching'. To order the results of a keywordsearch, you'll need to have some sort of algorithme that specifies the weight that each match should get. 1 point for each found keyword? 1 point for each field with a keyword in? 5 points if the keyword is in the titlefield and 1 point in the other fields?

    Its all possible, but you'll need to tell us what exactly you need.
    Maybe these clarify some things
    http://www.codingforums.com/showthre...ll+text+search
    Posting guidelines I use to see if I will spend time to answer your question : http://www.catb.org/~esr/faqs/smart-questions.html

  • #3
    Regular Coder
    Join Date
    Jan 2004
    Location
    Des Moines, Iowa
    Posts
    219
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Alright RAF..

    I knew my query was bunk. LOL.

    I am looking to do a full text search... my table structure is:

    id | productID | name | category | subCat | descr | image | overview | features |

    A user has a form with option to enter a word/words to search by...

    they may also enter word/words plus select whether to search within a specific category, manufacturer or subCat.

    I like your suggestion about points, but I'm not sure how to do that.

    So basically - I need a query that will search within productID, name, category, subCat, descr, overview and features when no other criteria are chosen and then order them by which one is the best match.

    If they match productID or name, those would be .. I guess? big point matches. descr, overview and feature content matches would be big matches too, but not as big as the first two. finally - any matches to category, subCat and mfg would be last.

    Does that make more sense?
    Rich S. Wyatt
    D3 Web Creations

  • #4
    raf
    raf is offline
    Master Coder
    Join Date
    Jul 2002
    Posts
    6,589
    Thanks
    0
    Thanked 0 Times in 0 Posts
    if you are using the mysql full text function (match () against()), then you can not influence the weight.
    see first link in previous mail

    If you wanna give points, like i outlined above, then the second link i posted should get you started. It will be something like

    sql="select *, if(locate('". $_POST['searchkey']."', productID),7,0) as prod, if(locate('". $_POST['searchkey']."', name),7,0) as nm, if(locate('". $_POST['searchkey']."', category),3,0) as cat, (locate('". $_POST['searchkey']."', subCat),1,0) as sCat, (locate('". $_POST['searchkey']."', descr),1,0) as dscr FROM yourtable ORDER BY prod DESC, nm DESC, cat DESC, sCat DESC"

    the numbers in bold will be the value of the resulting variable (prod, nm, ...) is the searchkey appears in the column (productID, name, ...). The other number is the value if the searchkey isn't included (0).
    Posting guidelines I use to see if I will spend time to answer your question : http://www.catb.org/~esr/faqs/smart-questions.html

  • #5
    Regular Coder
    Join Date
    Jan 2004
    Location
    Des Moines, Iowa
    Posts
    219
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Excellent - got it all in... now it gives me this error

    Can't find FULLTEXT index matching the column list

    but I searched for the term HP which I know is a match to the mfg field.

    Here is my query:

    $sql = "SELECT *, if(locate('".$search."', name),10,1) as name, if(locate('".$search."', productID),10,1) as productID, if(locate('".$search."', category),7,1) as category, if(locate('".$search."',descr),5,1) as descr, if(locate('".$search."',overview),5,1) as overview, if(locate('".$search."',features),5,1) as features, if(locate('".$search."',mfg),7,1) as mfg, if(locate('".$search."',subCat),7,1) as subCat, MATCH (name,productID) AGAINST ('".$search."') AS relevance FROM products WHERE MATCH(name,productID) AGAINST ('".$search."') ORDER BY name DESC, productID DESC, relevance DESC LIMIT 0,30";

    $result = mysql_query($sql) or die(mysql_error());
    $rows = mysql_num_rows($result);
    Rich S. Wyatt
    D3 Web Creations

  • #6
    Regular Coder
    Join Date
    Jan 2004
    Location
    Des Moines, Iowa
    Posts
    219
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Urg - disregard... It would help if I altered my table for the fulltext search... It is 7:30 am... mind isn't here yet.
    Rich S. Wyatt
    D3 Web Creations

  • #7
    Regular Coder
    Join Date
    Jan 2004
    Location
    Des Moines, Iowa
    Posts
    219
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Well that didn't work - I made each field an index with FULL TEXT...

    Am I supposed to make one index with all fields in it?

    Also - can I these fields be varchars and longtext and text... or do they all have to be text.

    ARg!
    Rich S. Wyatt
    D3 Web Creations

  • #8
    raf
    raf is offline
    Master Coder
    Join Date
    Jul 2002
    Posts
    6,589
    Thanks
    0
    Thanked 0 Times in 0 Posts
    should be one index with all variable from your match().

    the columns must be char, varchar or text.
    Posting guidelines I use to see if I will spend time to answer your question : http://www.catb.org/~esr/faqs/smart-questions.html

  • #9
    Regular Coder
    Join Date
    Jan 2004
    Location
    Des Moines, Iowa
    Posts
    219
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Alright - last question...

    Here is my SQL statement:

    PHP Code:
    $sql "select *, if(locate('".$search."', productID),7,0) as prod, if(locate('".$search."', name),7,0) as nm, if(locate('".$search."', descr),1,0) as dscr, MATCH (productID, name, descr, overview, features) AGAINST ('".$search."') AS relevance FROM products ORDER BY relevance DESC, prod DESC, nm DESC, category DESC, subCat DESC"
    I guess I don't know if the results are correct... 'relevance' gives me a big fat 0... see the result below.. (I did a fulltext search on the word 'bob')

    +----+----------------+------------------+------------+-----+----------+------+----+------+------+---------+-----------+
    -> | id | productID | nam |category | mfg| subCat| prod|nm| dscr| over | feature | relevance |
    -> +----+----------------+------------------+------------+-----+----------+------+----+------+------+---------+-----------+
    -> | 4 | 0 | bobtest | bob | HP | test | 0 | 7 | 0 | 0 | 0 | 0 |
    -> | 3 | hpcompaqdx2000 | HP Compaq DX2000 | Commercial | HP | Desktops | 0 | 0 | 0 | 0 | 0 | 0 |
    -> | 5 | 0 | NULL | NULL | | | 0 | 0 | 0 | 0 | 0 | 0 |
    -> +----+----------------+------------------+------------+-----+----------+------+----+------+------+---------+-----------+


    So - I'm wondering why I'm not getting a relevance #? Any thoughts?
    Last edited by rswyatt; 07-01-2004 at 03:54 AM.
    Rich S. Wyatt
    D3 Web Creations

  • #10
    Regular Coder
    Join Date
    Jan 2004
    Location
    Des Moines, Iowa
    Posts
    219
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Can I delete the above thread? LOL... So I don't think well early in the AM and late at night.

    THe problem was too short of a search word. :-)

    I'm going to bed now!
    Rich S. Wyatt
    D3 Web Creations


  •  

    Posting Permissions

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