What Can I Do To Make This Sql Return Results In Certain Situations?

I could use some help to make 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.

Solution 1:

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.