Query using like operator

Question

In a query in the database I inform the keywords, the month and the year. I want to get the data according to the keywords, the month and the year. For example: the word school on date 2015-08.

For this, I use the operator like instead of = because the date is complete and I want to do the search per month in a given year. The same thing goes for the keyword, because I may have other words than the word school on record.

The query is:

select * from despesas where palavraChave like "%escola%" or data like '2015-08%'

The mistake is:

If I pass no date and no word, it brings me the data and I don’t want it. I just want the data if one of the fields is found. I’ve used !null, !notnull, things like, but it doesn’t solve.

The language is sql and the server language is php.

Answer 1

1)Check whether variables have value or not before executing the query.

2)If the check cannot be performed do the following sql

select * from despesas where (palavraChava like "%escola%" and "%escola%" <> "%%") or (data like '2015-08%' and '2015-08%' <> '%')

Explaining better:

where I put "%escola%" <> "%%" is because I imagine the way you mount sql is as follows: palavraChave like "%{variavel}%" so if the variable is not filled the chunk will be "%%" <> "%%".

The same logic goes to the date field.

Answer 2

If I understand what you need, try this query.

SELECT * FROM despesas WHERE palavraChave LIKE '%escola%' AND Month(Data) = 8 AND Year(Data) = 2015

I’ve never seen it do LIKE in date field unless varchar. But still, work with date in the field varchar only mess.

Answer 3

You can use it LIKE to recover a date in your database because DATETIME or DATE sane strings, although it is more correct to use the commands used above by others.

You just have to do it like this

SELECT * FROM despesas WHERE palavraChave LIKE '%escola%' OR data LIKE '%2015-08%';

You need to use the symbol of % before the year and after the month or if you only want the month, use before and after the month LIKE '%-08-%', put the - before and after the month, otherwise you risk taking day and years that contain this value.

That way he only gets the month.

No need to use double quotes in text fields, single quotes is the most correct and most used.

If you use the OR, it will return you everything that has a school keyword and any date and everything that dates back to August 2015 and any key word, so if it is to do a search like this, it would be easier using only the date or keyword.

`SELECT * FROM despesas WHERE palavraChave LIKE '%escola%';`

or SELECT * FROM despesas WHERE data LIKE '%2015-08%';

Both SELECTS will return exactly the same thing as your query.

I believe you want your query to return the data where the keyword is school and the date is August 2015. That way, use the AND

`SELECT * FROM despesas WHERE palavraChave LIKE '%escola%' AND data LIKE '%2015-08%';`

Answer 4

Analyzing your query:

select * from despesas where palavraChave like "%escola%" or data like '2015-08%'

Are you telling SQL:

Please give me all Tablea records expenses, where the cuss it’s like "school" no matter the date, or where the date it’s like '2015-08' no matter what the wordChave.

That is, you are asking too much - using the AND, thus:

select * from despesas where palavraChave like '%escola%' AND data like '2015-08%'

You’re saying:

Please give me all records of the expenses table, where the wordChave is like "school" and the expense has the date shown with "2015-08". The others (records with another wordChave and others on another date) are excluded.

Sqlfiddle

Here is a Sqlfiddle to demonstrate the difference - use a OR and then use a AND and Run SQL to see the difference.