What can I do to make this SQL return results in certain situations?

I could use some help to m开发者_如何转开发ake this search return results in certain situations. The user has 2 fields to fill in and I would like to be able to leave either blank or fill in both. The fields are vehicle and keywords. The vehicle field is meant to allow searching based on the make, model, VIN, truck number (often is 2 - 3 digits or a letter prefix followed by 2 digits), and a few other fields that belong to the truck table. The keywords are meant to search most fields in the maintenance and maintenance_parts tables (things like the description of the work, parts name, parts number). Currently leaving the keywords field blank returns no results even when the vehicle field has valid results of its own.

"SELECT M.maintenance_id, M.some_id, M.type_code, M.service_date, M.mileage, M.mg_id, M.mg_type, M.comments, M.work_done,
                MATCH( M.comments, M.work_done) AGAINST( '$keywords' ) +
                MATCH( P.part_num, P.part_desc, P.part_ref) AGAINST( '$keywords' ) +
                MATCH( T.truck_number, T.make, T.model, T.engine, T.vin_number, T.transmission_number, T.comments) AGAINST( '$vehicle' )
                AS score
            FROM maintenance M, maintenance_parts P, truck T
            WHERE M.maintenance_id = P.maintenance_id
            AND M.some_id = T.truck_id
            AND M.type_code = 'truck'
            AND (MATCH( T.truck_number, T.make, T.model, T.engine, T.vin_number, T.transmission_number, T.comments) AGAINST( '$vehicle' )
            OR T.truck_number LIKE '%$vehicle%')
            AND MATCH( P.part_num, P.part_desc, P.part_ref) AGAINST( '$keywords' )
            AND MATCH( M.comments, M.work_done) AGAINST( '$keywords' )
            AND M.status = 'A' GROUP BY maintenance_id ORDER BY score DESC LIMIT 0, $limit";

You need to change some of your ANDs to ORs and then parenthesize the ORs.

Right now you require one of the $vehicle MATCHes and also both of the $keyword matches. Instead, I think you want to require any one of the $vehicle or $keyword matches.

Also, I think that you can combine the $keyword matches into a single MATCH call, can't you? And I think the LIKE check against T.truck_number is redundant given the MATCH that includes that column. In which case you could write that section of the SQL as:

 AND (MATCH( T.truck_number, T.make, T.model, T.engine, T.vin_number,
      T.transmission_number, T.comments) AGAINST( '$vehicle' )
     OR MATCH( P.part_num, P.part_desc, P.part_ref,M.comments, M.work_done) 
     AGAINST( '$keywords' ))

And I include the obligatory comment about protecting yourself against SQL injection by escaping the dynamic SQL you create.