[A better way to write this SQL select query]

Thank you everyone, I have been able to create the FULLTEXT index with the multiple columns, however when I do a search it produces no text results:

This is the SQL code that does not work:

[code]
$sql = "SELECT * FROM $inventory_table WHERE MATCH(year, size, color, gvw, brakes, vin, manufacturer, body_type, curb_wt, comments) AGAINST ('$search') AND status = 'Available'";
[/code]

But this code does work:

[code]
$sql = "SELECT * FROM $inventory_table WHERE ( year LIKE '%$search%' OR size LIKE '%$search%' OR color LIKE '%$search%' OR gvw LIKE '%$search%' OR brakes LIKE '%$search%' OR vin LIKE '%$search%' OR manufacturer LIKE '%$search%' OR body_type LIKE '%$search%' OR curb_wt LIKE '%$search%' OR comments LIKE '%$search%' ) AND status = 'Available'";
[/code]

Any ideas why it is producing no results for the FULLTEXT query? There are matching rows...

I know that SQL query is long and horrible, but my only alternatives are FULLTEXT searches right?

This table will be modified on a daily basis with probably 50 different insert/update queries per working day. Is this too much?

[A better way to write this SQL select query]

[!--quoteo(post=343507:date=Feb 7 2006, 07:16 PM:name=fenway)--][div class=\'quotetop\']QUOTE(fenway @ Feb 7 2006, 07:16 PM) [snapback]343507[/snapback][/div][div class=\'quotemain\'][!--quotec--]
That's right -- you need to add all of these columns to a _single_ FULLTEXT index.
[/quote]

Can you explain how to do this? I do it in phpMyAdmin and i just click on the FULLTEXT button for each column. You are saying that I have to create a full text index that has all of the columns in it?

[A better way to write this SQL select query]

What is the correct way to do a full search query? I set the columns to FULLTEXT indexes in mysql and this is the query that I tried in php:

[code]
$sql = "SELECT * FROM $inventory_table WHERE MATCH(year, size, color, gvw, brakes, vin, manufacturer, body_type, curb_wt, comments) AGAINST ('$search') AND status = 'Available'";
[/code]

This is the error I get:

Can't find FULLTEXT index matching the column list

I have verified that all of the columns are set as FULLTEXT

[A better way to write this SQL select query]

This is a SQL query I am using in php, it does not work when I put in the second WHERE clause, other than that it works as desired. Is there a way to write it to where it uses both WHERE clauses, maybe even a way to shorten up the query?

$sql = "SELECT * FROM $inventory_table WHERE year LIKE '%$search%' OR size LIKE '%$search%' OR color LIKE '%$search%' OR gvw LIKE '%$search%' OR brakes LIKE '%$search%' OR vin LIKE '%$search%' OR manufacturer LIKE '%$search%' OR body_type LIKE '%$search%' OR curb_wt LIKE '%$search%' OR comments LIKE '%$search%' WHERE status = 'Available'";