Hello. The application sends a request for data to the server. And there is a sql query - the usual select which selects all fields. I need him to select data by a specific filter. I did not come up with anything clever, I just went through all the possible combinations and gave them the indices 0, 1, 2, 3, and so on. That is, it turns out that I will have a lot of ifov or switch case. I will have it firstly in api, secondly in the application when receiving data. How to create a smart filter to get by with just one sql query. I have never met anything like this, I don’t even know how to do that. it turns out that I will have 20 options, that is, it will only be 20 conditions and it is very stupid. Here, for example, an online store here is a bunch of filters and a bunch of options, I'm sure that they have one universal request. how to build such a query?

    2 answers 2

    Um ... I got the impression that for some reason you decided that in the where clause you can only use the equal sign. This is not true. Read for example here about this.

    In general, in this case, you need to generate the correct where for the query based on the input parameters. For example:

    where_sql += "and((type_request=$type_request and $type_request < 4) or ($type_request = 4 and type_request<4))"; .... sql = "select * from table where (1=1)" + where_sql;
    • I know that not only equality can be when using where. can you please explain what is (1 = 1)? Is this a condition that will always be fulfilled? Well, I understood the point, thank you. I'll try to do it this way - Firespirit
    • 2
      @Firespirit is so as not to cut off the first and in where_sql . - Yura Ivanov

    Monsieur definitely knows a lot ....
    you do not need to select all the fields, there is not enough data to write everything you need, but if briefly then the usual select with parameters and selection conditions, you may need a composite query
    the entire sample should go in the query, it is not necessary to additionally filter from the code and others, it is better to do all this by means of sql where

    • what is where I know. just do not understand how to write a universal request. For example, I have a field "type_request", which can take values ​​1,2,3 and for it where normally works, but if suddenly the user wants to filter at the same time on 1,2, or at the same time 2,3 and so on, then how to build a request? - Firespirit
    • you need to use logical operators .... where type_request = 1 and type_request = 2 - gadfil
    • this does not solve the problem, there will still be several conditions and several requests, but it is necessary that there be one request. For example, I get the value type_request, then the SELECT name goes, ..... FROM table WHERE type_request = '$ type_request' does not work that way. And if you do as you suggested, then there will be ify and several queries will appear: for example: if ($ type_request <4) SELECT name, ..... FROM table WHERE type_request = '$ type_request' else if ($ type_request == 4) SELECT name, ..... FROM table WHERE type_request = 1 and type_request = 2 ... such is - Firespirit
    • You can sample tables and data that you need, or in extreme cases, you need subqueries and joins. All the logic of Api in one question does not fit, read about LEFT, RIGHT, INNER, OUTER JOIN, IN - gadfil
    • the table consists of the fields coord1, coord2, type_request, price, seats here filter 1) abs (coord1- $ coord1) <5 filter 2) type_request (already described his work above) filter 3) price> = $ price filter 4) seats> = $ seats - Firespirit