List:General Discussion« Previous MessageNext Message »
From:Paul DuBois Date:March 4 2003 3:17am
Subject:Re: FULLTEXT indexes and select query problem
View as plain text  
At 21:56 -0500 3/3/03, Stitchin' wrote:
>I found some stuff in the mySql manual about setting columns as fulltext
>indexes for fulltext searches and the syntax is
>  WHERE MATCH (column names...) AGAINST (the search stuff)
>So I went in and did an alter table statement to set my four search columns
>to be in a fulltext index.
>So here's the freaky thing ... I create this hideous looking select
>statement to grab the right stuff out of the database (all the stuff ending
>in _int and $searchwords are the variables from the search form that the
>user fills out to pick the designs they want to see)
>  $query = "SELECT * FROM designs, appliquetable, puffyfoamtable
>             WHERE designs.puffyfoam = puffyfoamtable.puffyfoam
>             AND designs.applique = appliquetable.applique
>             AND designs.applique=$applique_int
>             AND designs.puffyfoam=$pfoam_int
>             AND designs.largesize <= $largesize_int
>             AND designs.smallsize <= $smallsize_int
>             AND designs.availablefiles LIKE "%$file_int%"
>             AND MATCH(designname,designcomments,catcode,textsearch)
>             AGAINST ($searchwords)";
>      $result = mysql_query($query)
>           or die ("Couldn't execute query A<2 P<2.");
>And I get the "Couldn't execute query A<2 P<2." Error... but when I copy the
>statement into phpMyAdmin SQL screen and put values in the spots like below,
>it actually finds the one record in the database that matches:
>SELECT  *  FROM designs, appliquetable, puffyfoamtable
>WHERE designs.puffyfoam = puffyfoamtable.puffyfoam
>AND designs.applique = appliquetable.applique
>AND designs.applique = 0
>AND designs.puffyfoam = 0
>AND designs.largesize <= 4.00
>AND designs.smallsize <= 4.00
>AND designs.availablefiles LIKE  "%SEW%"
>AND  MATCH ( designname, designcomments, catcode, textsearch) AGAINST
>It probably something stupid like quotes or something ... I'm gonna go back
>and double-check those variables from the search form ... Anybody out there
>see what I missed? :)

It probably something stupid like quotes or something.  Maybe next time
you could go back and double-check *first* before posting?  Thanks.

But you might also try changing your die() call so that it prints the
value of mysql_error() -- that way you'll see the actual error message
being returned by the server.  That'll give you some information you're
now not taking advantage of. Also, print the text of the query; that'll
likely show you what you need to know.  Yes, that's a hint as to the
source of your problem.

>Renee Toth
>Stitchin' Up A Storm

FULLTEXT indexes and select query problemStitchin'4 Mar
  • Re: FULLTEXT indexes and select query problemPaul DuBois4 Mar