Value for "any" in SQL query

Hello,

I have a query for a search engine that I´m trying to optimize. What I need is to know which is the symbol for "any result" in the query, I´m actually using the % symbol, but is not working

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 
T2.id_cat ='%' AND  (T1.id=T2.id_obiekt) AND  T3.id_cat ='%' AND (T1.id=T3.id_obiekt)  AND  name LIKE '%construction%' ORDER BY date_published DESC 

Open in new window


I need to make the  query work when (T2.id_cat =) and (T3.id_cat =) when it has any value.

Thanks in advance
Klaus AndersenAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

shishir_sriCommented:
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 

Open in new window

0
Klaus AndersenAuthor Commented:
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.
0
Ray PaseurCommented:
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.
0
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

Klaus AndersenAuthor Commented:
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?
0
Ray PaseurCommented:
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.
0
Klaus AndersenAuthor Commented:
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.
0
shishir_sriCommented:
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.
0
Ray PaseurCommented:
... 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);
}

Open in new window

0
Klaus AndersenAuthor Commented:
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
0
Ray PaseurCommented:
If you need 12 queries, don't try to do it with 3 queries.  Just sayin'
0
Klaus AndersenAuthor Commented:
But really there is not a symbol like (*) that will say to the query


 WHERE this=anything?
0
Ray PaseurCommented:
@_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.
0
Klaus AndersenAuthor Commented:
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.
0
shishir_sriCommented:
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.
0
Ray PaseurCommented:
0
Slick812Commented:
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

Open 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.
0
PortletPaulfreelancerCommented:
>> the search is working properly now, I just want to reduce the time of the results
reducing the time for results generally means avoiding innefficiencies - trying to look for "anything" is inefficient - so it would have the reverse effect of making your time to results longer. As a "general rule" the more you put into an SQL where clause the more time and effort will be devoted to meeting the request. i.e. more = slower . So it is sensible to avoid doing things that you don't really need to do: especially conditions which might by themselves be very inefficient such as "this=anything"

Ray is pointing you toward the most efficient method, which is to formulate the optimum query for each set of parameters before sending that query to the database.

----------------------
From a purely SQL perspective, you could achieve 'anything' but not the way you are approaching it.

A practical alternative is to couple conditions together, let's say you have a parameter for book title (I will call this @title) and a table of book information (call this `books`) your query might then be:

select isbn, title, author from`books` where (@title is null OR `books`.`title` = @title) ;

by using the parentheses I have 'coupled' 2 conditions with OR,
so either of these can be true and the query will operate, e.g.

 if @title = 'Catch 22' then the query will do this:

select isbn,title,author from `books` where ( 'Catch 22' is null OR `books`.`title`= 'Catch 22' );

& from that query you would to only get the book 'Catch 22'

if however @title is null then the query would do this:

select  isbn, title, author from `books` where ( null is null OR `books`.`title` =  null ) ;

here, because null really is null, then you have the effect of 'anything'.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Ray PaseurCommented:
See also this excellent article from one of our colleagues here at EE:
http://www.experts-exchange.com/Database/MySQL/A_1250-3-Ways-to-Speed-Up-MySQL.html
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
PHP

From novice to tech pro — start learning today.