在某些情况下，我该怎么做才能使此SQL返回结果? [英] What can I do to make this SQL return results in certain situations?

问题描述

在某些情况下，我可以使用一些帮助来使此搜索返回结果.用户有2个字段要填写，我希望可以保留为空白或同时填写两者.字段是车辆和关键字.车辆字段旨在允许基于品牌，型号，VIN，卡车编号(通常是2-3位数字或字母前缀后跟2位数字)以及属于卡车表的其他几个字段进行搜索.关键字旨在搜索maintenance和maintenance_parts表中的大多数字段(诸如工作描述，零件名称，零件编号之类的内容).当前保留关键字字段为空，即使车辆字段本身具有有效结果也不会返回任何结果.

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. 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";

推荐答案

您需要将一些AND更改为OR，然后在OR中加上括号.

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

现在，您需要$ vehicle匹配之一，也需要$ keyword匹配中的两者.相反，我认为您需要$ vehicle 或 $ keyword匹配项中的任何一个.

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.

此外，我认为您可以将$ keyword匹配项组合成一个MATCH调用，不是吗?而且我认为，鉴于包含该列的匹配，对T.truck_number的LIKE检查是多余的.在这种情况下，您可以将SQL的该部分写为:

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' ))

此外，我还附有关于通过逃避创建的动态SQL来保护自己免受SQL注入的强制性评论.

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

这篇关于在某些情况下，我该怎么做才能使此SQL返回结果?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！