What would be the correct way to know and display the number of records in a table that meet a certain condition?
I have tried the following to show the number of records without any condition but it doesn't show anything.
<?php
$sql = "SELECT * FROM avisos";
$result = mysql_query($sql);
$numero = mysql_num_rows($result);
echo 'Número de total de registros: ' . $numero;
?>
Count
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 records in a table:
This query will return a column (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 records whose
id
is greater than7
: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 tomysqli
or toPDO
, since with this function the security of the data could be compromised and because this code will not work as of PHP 7.► Using
mysql
You can use
mysql_fetch_assoc
:► Using
mysqli
The passage from
mysql
amysqli
is almost imperceptible.You would create the connection like this:
$mysqli
would be a connection object that you'll use later in the code.And to run the same code as above, you have two possibilities:
A. Procedural mode
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 current times.
NOTE: Generally the results with the methods
fetch
are read within a loopwhile
in which the set of results returned by the query is traversed. Looping is not used here because a single row is expected in the results and in those cases it can be accessed without the need to iterate through the results in a loop.► Using
PDO
PDO
has the methodfetchColumn()
, which we can use for cases where we expect a single column as the result.Grades
Consider the following option to count by groups, that is, if you have a clothing database, count how many clothes you have by type of clothing, such as shirts, pants, etc...
$sql = " select
ropa
, COUNT(*) frominventario
group byropa
;