[SOLVED] keyword search won't co-operate

[gwydionwaters]

so i am trying to get a keyword search where the user can type in a word and have the select search two columns and return results from either/both columns.

i have tried

SELECT * FROM blah WHERE items.Title LIKE '%$Key%' OR items.Desc LIKE '%$Key%'

also i have tried just

where item.title = '$key'

which works, but i want to be able to use a boolean type search. so you can search for car and get:

car parts

my new car

etc ..

[peranha]

Works fine for me.

 

so i am trying to get a keyword search where the user can type in a word and have the select search two columns and return results from either/both columns.

i have tried

SELECT * FROM blah WHERE items.Title LIKE '%$Key%' OR items.Desc LIKE '%$Key%'

also i have tried just

where item.title = '$key'

which works, but i want to be able to use a boolean type search. so you can search for car and get:

car parts

my new car

etc ..

 

Is your table items, or item?

[gwydionwaters]

that's wierd, my table is items, i skipped the s due to lazzines.

my full query is as such

SELECT items.Title, items.Desc, author.Author, type.Type, func.Func, cost.Cost, rate.Rate 
FROM items 
INNER JOIN author ON items.Author_id = author.Author_id 
INNER JOIN type ON items.Type_id = type.Type_id 
INNER JOIN func ON items.Func_id = func.Func_id 
INNER JOIN cost ON items.Cost_id = cost.Cost_id 
INNER JOIN rate ON items.Rate_id = rate.Rate_id 
WHERE items.Title LIKE '%$Key%' OR items.Desc LIKE '%$Key%'

which like i said works fine with only the 'WHERE items.Title = '$Key'" but not with any sort of wildcard, nor will it return any results if i try "WHERE items.Title = '$Key' OR items.Desc = '$Key'"

[gwydionwaters]

so i have started another approach as a second option

i have indexed (table)items on title and desc

and now am trying this to get the key term in the select as text

$Key=$_POST['Key'];
$Key=trim($Key);            //remove whitespace off ends
include("dbase.incl.php");
mysql_connect($hostname,$username,$password);
@mysql_select_db($database) or die( "Unable to select database");
$query = "SELECT items.Title, items.Desc, author.Author, type.Type, func.Func, cost.Cost, rate.Rate 
FROM items 
INNER JOIN author ON items.Author_id = author.Author_id 
INNER JOIN type ON items.Type_id = type.Type_id 
INNER JOIN func ON items.Func_id = func.Func_id 
INNER JOIN cost ON items.Cost_id = cost.Cost_id 
INNER JOIN rate ON items.Rate_id = rate.Rate_id 
WHERE MATCH (Title,Desc) AGAINST ('";
$Key2 = $Key;            //i want to use $Key as is later on
$key2 .= "' IN BOOLEAN MODE)";
$query .= $Key2;
$result=mysql_query($query);
$num=mysql_num_rows($result);
mysql_close();

but it say's my query is not a valid result resource my query would be

SELECT items.Title, items.Desc, author.Author, type.Type, func.Func, cost.Cost, rate.Rate 
FROM items 
INNER JOIN author ON items.Author_id = author.Author_id 
INNER JOIN type ON items.Type_id = type.Type_id 
INNER JOIN func ON items.Func_id = func.Func_id 
INNER JOIN cost ON items.Cost_id = cost.Cost_id 
INNER JOIN rate ON items.Rate_id = rate.Rate_id 
WHERE MATCH (Title,Desc) AGAINST ('term' IN BOOLEAN MODE)

is this an incorrect syntax?

[fenway]

Check mysql_error() to find out exactly what's wrong.

[gwydionwaters]

do i just add

mysql_error();

 

i get this error on the page after a search attempt

Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource in /f1/content/gwaters/public/scripts/gearsearch.php on line 19

 

is there a better way to search a small (under 1000 entries) table? i only want to search two fields in the table. and i want to send a term or string (depending on user that is) and have it return all entires that contain the term or string in either field1 or field2 or both

[fenway]

Add it before mysql_num_rows() -- try:

 

$result=mysql_query($query) or die( mysql_error() )

[gwydionwaters]

ok, i got this

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'Desc) AGAINST ('mora'/*which is the first word in one of the titles, althought the title is 'Mora'*/ at line 1

which would be refering to this here

MATCH (Title,Desc)/*both are fulltext indexed in the table*/ AGAINST ('term'/ IN BOOLEAN MODE)

i have tried it with caps and also i have tried searching the whole title. no luck there.

i even tried matching only against title and using the full exact title of an entry, also no luck

[gwydionwaters]

got it, using joins i had to be more specific with the columns

 MATCH (items.Title,items.Desc) AGAINST 

and in mysql 5.0 (not 5.1 like i have been reading tutorials for) there is no need for the

AGAINST ('term' IN NATURAL LANGUAGE MODE)

as opposed to

AGAINST ('term')

thanks for helping me figure it out

[fenway]

Actually, the real issue is that "desc" is a reserved keyword... you simply accidentally worked around the problem by using a table alias.  Rename the column ASAP.