SQL does not return all results

Asked

Viewed 270 times

3

I have 2 tables in Mysql, a call municipalities and another call municipios_ibge both have a column called municipality that guards the municipality of each city in Brazil. I have to do the following: I need to check the municipalities in the table municipios_ibge has on the table municipalities and if you have, list it then on the screen.

Currently instead of listing the name of the municipality I am listing the same command using to do the WHEREbecause the tables have more than 5 thousand records and it returns me just under 20 records on the screen and in the database at least 70% of the data are equal of both tables.

When I make the command SELECT * FROM municipios WHERE municipio LIKE '%Alto Alegre%'in phpmyadmin it returns data to me, but when the script I’m using does it returns nothing.

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 he 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 me only this data from a table that has more than 5 thousand records and at least 4 thousand are equal of both tables.

php mysql pdo phpmyadmin

  • Gives a Count(); in the variable: $municipios_ibge to see how many records it is bringing in this first query.

  • @Kayobruno he returns 5732

  • Around and around these problems of fucnionar in phpmyadmin and not in PHP appear, try to replace your , by single or double quotes, the only operation I use the severe signal is in the inserts, the rest are single quotes, at least in my programs work

  • There are two things I didn’t understand , so you use the $estados_list list ? and so you use the TRIM in TRIM(municipio) ?

1 answer

0


Try changing your query so that it sees what’s in the other table instead of doing these loops with no needs.

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 of another table is required 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

Browser other questions tagged php mysql pdo phpmyadmin

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