query sql does not work

Asked

Viewed 244 times

0

My system is generating a query from a filter. All query that I use in a given field do not find anything, as simple as the query down.

select * from prt_license WHERE organization_type = 'commercial';
//testei na ide de banco de dados e da retorno vazio

How much the system generates, which are querys similar to the below:

select sql_calc_found_rows id_license, organization_name
       , organization_type, edition, licensed_version
       , updates_expiry_date, support_expiry_date, issued_date
       , users_licensed, advanced_clients_licensed, razao_social
from  license_report_view
where ativo = 1 
      and organization_type in ('commercial' , 'professional') 
      and users_licensed between 5 and 1000
order by id_license asc limit 0, 10

I have put in minuscule and capital, but nothing works. The field is a varchar of 100.

I have no idea why it is not working, since there is another table that also used the same technique that worked perfectly.

Upshot

inserir a descrição da imagem aqui

Result with the second query

inserir a descrição da imagem aqui

php mysql

  • of a Trim in value and column.

  • Cara.. I take the value of an external form, and an array of an sent ajax is generated, treat this array and Gero a new array to be compared. , $sWhere .= " AND organization_type IN ('". implode("' , '", $query["tipo_de_licenciamento"])."') "; The query like this, but in another form, it worked and this is not giving

  • Made a distinct to confirm whether these records exist? The suggestion of trim works if it’s just a matter of white character, but will not solve if there are characters ghosts.

  • How so disctinct for this AND?

  • Given the circumstances, it seems that these 'commercial' and 'professional' values do not actually exist in your table. So do: select distinct(organization_type), len(organization_type) from license_report_view and check if it contains the correct number of characters: commissary = 10

  • But there is...

  • I’ll put a print of the result, pera

  • It has two more characters. O trim (as rray said) should have worked. Try again select distinct(organization_type), len(organization_type), len(trim(organization_type)) from license_report_view.

  • even with Trim continued the same thing... which would be this ghost character?

  • Do so: select * from license_report_view where organization_type like '%\0%'; I don’t have Mysql here to test.

  • You didn’t call me back... I’ll put the other print here

  • Let’s go continue this discussion in chat.

  • I don’t have access. Blocked here in the company. I found this post on Soen, follow the steps there to find these two characters that are the most. How can I find non-ASCII characters in Mysql?

Show 8 more comments

2 answers

1

do not know which bank your commands can change but follow example:

select * from prt_license WHERE TRIM(UPPER(organization_type)) = TRIM(UPPER('commercial'));

  • I need to test like an or... so I have to use the IN anyway

  • And even doing so... still returning nothing

  • I need the IN function, because it comes from an array, as mentioned above, so it has to be something like: '$sWhere .= " AND organization_type IN ('". implode("' , '", $query["typo_de_licensing"])."') ";'

1


Your problem is not in the select of your database but rather in the way you are reading your txt file. Use the function trim($arquivo) php to clear whitespace.

Due to the storage of blank spaces your select can not find any results, so always to test can use the command length sql to check the size of your varchar.

Browser other questions tagged php mysql

You are not signed in. Login or sign up in order to post.