NYCPHP Meetup

[Phillip Powell]

Apologies in advance if this is off-topic but this has been a perplexing 
issue now for weeks and found no resolution on several PHP and MySQL 
boards and forums.

Environment: PHP 4.3.2, MySQL 4.0.10, Apache 2.0, Linux Red Hat 7.3


I have a rather complicated query that is dynamically created via PHP 
class method, with a combination of LEFT JOINs and two MATCHES where the 
first match is non-boolean to get the accurate score, the second to 
search as boolean:

    quote:
    ------------------------------------------------------------------------

    SELECT
    image.id, image.image_name,
    (MATCH (image_name, image_alt, image_location_city,
    image_location_state, image_location_country) AGAINST ('test')
    OR MATCH (first_name, last_name) AGAINST ('test')
    OR MATCH (keyword_name) AGAINST ('test')
    OR MATCH (event_name) AGAINST ('test')
    OR MATCH (placement_name) AGAINST ('test')
    ) as score,
    image.image_path, image.image_creation_date
    FROM image

    LEFT JOIN image_person_assoc ON image_person_assoc.image_id = image.id
    LEFT JOIN person ON person.id = image_person_assoc.person_id
    LEFT JOIN image_keyword_assoc ON image_keyword_assoc.image_id =
    image.id
    LEFT JOIN keyword ON keyword.id = image_keyword_assoc.keyword_id
    LEFT JOIN image_event_assoc ON image_event_assoc.image_id = image.id
    LEFT JOIN event ON event.id = image_event_assoc.event_id
    LEFT JOIN image_placement_assoc ON image_placement_assoc.image_id =
    image.id
    LEFT JOIN placement ON placement.id =
    image_placement_assoc.placement_id

    WHERE MATCH (image_name, image_alt, image_location_city,
    image_location_state, image_location_country) AGAINST ('+test+' IN
    BOOLEAN MODE)
    OR MATCH (first_name, last_name) AGAINST ('+test+' IN BOOLEAN MODE)
    OR MATCH (keyword_name) AGAINST ('+test+' IN BOOLEAN MODE)
    OR MATCH (event_name) AGAINST ('+test+' IN BOOLEAN MODE)
    OR MATCH (placement_name) AGAINST ('+test+' IN BOOLEAN MODE)

    GROUP BY image.id
    ORDER BY score DESC, upper(image.image_name) ASC
    ------------------------------------------------------------------------



Sample Results:

    quote:
    ------------------------------------------------------------------------

    +-----+----------------------------+-------+-------------------------------------------------------------------------+---------------------+

    | id | image_name             | score |
    image_path                                     | image_creation_date |
    +-----+----------------------------+-------+-------------------------------------------------------------------------+---------------------+

    | 100 |blah.jpg                   | 1        |
    /html/images/blah.jpg                  | 2003-01-01 |
    | 101 | mysql-81x42.png | 1        | /html/images/mysql-81x42.png |
    0000-00-00 |
    +-----+----------------------------+-------+-------------------------------------------------------------------------+---------------------+

    ------------------------------------------------------------------------



Using this query I always get a relevancy score of 1 every time; I do 
not actually get the floating-point decimal number that I was seeking 
(the accurate relevancy); this based on information I found at 
http://dev.mysql.com/doc/mysql/en/Fulltext_Search.html . The SQL query 
is correct, though (I receive no SQl-related nor MySQL-related errors), 
just not numerically accurate in its relevancy.

Anything I might need to do to finetune this?

Thanx
Phil

-- 
---------------------------------------------------------------------------------
Phil Powell
Multimedia Programmer
BPX Technologies, Inc.
#: (703) 709-7218 x107 
Fax: (703) 709-7219