SQL does not return all results

Question

SQL does not return all results

Navigation

#1 by (0 votes)

3

I have 2 tables in MySQL, one called municipalities and another called municipios_ibge both have a column named municipio that holds the municipality of each city of Brazil. I have to do the following: I need to check if the municipalities of the municipios_ibge table have in the table municipalities and if so, list it then on the screen.

Now instead of listing the name of the municipality I am listing the same command using to make WHERE because in the tables there are more than 5 thousand records and it returns me just under 20 records on the screen being in the bank by the less than 70% of the data is equal to both tables.

When I do the command SELECT * FROM municipios WHERE municipio LIKE '%Alto Alegre%' in phpmyadmin it returns me data, but when the script I'm using it does not return anything.

My code

<?php

$estados_list = array("AC"=>"Acre", "AL"=>"Alagoas", "AM"=>"Amazonas", "AP"=>"Amapá","BA"=>"Bahia","CE"=>"Ceará","DF"=>"Distrito Federal","ES"=>"Espírito Santo","GO"=>"Goiás","MA"=>"Maranhão","MT"=>"Mato Grosso","MS"=>"Mato Grosso do Sul","MG"=>"Minas Gerais","PA"=>"Pará","PB"=>"Paraíba","PR"=>"Paraná","PE"=>"Pernambuco","PI"=>"Piauí","RJ"=>"Rio de Janeiro","RN"=>"Rio Grande do Norte","RO"=>"Rondônia","RS"=>"Rio Grande do Sul","RR"=>"Roraima","SC"=>"Santa Catarina","SE"=>"Sergipe","SP"=>"São Paulo","TO"=>"Tocantins");

$conexao = new PDO('mysql:host=localhost;dbname=alb', 'root', 'root', array(PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES utf8"));

$municipios_ibge = $conexao->query("SELECT * FROM municipios_ibge ORDER BY municipio");

while($dados = $municipios_ibge->fetch(PDO::FETCH_ASSOC)){

  $municipio = trim($dados['municipio']);

  $municipios = $conexao->query("SELECT * FROM 'municipios' WHERE TRIM('municipio') LIKE '%$municipio%' ORDER BY 'municipio' ASC");

  if($municipios->rowCount() > 0){

    echo "SELECT * FROM 'municipios' WHERE TRIM('municipio') LIKE '%$municipio%' ORDER BY 'municipio' ASC".'<br />';
  }
}


?>

What it returns:

SELECT * FROM 'municipios' WHERE TRIM('municipio') LIKE '%Agronômica%' ORDER BY 'municipio' ASC
SELECT * FROM 'municipios' WHERE TRIM('municipio') LIKE '%Alto Bela Vista%' ORDER BY 'municipio' ASC
SELECT * FROM 'municipios' WHERE TRIM('municipio') LIKE '%Anita Garibaldi%' ORDER BY 'municipio' ASC
SELECT * FROM 'municipios' WHERE TRIM('municipio') LIKE '%Arabutã%' ORDER BY 'municipio' ASC
SELECT * FROM 'municipios' WHERE TRIM('municipio') LIKE '%Arroio Trinta%' ORDER BY 'municipio' ASC
SELECT * FROM 'municipios' WHERE TRIM('municipio') LIKE '%Arvoredo%' ORDER BY 'municipio' ASC
SELECT * FROM 'municipios' WHERE TRIM('municipio') LIKE '%Atalanta%' ORDER BY 'municipio' ASC
SELECT * FROM 'municipios' WHERE TRIM('municipio') LIKE '%Aurora%' ORDER BY 'municipio' ASC
SELECT * FROM 'municipios' WHERE TRIM('municipio') LIKE '%Bandeirante%' ORDER BY 'municipio' ASC
SELECT * FROM 'municipios' WHERE TRIM('municipio') LIKE '%Barra Bonita%' ORDER BY 'municipio' ASC
SELECT * FROM 'municipios' WHERE TRIM('municipio') LIKE '%Capão Alto%' ORDER BY 'municipio' ASC
SELECT * FROM 'municipios' WHERE TRIM('municipio') LIKE '%Chapadão do Lageado%' ORDER BY 'municipio' ASC
SELECT * FROM 'municipios' WHERE TRIM('municipio') LIKE '%Cordilheira Alta%' ORDER BY 'municipio' ASC
SELECT * FROM 'municipios' WHERE TRIM('municipio') LIKE '%Cunhataí%' ORDER BY 'municipio' ASC
SELECT * FROM 'municipios' WHERE TRIM('municipio') LIKE '%Dona Emma%' ORDER BY 'municipio' ASC
SELECT * FROM 'municipios' WHERE TRIM('municipio') LIKE '%Formosa do Sul%' ORDER BY 'municipio' ASC
SELECT * FROM 'municipios' WHERE TRIM('municipio') LIKE '%Forquilhinha%' ORDER BY 'municipio' ASC
SELECT * FROM 'municipios' WHERE TRIM('municipio') LIKE '%Fraiburgo%' ORDER BY 'municipio' ASC

It returns only this data from a table that has more than 5,000 records and at least 4,000 are the same from both tables.

php mysql pdo phpmyadmin

asked by anonymous 15.08.2016 / 01:28

1 answer

"java.lang.SecurityException: Permission Denial" on Android 6 Marshmallow (API 23) Replace a variable within a Word file

score 0 · Accepted Answer

Try changing your query so that it looks at what's in the other table instead of making those loops without need.

SELECT * FROM municipios M
 WHERE  exists ( 
                SELECT * FROM municipios_ibge Mibge
                where  Mibge.municipio like M.municipio
                )
ORDER BY municipio ASC

Obs; To use Like with a column from another table, you need the use of the CONCAT () function;

SELECT * FROM @municipios M
 WHERE  exists ( 
                SELECT * FROM @municipios_ibge Mibge
                where  Mibge.municipio LIKE CONCAT('%', M.municipio, '%') 
                )
ORDER BY municipio ASC