How can I know how many times a data is repeated in an sql query?

Question

How can I know how many times a data is repeated in an sql query?

Navigation

#1 by (5 votes)
#2 by (1 votes)
#3 by (0 votes)

3

Good afternoon I want to know how to get the number of times that a type of data is repeated and take it to a table with php at the moment I have the following code but I do not know how to extract the number of times that a data is repeated in this consultation

this is the code:

$rs1 = mssql_query("SELECT vendedor, nombre FROM vendedor WHERE clasificacion = '1' GROUP BY vendedor, nombre ORDER BY nombre ASC", $link);
    while ($row1 = mssql_fetch_row($rs1)){
        echo "<tr value='$row1[0]'><td>$row1[1]</td></tr>";
}
$rs= mysqli_query($mysqli,"SELECT codigo_vendedor, estado,COUNT(*) as total FROM pedidos WHERE codigo_vendedor = '$row1[0]' AND DATE_FORMAT(fecha_pedido, '%Y-%m-%d') BETWEEN '$fecha_desde' AND '$fecha_hasta' GROUP BY estado");
        while ($row = mysqli_fetch_row($rs)){
      //echo "fguyhkglfjsdojgdsfkljfgknlhlkj";

            $row[0] == $row1[0];                    
            if ($row[1] == 'Logistica') {
                $row[1] = $log; 
            }elseif ($row[1] == 'Picking') {
                $row[1] = $pic;
            }elseif ($row[1] == 'Facturado') {
                $row[1] = $fac;              
            }elseif ($row[1] == 'Packing') {
                $row[1] = $pac;
            }elseif ($row[1] == 'Cartera') {
                $row[1] = $car;
            }elseif(strpos($row[1], 'Facturacion') !== false){
      $row[1] = $fact;
        }
       }
?>
 </tr>
 <td><?php echo $row1[2]?></td>
 <td><?php echo $row[3]?></td>
 <td><?php echo $log?></td>
 <td><?php echo $car?></td>
 <td><?php echo $fact?></td>
 <td><?php echo $pic?></td>
 <td><?php echo $fac?></td>
 <td><?php echo $pac?></td>

I would be very grateful with your help

mysql sql php mysqli phpmyadmin

asked by Christian Dagnover 23.09.2016 в 22:35

source

3 answers

1

This query returns the duplicate registration number on your table

   SELECT vendedor, nombre, count(*) As Reg_Duplicados FROM vendedor  GROUP BY vendedor, nombre Having Count(*) >1

answered by 24.09.2016 в 13:15

0

A simple example is to get an initialized counter at 0.

$cont = 0;

And once inside the while you do the search

if($dato == "tuPalabra") {
    $cont++;
}

And if you want to have several counters you use a switch

switch($dato) {
    case "palabra1":
        $cont1++;
         break;
    case  "palabra2":
        $cont2++;
        break;
}

The data would obviously be your row ["field"]

Then you can also by means of the sql query

answered by 23.09.2016 в 23:37

Optimize a query with a repeated subquery of MySQL Accessibility inconsistency c #

score 5 · Accepted Answer

The amount that repeats a data is brought by your query and it is not necessary to have if else , the code would be something like that, already modified so that it is displayed in a tabla

$rs= mysqli_query($mysqli,"SELECT  estado,COUNT(estado) as total FROM pedidos WHERE codigo_vendedor = '$row1[0]' AND DATE_FORMAT(fecha_pedido, '%Y-%m-%d') BETWEEN '$fecha_desde' AND '$fecha_hasta' GROUP BY estado");
     echo "<table><thead><tr><th>Estado</th><th>Cantidad</th></tr></thead <tbody>";
    while ($row = mysqli_fetch_row($rs)){
         echo "<tr><td>" . $row[0] . "</td><td>". $row[1]."</td></tr>";
    }
    echo "</tbody></table>"
   }