Results 1 to 11 of 11
  1. #1
    Join Date
    Feb 2012
    Posts
    6

    Question Unanswered: need help with MATCH AGAINST query

    Hi all Im pretty new to all this but I have a php script that is supposed to take a user inputed variable and query the database searching for the word(s) in either of two colums. but when I test it I dont get any errors but it also does not return any rows. I am trying to use MATCH AGAINST. This is my code-

    $SQLstring = "SELECT thumbnail, postTitle, link, startDate, price FROM tablead" .
    " WHERE MATCH (postTitle,postdescription) AGAINST ('$ant' NATURAL LANGUAGE MODE)";


    And here's the two columns from the data dictionary-


    Column Type Null Default Comments
    ----------------------------------------------------
    postTitle text Yes NULL

    postdescription text Yes NULL


    Can anyone help me with why it would not return any rows

  2. #2
    Join Date
    Sep 2009
    Location
    San Sebastian, Spain
    Posts
    880
    Hi, what I would do is print of the SQL statement so that $ant content is included. This way it may give you a better idea of where the problem lies.
    Ronan Cashell
    Certified Oracle DBA/Certified MySQL Expert (DBA & Cluster DBA)
    http://www.it-iss.com
    Follow me on Twitter

  3. #3
    Join Date
    Feb 2012
    Posts
    6

    still need help with MATCH AGAINST....

    Hi everyone I hate to repost but I still need help with my query issue using MATCH AGAINST here is the info I think/hope its all that is needed thank you



    I have a php script that is supposed to take a user inputed variable and query the database searching for the word(s) in either of two colums. but when I test it I dont get any errors but it also does not return any rows. I am trying to use MATCH AGAINST. This is my code-

    $SQLstring = "SELECT thumbnail, postTitle, link, startDate, price FROM tablead" .
    " WHERE MATCH (postTitle,postdescription) AGAINST ('$ant' NATURAL LANGUAGE MODE);";


    And here's the two columns from the data dictionary-


    Column Type Null Default Comments
    ----------------------------------------------------
    postTitle text Yes NULL

    postdescription text Yes NULL


    I tested to make sure $ant held the intended string using echo and it does and I know the field in the database held the string cause I put it there so I am at a loss. Please help

  4. #4
    Join Date
    Feb 2012
    Posts
    6
    I did test to make sure the $ant variable held the intended string and it does and I tried just running the query in PHPmyadmin and it says no rows returned. Im pretty sure there's no syntax errors cause it runs the query with no errors too.

  5. #5
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    don't repost, unless you think the thread may have been posted in the wrong section. in this case all you need do is bump the thread by reposting in the same thread to move the post up the queue

    threads merged
    I'd rather be riding on the Tiger 800 or the Norton

  6. #6
    Join Date
    Feb 2012
    Posts
    6
    I'm sorry for the repost thanks for the info

  7. #7
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    so what actually is in $ant
    what is the SQL you are actually sending to the db, as opposed to the SQL written as code in PHP.

    what table type is tabelad, IIRC Match against only works on MyISAM (but that ,might be out of date / changed since I last used Match against)
    I'd rather be riding on the Tiger 800 or the Norton

  8. #8
    Join Date
    Feb 2012
    Posts
    6
    $ant is holding the string from the text box and the SQL command Im sending is

    SELECT thumbnail, postTitle, link, startDate, price FROM tablead
    WHERE MATCH (postTitle,postdescription) AGAINST ('pontiac' IN NATURAL LANGUAGE MODE);


    the word pontiac is just an example it could be any word(s) and all my table use MyISAM.

  9. #9
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    how many rows, in total, are there in the table?

    could you run the following query please
    Code:
    SHOW CREATE TABLE tablead
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  10. #10
    Join Date
    Feb 2012
    Posts
    6
    sure it says

    tablead CREATE TABLE `tablead` (
    `thumbnail` varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL,
    `image1` varchar(50) COLLATE utf8_unicode_ci NOT NULL,
    `image2` varchar(50) COLLATE utf8_unicode_ci NOT NULL,
    `image3` varchar(50) COLLATE utf8_unicode_ci NOT NULL,
    `image4` varchar(50) COLLATE utf8_unicode_ci NOT NULL,
    `postTitle` text COLLATE utf8_unicode_ci,
    `link` varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL,
    `startDate` date DEFAULT NULL,
    `renewnotice` tinyint(1) DEFAULT NULL,
    `renewnoticedate` date DEFAULT NULL,
    `price` varchar(10) COLLATE utf8_unicode_ci DEFAULT NULL,
    `keyword1` varchar(20) COLLATE utf8_unicode_ci DEFAULT NULL,
    `keyword2` varchar(20) COLLATE utf8_unicode_ci DEFAULT NULL,
    `keyword3` varchar(20) COLLATE utf8_unicode_ci DEFAULT NULL,
    `keyword4` varchar(20) COLLATE utf8_unicode_ci DEFAULT NULL,
    `postdescription` text COLLATE utf8_unicode_ci,
    `catnum` varchar(10) COLLATE utf8_unicode_ci DEFAULT NULL,
    `adID` int(10) unsigned NOT NULL AUTO_INCREMENT,
    PRIMARY KEY (`adID`),
    FULLTEXT KEY `postTitle,postdescription` (`postTitle`,`postdescription`)
    ) ENGINE=MyISAM AUTO_INCREMENT=2 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

  11. #11
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by aaronandelib View Post
    AUTO_INCREMENT=2
    your table has only 2 rows in it ???!!

    Quote Originally Posted by da manual
    Such a technique works best with large collections (in fact, it was carefully tuned this way). For very small tables, word distribution does not adequately reflect their semantic value, and this model may sometimes produce bizarre results. For example, although the word MySQL is present in every row of the articles table shown earlier, a search for the word produces no results:
    Code:
    mysql> SELECT * FROM articles
        -> WHERE MATCH (title,body) AGAINST ('MySQL');
    Empty set (0.00 sec)
    The search result is empty because the word MySQL is present in at least 50% of the rows. As such, it is effectively treated as a stopword. For large data sets, this is the most desirable behavior: A natural language query should not return every second row from a 1GB table. For small data sets, it may be less desirable.

    A word that matches half of the rows in a table is less likely to locate relevant documents. In fact, it most likely finds plenty of irrelevant documents. We all know this happens far too often when we are trying to find something on the Internet with a search engine. It is with this reasoning that rows containing the word are assigned a low semantic value for the particular data set in which they occur. A given word may reach the 50% threshold in one data set but not another.

    The 50% threshold has a significant implication when you first try full-text searching to see how it works: If you create a table and insert only one or two rows of text into it, every word in the text occurs in at least 50% of the rows. As a result, no search returns any results. Be sure to insert at least three rows, and preferably many more.
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

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