Graham Anderson <[EMAIL PROTECTED]> wrote on 04/14/2005 11:51:58 AM:
> many thanks
> that was exactly what I needed.....
>
> Building upon the initial question:
> Is there a way to get Match statements to do partial word
> searches...like %LIKE% ?
>
> For example, if the below Match Against query with 'chris' does not
> bring up anything...
> AND MATCH (
> media.name, media.product
> )
> AGAINST (
> 'chris'
> )
>
> Query the database again with 'chris*' ? Is querying the database(if
> the first query fails) twice the best way to do this?
> AND MATCH (
> media.name, media.product
> )
> AGAINST (
> 'chris*'
> )
>
>
> Is there a standard way of doing this ?
>
> thanks Shawn :)
>
> g
> On Apr 13, 2005, at 11:00 AM, [EMAIL PROTECTED] wrote:
>
> > Graham Anderson <[EMAIL PROTECTED]> wrote on 04/13/2005 01:46:35 PM:
> >
> >> Hi
> >> I am trying to set up my Match statements to filter the result of the
> >> main query [which works]
> >>
> >> If 'chris' does not exist in the first MATCH statement [AND MATCH
> >> (media.name, media.product)], then the results get a bit screwed up
:(
> >> If the first match statement finds something, then the query works
> >> fine....
> >>
> >> What would be a better way to structure this...without using a
> >> subquery
> >> as I am on MYSQL 3.23
> >>
> >>
> >> SELECT media.id, media.product AS product, media.name AS name,
> >> Concat_WS( ' ', artist.fname, artist.lname ) AS artist, mediaType.id
> >> AS
> >> mediatype, mediaType.name, media.product, media.path
> >> FROM media, artist, mediaType
> >> WHERE media.artist_id = artist.id
> >> AND media.mediaType_id = mediaType.id
> >>
> >> AND MATCH (
> >> media.name, media.product
> >> )
> >> AGAINST (
> >> 'chris'
> >> )
> >> OR MATCH (
> >> artist.fname, artist.lname
> >> )
> >> AGAINST (
> >> 'chris'
> >> )
> >> ORDER BY product, media.name, artist ASC
> >> LIMIT 0 , 30
> >>
> >>
> > it's a parentheses issue. Your query looks like this in the where
> > clause
> >
> > WHERE artist_ID AND mediaType_ID AND first match OR second match
> >
> > Which gets evaluated like
> >
> > WHERE (artist_ID AND mediaType_ID AND first match) OR second match.
> >
> > Any record that matched your second match condition also satisfied
your
> > WHERE clause. Because you wanted to match on artist_Id and
mediaType_ID
> > plus one of the match conditions, you needed to put a set of
> > parentheses
> > around BOTH of your match conditions so that your WHERE clause looked
> > like:
> >
> > WHERE artist_ID AND mediaType_ID AND (first match OR second match)
> >
> > Here is an updated version of your original query (I also changed your
> > implicit inner joins to explicit ones (it's a pet peeve)):
> >
> > SELECT media.id, media.product AS product
> > , media.name AS name
> > , Concat_WS( ' ', artist.fname, artist.lname ) AS artist
> > , mediaType.id AS mediatype
> > , mediaType.name
> > , media.product
> > , media.path
> > FROM media
> > INNER JOIN mediaType
> > ON media.mediaType_id = mediaType.id
> > INNER JOIN artist
> > ON media.artist_id = artist.id
> > WHERE MATCH (media.name, media.product)
> > AGAINST ('chris')
> > OR MATCH (artist.fname, artist.lname)
> > AGAINST ('chris')
> > ORDER BY product, media.name, artist ASC
> > LIMIT 0 , 30
> >
> >
> > Shawn Green
> > Database Administrator
> > Unimin Corporation - Spruce Pine
>
>
Sorry, according to:
http://dev.mysql.com/doc/mysql/en/fulltext-restrictions.html full text
searches are only exact match (with or without case sensitivity).
