Count and show number of records that meet a condition


Good, as it would be the correct way to know and show the number of records in a table that meet a certain condition. I have tried with the following to show the number of records without any condition but it shows nothing.

$sql = "SELECT * FROM avisos";
$result = mysql_query($sql);
$numero = mysql_num_rows($result);
echo 'Número de total de registros: ' . $numero;
asked by Tefef 27.01.2018 в 10:51

1 answer



To count the records in a table according to certain conditions there are several ways to do it. The most common is using COUNT(*) .

For example, if you want to count all the records in a table:

SELECT COUNT(*) total FROM tabla;

This query will return a column (to which we have given the alias total ), with the number of rows that exist in the table.

The use of criteria is applied as usual, since it is a query like any other.

For example, if you want to count the records whose id is greater than 7 :

SELECT COUNT(*) total FROM tabla WHERE id>7;

Any criteria can be applied, as is usually done.

PHP Code

Regarding the PHP code, I answer your question indicating how it would be done with the extension mysql_* , knowing that it has been declared obsolete 1 . It is recommended to pass to mysqli or to PDO , since with this function the security of the data could be compromised and because this code will not work from PHP 7.

► Using mysql

You can use mysql_fetch_assoc :

$sql = "SELECT COUNT(*) total FROM avisos";
$result = mysql_query($sql);
$fila = mysql_fetch_assoc($result);
echo 'Número de total de registros: ' . $fila['total'];

► Using mysqli

The step from mysql to mysqli is almost imperceptbile.

You would create the connection like this:

$mysqli = new mysqli("localhost", "usuario", "contraseña", "basedatos");

$mysqli would be a connection object that you will use later in the code.

And to execute the same code above, you have two possibilities:

A. Procedural mode

$sql = "SELECT COUNT(*) total FROM avisos";
$result = mysql_query($mysqli, $sql);
$fila = mysqli_fetch_assoc($result);
echo 'Número de total de registros: ' . $fila['total'];

B. Object oriented mode

Although the procedural mode is very similar to the usual way of using it, due to the old extension, I would recommend that you learn the object-oriented style. It is more modern and adapted to modern times.

$sql = "SELECT COUNT(*) total FROM avisos";
$result = $mysqli->query($sql);
$fila = $result->fetch_assoc();
echo 'Número de total de registros: ' . $fila['total'];

NOTE: Generally the results with fetch methods are read within a while loop in which the result set returned by the query is scanned. The loop is not used here because in the results a single row is expected and in those cases it can be accessed without having to loop the results in a loop.

► Using PDO

PDO has the method fetchColumn() , which we can use for cases in the that we expect a single column as a result.

$sql = "SELECT COUNT(*) total FROM avisos";
$result = $pdo->query($sql); //$pdo sería el objeto conexión
$total = $result->fetchColumn();
echo 'Número de total de registros: ' . $total;

1. For more details you can consult the question: Why should not the mysql_ * API be used in PHP / MySQL?

answered by 27.01.2018 / 13:08