Value for "any" in SQL query

Why not simply remove those conditions from the query?

SELECT id,name,organizer_id,no_pages,publication_date,price,currency  FROM reports AS T1, sector_index_reports AS T2, country_index_reports AS T3 WHERE 
(T1.id=T2.id_obiekt) AND (T1.id=T3.id_obiekt)  AND  name LIKE '%construction%' ORDER BY date_published DESC 

Select allOpen in new window

Hey, thanks for reply.
I cant remove it because is conditioned to other searching posibilities  by the user, so this parameter must to exist.

It´s  a search for documents. If the user makes a simple search, without using the filters,
the query should process only the "keyword", where categories and regions will have no value. But its  also possible to make a filtered search, where categories and/ or regions may have a value.

conditioned to other searching possibilities  by the user, so this parameter must to exist.

No, it does not have to exist.  Basically you would create the query string that works based on whatever the client put into the request.  This means sometimes you would start with different query strings.

You might try replacing such a test with something like WHERE 1=1 which gives a universal match.

No, it does not have to exist.

So, how do I enter the value for categories and regions into the query, when the user select one or both of them?

You write program logic to determine what the user requested and what the corresponding query should be.  It's not rocket science, but it's detailed work.  A list of all the possible combinations of client request variables would be useful.  Then you could make a matrix of the queries that will be responsive to the request variables.  Almost certainly these queries would be different in many cases (choosing different tables, different columns, etc.) and you would have variable data to insert into the base queries.

list of all the possible combinations of client request variables would be useful.

I thought in this:

T2.id_cat = the sum of all existing ID´S of the sectors
T3.id_cat = the sum of all existing ID´S of the regions

Basically, it would give me the same result that Tx.idcat="any", with the difference that would be lot of extra code.

If the user has specified a category for search, include the parameter in the query. If not, then don't include the parameter in the query.

You'll need to do this where you're building the query; not in sql itself.

... lot of extra code

Often that''s the right way to write programs! Trying to be too efficient in the way you write your code will invariably lead to run-time errors.  Trust me, I've done that and it never worked out correctly.  

What has worked for me is the matrix of requests and queries.  

You can do this in OOP programming very easily.  Each query is built by a separate method of the class. Feed the request to the constructor, which contains the matrix.  The constructor analyzes the request against the matrix and calls the method that generates the appropriate query.  If there is no method to match the query, the constructor returns FALSE.  If a method has created a query, it stores the query as an object property that you can retrieve.

The advantage of this structure is that it can be mocked up and built up in layers as you think of new ways to query the data base.  You can test it with mock objects.  And perhaps most importantly, there will only be this much code needed in your main script.

$obj = new QueryBuilder($_GET);
if ($obj) 
{
    $sql = $obj->query;
}
else
{
    trigger_error("Cannot build query", E_USER ERROR);
}

Select allOpen in new window

If the user has specified a category for search, include the parameter in the query. If not, then don't include the parameter in the query.

This is the version that I have actually. Using If /else, but in this way I need to use 12 queries for the possible options, and in the way I try to do now would be only 3 queries

If you need 12 queries, don't try to do it with 3 queries.  Just sayin'

But really there is not a symbol like (*) that will say to the query


 WHERE this=anything?

@_PJ_, you're taking the longest and slowest possible approach to learning PHP and MySQL.  It doesn't have to be this hard.  Trial and error is just too much trouble.  Please consider getting this book and using it.  And when a new edition comes out, throw the old one away and get the new one.

In SQL the presence of a WHERE clause is intended to reduce the results set.  It follows that the omission of a WHERE clause means "this=anything."  It's been that way as long as I can remember.

I know it may look "not wise" to use "this=anything" after a WHERE. But this is my idea, in a way to keep the same structure to the 4 possible combinations that each case has, to avoid writing it 12 times.

But I will see, In general all the search is working properly now, I just want to reduce the time of the results and optimize functions or code is not completely necessary.

Result time is reduced by eliminating unwanted baggage from a query. You cannot expect to reduce any result time while keeping a "this=anything" in the query.

See AntiPractice #26.

greetings   _PJ_ , , You are looking at this problem from a view that you can not find understanding.
The main point here is that your Query is a PHP string, and strings are easy to change in PHP,

you say - "I cant remove it because is conditioned", it is true that you may need that query filter condition, but you are making a STRING value for the query, SO you can change the string.

$conditions = ''; // start with an empty string
$conditions .= (empty($_POST['id_cat2'])) ? '' : "  AND  T2.id_cat ='".$_POST['d_cat2']."' "; // if there is Value add the AND to sql string
$conditions .= (empty($_POST['id_cat3'])) ? '' : "  AND  T3.id_cat ='".$_POST['d_cat3']."' ";

$queryStr = "SELECT id,name,organizer_id,no_pages,publication_date,price,currency  FROM reports AS T1, sector_index_reports AS T2, country_index_reports AS T3 WHERE 
(T1.id=T2.id_obiekt) ".$conditions." AND (T1.id=T3.id_obiekt)  AND  name LIKE '%construction%' ORDER BY date_published DESC "

// now do the mysqli query here

Select allOpen in new window

At least this is the way I would do it.
I hope this gives you some IDEA about this, I left out the important SQL string escaping with mysqli_real_escape_string (  )  to keep this simple.

See also this excellent article from one of our colleagues here at EE:
https://www.experts-exchange.com/Database/MySQL/A_1250-3-Ways-to-Speed-Up-MySQL.html