SitePoint Sponsor

User Tag List

Results 1 to 13 of 13

Hybrid View

  1. #1
    SitePoint Addict Philip Toews's Avatar
    Join Date
    Dec 2001
    Location
    Kuala Belait, Brunei
    Posts
    367
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    extending FULLTEXT searches

    A FULLTEXT search is limited to one table.
    All parameters to the MATCH function must be columns from the same table that is part of the same fulltext index, unless this MATCH is IN BOOLEAN MODE.
    What does that mean? Sorry for the newbie question.

    p
    Philip Toews Professional esl Educator and ASP.NET wannabe

    http://www.philiptoews.com
    philip@philiptoews.com

  2. #2
    SitePoint Author Kevin Yank's Avatar
    Join Date
    Apr 2000
    Location
    Melbourne, Australia
    Posts
    2,571
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    Consider this example: Your site has two tables: authors and articles. Each author has a description, and each article has a body text. Since you want both of these fields to be keyword-searchable, you might decide to create FULLTEXT indices for each of them.

    The problem now is that a single MATCH function in a SELECT query can't search both indices at once, as they are in different tables. Instead, you need to search each table separately and combine the results in some intelligent fashion yourself.

    So basically, a single FULLTEXT search can't search fields in multiple tables at once.
    Kevin Yank
    CTO, sitepoint.com
    I wrote: Simply JavaScript | BYO PHP/MySQL | Tech Times | Editize
    Baby’s got back—a hard back, that is: The Ultimate CSS Reference

  3. #3
    SitePoint Addict Philip Toews's Avatar
    Join Date
    Dec 2001
    Location
    Kuala Belait, Brunei
    Posts
    367
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Question OK..just two more questions then

    What does the "BOOLEAN" part of the restriction mean? (that's a newbie question...I know...sorry).

    More importantly. How do I do what you suggested?:
    Instead, you need to search each table separately and combine the results in some intelligent fashion yourself.
    In fact, what I want to do is pass the accumulated results to the paged results class that you describe in your tutorial on that subject. I have modifed the CLASS to accept results instead of queries, but I don't know how to merge the results of two separate queries into one result.

    For that matter, what I am wondering here is how to do table joins for FULLTEXT queries. Using your tutorial example, how would I force MySQL to only SELECT the records in a given category of jokes? The way the system works (as produced by the tutorial in your book) is that categories are determined by queries to a lookup table. This neccesitates a table join in the query, n'est-ce pas? So here is the crux of my confusion. How can I set up a query using a FULLTEXT search that is restricted to only certain categories as determined by a query to a lookup table?

    The FULLTEXT search capability in MySQL is very cool, but the restrictions on it make it a pain to work with. A solution to my categories inclusion question above would make it possible to extend the CMS in your book in an extremely useful way.

    Any responses will be GREATLY appreciated.
    Philip Toews Professional esl Educator and ASP.NET wannabe

    http://www.philiptoews.com
    philip@philiptoews.com

  4. #4
    SitePoint Author Kevin Yank's Avatar
    Join Date
    Apr 2000
    Location
    Melbourne, Australia
    Posts
    2,571
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    For the BOOLEAN part of the restriction, I'd suggest reading the MySQL reference manual's section on the subject. If that doesn't explain it for you, let me know and I'll take a closer look.

    As for using MATCH in SELECTs that involve table joins, there's nothing to stop you from doing this. The only restriction is that a single MATCH expression cannot span multiple tables.
    Kevin Yank
    CTO, sitepoint.com
    I wrote: Simply JavaScript | BYO PHP/MySQL | Tech Times | Editize
    Baby’s got back—a hard back, that is: The Ultimate CSS Reference

  5. #5
    SitePoint Addict Philip Toews's Avatar
    Join Date
    Dec 2001
    Location
    Kuala Belait, Brunei
    Posts
    367
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    So how does one merge query results?

    Thanks for the clarification. The ability to use table joins will be helpful, but I am still wondering how to merge results together. How do I do separate queries and then join the results together in order to pass them to things like the paged results class?

    p
    Philip Toews Professional esl Educator and ASP.NET wannabe

    http://www.philiptoews.com
    philip@philiptoews.com

  6. #6
    SitePoint Author Kevin Yank's Avatar
    Join Date
    Apr 2000
    Location
    Melbourne, Australia
    Posts
    2,571
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    Since each MATCH expression produces a numerical value that represents the relevance of each row to the search keywords, you can take the sum of two MATCH expressions as the total relevance based on two MATCH searches.

    SELECT *, MATCH (table1.col1) AGAINST ('keyword') + MATCH (table2.col2) AGAINST ('keyword') AS rel WHERE rel > 1 ORDER BY rel DESC
    Kevin Yank
    CTO, sitepoint.com
    I wrote: Simply JavaScript | BYO PHP/MySQL | Tech Times | Editize
    Baby’s got back—a hard back, that is: The Ultimate CSS Reference

  7. #7
    SitePoint Addict Philip Toews's Avatar
    Join Date
    Dec 2001
    Location
    Kuala Belait, Brunei
    Posts
    367
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    thanks Kevin

    I appreciate the detailed answer. It opens up a whole spate of possibilities for FULLTEXT searches.

    Seems the MySQL 4.0 holds great improvements for FULLTEXT searching capabilities. Faster, more stable...maybe even a few configuarable parameters.

    Does anyone have an informed notion of when that will be released? I'm waiting to buy a MySQL reference book until then....

    Thanks again Kevin

    p
    Philip Toews Professional esl Educator and ASP.NET wannabe

    http://www.philiptoews.com
    philip@philiptoews.com

  8. #8
    SitePoint Addict Philip Toews's Avatar
    Join Date
    Dec 2001
    Location
    Kuala Belait, Brunei
    Posts
    367
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    trouble joining tables in FULLTEXT search

    I have tried to follow Kevin's advice by using the following query. Both tables exist and both have FULLTEXT indexes.

    Here's my query:
    SELECT * , MATCH (grand_summary.long_text) AGAINST ('simple') + MATCH (grammar_sections.long_text) AGAINST ('simple') FROM grand_summary, grammar_sections AS rel WHERE rel > 1 ORDER BY rel DESC;
    Here's the error message:
    Unknown table 'grammar_sections' in field list
    Can anyone please tell me how to adjust this query so that I can search two tables as a table join doing a FULLTEXT search?

    p
    Philip Toews Professional esl Educator and ASP.NET wannabe

    http://www.philiptoews.com
    philip@philiptoews.com

  9. #9
    Making a better wheel silver trophy DR_LaRRY_PEpPeR's Avatar
    Join Date
    Jul 2001
    Location
    Missouri
    Posts
    3,428
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    you have your "AS rel" in the wrong place. you were aliasing grammar_sections as rel, so then it should be referred to as rel in your SELECT (e.g. rel.long_text), NOT grammar_sections. and should that not be rel > 0? here's how it should be:

    SELECT *, MATCH (grand_summary.long_text) AGAINST ('simple') + MATCH (grammar_sections.long_text) AGAINST ('simple') AS rel FROM grand_summary, grammar_sections WHERE rel > 0 ORDER BY rel DESC;
    however, i still don't think that will work. you'll probably get something like "Unknown column rel..." because rel is not a column. i believe you should do it like this:

    SELECT *, MATCH (grand_summary.long_text) AGAINST ('simple') + MATCH (grammar_sections.long_text) AGAINST ('simple') AS rel FROM grand_summary, grammar_sections WHERE (MATCH (grand_summary.long_text) AGAINST ('simple') AND MATCH (grammar_sections.long_text) AGAINST ('simple')) ORDER BY rel DESC;
    if you want to allow a match in either table, rather than both, use OR in the WHERE instead of AND.
    - Matt ** Ignore old signature for now... **
    Dr.BB - Highly optimized to be 2-3x faster than the "Big 3."
    "Do not enclose numeric values in quotes -- that is very non-standard and will only work on MySQL." - MattR

  10. #10
    SitePoint Addict Philip Toews's Avatar
    Join Date
    Dec 2001
    Location
    Kuala Belait, Brunei
    Posts
    367
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Almost there....

    Thanks Dr. Pepper,

    I have tried the code and I get the following FULLTEXT error:

    MySQL said: Can't find FULLTEXT index matching the column list
    Now I am sure that both grand_summary and grammar_sections each have a FULLTEXT index. I don't know if it is relevant, but the two tables I am working with here have a FULLTEXT index on three columns each. That is to say, they are each indexed on three columns. In both cases, the columns have the same names...NAME, SHORT_TEXT, LONG_TEXT.

    What exactly is this error message telling me? That I need to list all three columns, or that it can't reconcile a FULLTEXT search on two tables?

    Just a little more help <beg>please....</beg>


    TIA

    p
    Philip Toews Professional esl Educator and ASP.NET wannabe

    http://www.philiptoews.com
    philip@philiptoews.com

  11. #11
    Making a better wheel silver trophy DR_LaRRY_PEpPeR's Avatar
    Join Date
    Jul 2001
    Location
    Missouri
    Posts
    3,428
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    i don't have any experience with FULLTEXT indexes, so i didn't know if i'd be able to help with this error. however, it looks like you may need to list all columns that have the FULLTEXT index. i found this in the manual, under Full-text Restrictions:

    The MATCH() column list must exactly match the column list in some FULLTEXT index definition for the table, unless this MATCH() is IN BOOLEAN MODE.
    isn't that what it sounds like? hope that helps.

  12. #12
    SitePoint Member
    Join Date
    May 2003
    Location
    finland
    Posts
    2
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Question

    hi all,

    I also have the same problem. I tried to use this statement

    SELECT *, MATCH (products.product_name) AGAINST ('simple') + MATCH (services.service_name) AGAINST ('simple') AS rel FROM products, services WHERE (MATCH (products.product_name) AGAINST ('simple') OR MATCH (services.service_name) AGAINST ('simple')) ORDER BY rel DESC;
    And I'm getting this error

    Can't find FULLTEXT index matching the column list

    I also have in products table a product_description and in services a service_description wich also are fulltext fields and I also wanna search them in the same select statement. How can I do that?

    Thanks

  13. #13
    SitePoint Member
    Join Date
    May 2003
    Location
    finland
    Posts
    2
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi all,
    To tell you what I'm trying to do is to make a search engine for my web. and I have these tables in my database

    CREATE TABLE tuotteet (
    id INT AUTO_INCREMENT NOT NULL PRIMARY KEY,
    nimi VARCHAR(200) NOT NULL,
    ryhma VARCHAR(200) NOT NULL,
    kuvaus TEXT NOT NULL,
    kuva TEXT NOT NULL,
    esite TEXT,
    FULLTEXT (kuvaus)
    );

    CREATE TABLE palvelut (
    id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
    nimi VARCHAR(200) NOT NULL,
    kuvaus TEXT NOT NULL,
    kuva TEXT NOT NULL,
    FULLTEXT (kuvaus)
    );

    I'm triying to fulltext search the kuvaus fields and this is the sql

    SELECT *, MATCH (tuotteet.kuvaus ) AGAINST ('korjaus') + MATCH (palvelut.kuvaus) AGAINST ('korjaus') AS rel FROM tuotteet, palvelut WHERE (MATCH (tuotteet.kuvaus) AGAINST ('korjaus') OR MATCH (palvelut.kuvaus) AGAINST ('korjaus')) ORDER BY rel DESC;

    but this is returning both fields in one result.what i want is if the word korjaus found in table tuotteet field kuvaus, then the search returns that fields id name wich is link
    and if it also found in the other tables kuvaus it also return in that fields id and name but in a new line.
    did you understand what i mean?
    if you can give me some example will be great




    thanks again


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
  •