Return least repeating records

Question

Return least repeating records

Asked 5 years, 5 months ago

Viewed 48 times

1

I have a table registro structured 'id(AI)', 'b1(INT 2)', 'b2(INT 2)', 'b3(INT 2)', 'b4(INT 2)', 'b5(INT 2)' which receives the values entered in the FORM of the insert.php page, example how data is saved:

'ID=1','12','13','6','7','2' 'ID=2','6','7','1','7','15'

on the table classes with the structure 'id(AI)', 'nome(TEXT)' already have the data entered as pre '1,a','2,b','3,c'...'20,t' respectively.

I am trying to count how many times the ID’s as they are inserted repeat and always list the 10 that repeat less.

My application is PHP, the index.php page is like this:

<?php session_start();
include_once("conexao.php");
date_default_timezone_set('America/Sao_Paulo');
$date = date('d-m-Y');
$datec = date('Y-m-d');
?>
<!doctype html>
<html lang="pt-br">
  <head>
   <!-- Required meta tags -->
   <meta charset="utf-8">
   <meta name="viewport" content="width=device-width, initial-scale=1, shrink-to-fit=no">
 <title>estudo teste</title>
  <link rel="stylesheet" href="../adm/assets/libs/css/style.css">
  <link rel="stylesheet" href="../adm/assets/vendor/bootstrap/css/bootstrap.min.css">
 </head>
<body>
<?php 

$query = "SELECT * FROM classes ORDER BY id ASC";
$resultado = mysqli_query($conectar, $query);
while($linhas = mysqli_fetch_assoc($resultado)){
$id = $linhas['id'];
$nome = $linhas['nome'];

$total = "SELECT * FROM registros WHERE b1='$id' OR b2='$id' OR b3='$id' OR b4='$id' OR b5='$id'";
$resultado_total = mysqli_query($conectar, $total);
$total = mysqli_num_rows($resultado_total);

if($total != ''){
echo $nome." / ".$total."<br>   ";
}}

?>
 </body>
</html>

at the moment I can only count how many times it repeats itself, the intention is to return more or less like this:

Resultado ID 12-i = 1
Resultado ID 01-a = 1
Resultado ID 07-g = 2 

...

edited with the information you requested .

– Ronaldo de Lima

2019/01/31 at 00:03

1 answer

Browser other questions tagged php database

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

by Sorack • **27,430** points · Answer 1 · 2019-01-31T00:35:38+00:00

Your example does not match the description of the problem, but I deduce that the result you want can be achieved by creating an auxiliary table of numbers, which will have the possible values:

CREATE TABLE numeros (
  numero VARCHAR(2) NOT NULL
);

INSERT INTO numeros(numero)
VALUES('01'), ('02'), ('03'), ('04'), ('05'),
      ('06'), ('07'), ('08'), ('09'), ('10'),
      ('11'), ('12'), ('13'), ('14'), ('15'),
      ('16'), ('17'), ('18'), ('19'), ('20');

Then make a UNPIVOT of your table registros to generate a row for each column b?:

SELECT r.b1 AS numero FROM registros r
UNION ALL
SELECT r.b2 AS numero FROM registros r
UNION ALL
SELECT r.b3 AS numero FROM registros r
UNION ALL
SELECT r.b4 AS numero FROM registros r
UNION ALL
SELECT r.b5 AS numero FROM registros r

After that make a INNER JOIN with the auxiliary table numeros you previously created, using the aggregation function COUNT to count the amount of records for each number and a ORDER BY for that quantity. Then use the clause LIMIT to limit to 10 records (which is what you had specified in the question):

SELECT x.numero,
       COUNT(1) AS quantidade
  FROM (SELECT r.b1 AS numero FROM registros r
        UNION ALL
        SELECT r.b2 AS numero FROM registros r
        UNION ALL
        SELECT r.b3 AS numero FROM registros r
        UNION ALL
        SELECT r.b4 AS numero FROM registros r
        UNION ALL
        SELECT r.b5 AS numero FROM registros r) x
      INNER JOIN numeros n ON n.numero = x.numero
 GROUP BY x.numero
 ORDER BY 2, x.numero
 LIMIT 10

The result can be found in SQL Fiddle.

Applying the example to your code we will have something similar to the following:

<?php
  session_start();
  include_once("conexao.php");
  date_default_timezone_set('America/Sao_Paulo');
  $date  = date('d-m-Y');
  $datec = date('Y-m-d');
?>
<!doctype html>
<html lang="pt-br">
  <head>
   <!-- Required meta tags -->
   <meta charset="utf-8">
   <meta name="viewport" content="width=device-width, initial-scale=1, shrink-to-fit=no">
 <title>estudo teste</title>
  <link rel="stylesheet" href="../adm/assets/libs/css/style.css">
  <link rel="stylesheet" href="../adm/assets/vendor/bootstrap/css/bootstrap.min.css">
 </head>
<body>
<?php
  $query = "SELECT x.numero,\n";
  $query.= "       COUNT(1) AS quantidade\n";
  $query.= "  FROM (SELECT r.b1 AS numero FROM registros r\n";
  $query.= "        UNION ALL\n";
  $query.= "        SELECT r.b2 AS numero FROM registros r\n";
  $query.= "        UNION ALL\n";
  $query.= "        SELECT r.b3 AS numero FROM registros r\n";
  $query.= "        UNION ALL\n";
  $query.= "        SELECT r.b4 AS numero FROM registros r\n";
  $query.= "        UNION ALL\n";
  $query.= "        SELECT r.b5 AS numero FROM registros r) x\n";
  $query.= "      INNER JOIN numeros n ON n.numero = x.numero\n";
  $query.= " GROUP BY x.numero\n";
  $query.= " ORDER BY 2, x.numero\n";
  $query.= " LIMIT 10";

  $resultado = mysqli_query($conectar, $query);

  while ($linhas = mysqli_fetch_assoc($resultado)) {
    $numero     = $linhas['numero'];
    $quantidade = $linhas['quantidade'];

    echo $numero . " / " . $quantidade . "<br>   ";
  }
?>
 </body>
</html>