How many records are there in mysql table. Selecting and counting rows in one query - SQL_CALC_FOUND_ROWS
To determine the number of records in a MySQL table, you need to use the special COUNT() function.
The COUNT() function returns the number of records in a table that match a given criterion.
The COUNT(expr) function always counts only those rows for which the result of expr is NOT NULL .
An exception to this rule is the use COUNT functions() with an asterisk as an argument - COUNT(*) . In this case, all rows are counted, regardless of whether they are NULL or NOT NULL.
For example, the COUNT(*) function returns the total number of records in the table:
SELECT COUNT(*) FROM table_name
How to count the number of records and display them on the screen
Example PHP+MySQL code for counting and displaying the total number of rows:
$res = mysql_query("SELECT COUNT(*) FROM table_name") $row = mysql_fetch_row($res); $total = $row; // total records echo $total; ?>
This example illustrates the simplest use of the COUNT() function. But you can also perform other tasks using this function.
By specifying a specific table column as a parameter, the COUNT(column_name) function returns the number of records in that column that do not contain a NULL value. Posts from NULL values are ignored.
SELECT COUNT(column_name) FROM table_name
The mysql_num_rows() function cannot be used because in order to find out the total number of records, you need to run a SELECT * FROM db query, that is, get all the records, and this is not desirable, so it is preferable to use the count function.
$result = mysql_query("SELECT COUNT (*) as rec FROM db");
Using the COUNT() function as an example
Here is another example of using the COUNT() function. Let's say there is a table ice_cream with an ice cream catalog, which contains category identifiers and ice cream names.
Starting from version 4.0, the MySQL DBMS has a rather convenient ability to count the number of all records that match a query, when the number of records is limited by LIMIT. When working with database searches, as well as when selecting from tables with big amount records, such functionality is simply necessary.
Syntax. In a SELECT query, you must specify the SQL_CALC_FOUND_ROWS option before the list of columns. Here's the beginning of the SELECT syntax.
SELECT
select_expr, … …
Thus, when executing the SELECT SQL_CALC_FOUND_ROWS query, the DBMS will count the total number of rows that match the query condition and store this number in memory. Naturally, the SELECT SQL_CALC_FOUND_ROWS query only makes sense when using a limit (LIMIT). Immediately after executing the selection query, to obtain the number of records, you need to execute another SELECT query: SELECT FOUND_ROWS ();. As a result, MySQL will return one row with one field in which the number of rows will be stored.
An example of the requests themselves:
> SELECT SQL_CALC_FOUND_ROWS * FROM tbl_name WHERE number > 100 LIMIT 10;
> SELECT FOUND_ROWS();
The first query will return (output) 10 rows of table tbl_name for which the condition number > 100 is true. The second call to the SELECT command will return the number of rows that the first SELECT command would have returned if it had been written without the LIMIT expression. Although using the SELECT SQL_CALC_FOUND_ROWS command requires MySQL to recalculate all rows in the result set, it is still faster than without LIMIT because it does not have to send the result to the client.
Example requests from PHP:
$result = mysql_query("SELECT SQL_CALC_FOUND_ROWS * FROM table1 LIMIT 0, 10″, $link);
while ($row = mysql_fetch_assoc($result))
{
var_dump($row);
}$result = mysql_query("SELECT FOUND_ROWS()", $link);
$num_rows = mysql_result($result, 0);
echo "$num_rows Rows\n";
As a result of executing the code, provided that $link points to an open connection to the DBMS, PHP will print 10 rows from the table table1 , and then an integer value of the number of rows that match the query (ignoring LIMIT).
In UNION queries, SQL_CALC_FOUND_ROWS can behave in two ways due to the fact that LIMIT can appear in multiple places. Row counting can be done for individual SELECT queries, or for the entire query after merging.
The purpose of SQL_CALC_FOUND_ROWS for UNION is that it should return the number of rows that would be returned without a global LIMIT. The conditions for using SQL_CALC_FOUND_ROWS with UNION are listed below:
- The SQL_CALC_FOUND_ROWS keyword must be specified in the first SELECT statement.
- The value of FOUND_ROWS() will only be accurate if UNION ALL is used. If UNION is specified without ALL, duplicate elimination occurs and the value of FOUND_ROWS() will only be approximate.
- If LIMIT is not present in the UNION, then SQL_CALC_FOUND_ROWS is ignored and the number of rows in the temporary table that is created to execute the UNION is returned.
From the author: one of my friends claims that Santa Claus exists. He believed in him after his youngest son New Year presented 10 kg of chocolates. Now next year we will have to ask Santa Claus for fillings for all family members! Fortunately, in web programming you can play it safe against such surprises and determine the number of rows in MySQL in advance.
Why know how much?
Any table consists of columns and rows. The amount stored in the database depends on how many rows are contained in each of its tables.
Just as the number of holes in a person’s teeth depends on the amount of candy previously eaten. More precisely, from the total weight of sweets that were “gifted” to your child for the New Year.
It is clear that from a large number of lines in MySQL database There won’t be more fillings in your mouth, but knowledge of this parameter may still be needed when optimizing the operation of your site, to run some scripts, etc.
Easily!
To get the number of rows in MySQL, you don’t have to be smart about composing SQL queries. Simply open the desired table in the program shell and look at the id of the last row. It is this column that is most often used as a unique key, and its value is generated automatically (auto-increment) by incrementing one (+1) to the previous one.
To prove this “axiom”, let’s open a table in any database in phpMyAdmin. For example, the table animal. As you can see in the screenshot, the value of the id column goes in order, from 1 to... So, set it aside! Where did the cat with “identification number” 4 run off to? He was probably scared of the “dogs” number 5 and 6 again
In this MySQL database, you can simply count the number of rows in a table “on your fingers”. But what if the table has not 7 rows, but several hundred? For example, like in this one.
And this is just one of eight sample pages. In fact, the number of records can be counted not only in hundreds, but also in thousands. Additionally, there is no id column because it is optional. Its presence depends on the structure of the entire database, since in it the values of all rows are interconnected. It turns out that the MySQL row counting method described above does not always work.
Several correct methods
As they say, the first method is lumpy, so let’s look at several more effective and less “simple” methods:
COUNT() function – returns the number of rows. Is built-in SQL function. Still, let's find out the number of records of a large table from the database, the screenshot of which is given above. Request code:
SELECT COUNT(*) FROM `country`
SELECT COUNT (*) FROM `country` |
It turns out that there are as many as 239 records in the country table! It is clear that there are not enough fingers or toes to count them (unless you use the limbs of other people or animals)). As you can see, this method of counting the number of rows in MySQL works much more accurately and is most often used.
But this function does more than just that! For example, you can use it to find out how many of the countries “represented” in the database are in Europe:
And also how many times each country is mentioned in the table. Example request:
SELECT `Name` , COUNT(*) FROM `country` GROUP BY `Name`
SELECT ` Name ` , COUNT (* ) FROM ` country ` GROUP BY ` Name ` |
Using this query, we found out the number of rows in MySQL that mention each country. From the results of its execution it is clear that one time at a time.
I almost forgot! If you do not yet have the world database, which we are working with today, then you can download it from this link from the official website of the MySQL DBMS developers. After downloading the archive with it, you need to unzip it and then import it from the database server. In phpMyAdmin, this can be done through the “Import” main menu item.
I talked about this procedure in more detail in one of the previous materials.
MAX() function - in some cases, it also allows MySQL to find out the number of rows in a table. Why sometimes? I'll explain now. Remember the first method of counting that we looked at at the beginning. It implies that the largest value of the id column is equal to the number of records. The use of this function is a kind of “automation” of that method. MAX() returns the maximum value of the specified column (id). To test the function, we will use the city table, which is also part of the world database. Request code:
SELECT MAX(`ID`) FROM `city`
SELECT MAX (`ID`) FROM `city` |
As a result of the query, we received the value of the column of the last row. It turns out that MySQL has counted the number of rows.
If necessary, you can get last entry in full:
SELECT MAX(`ID`),`Name`,`CountryCode`,`District`,`Population` FROM `city`
SELECT MAX (`ID`), `Name`, `CountryCode`, `District`, `Population` FROM `city` |
Please note that for selecting, before calling the function, instead of “*” we list the names of all columns. This is a syntax feature when using built-in functions in MySQL.
Determining the number of records in PHP
Often in website code PHP MySQL the number of rows is required to be obtained in order to then pass this value as an input parameter for further processing. This can be done with the following code:
query("SELECT * FROM `country`")) ( printf("The table has %d rows.\n", $result->num_rows); $result->close(); ) mysqli_close($con); ?>
I once faced this problem, but many of the solutions offered on the Internet did not work for me. And yet I found a way out. And not even alone.
Method 1. phpMyAdmin
Let's start with the simplest and most obvious. Let's say you don't need to use the result in code, but just find out the number of lines, that's all.
Then go to phpMyAdmin (localhost/tools/phpmyadmin - for Denver), then select the desired database, then select the table, the number of rows in which you want to find out, and see the following message:
Method 2. COUNT
This is what the SQL query itself looks like:
SQL query with condition:
Implementation in PHP:
Similarly, you can add a condition here. Then the code will display the number of rows in the table that satisfy the condition.
Method 3: mysql_num_rows
For example, in WordPress posts are stored in the wp_posts table, so we can find out how many posts (posts) are stored in the database (in the MySQL table). This code is given for example only (or for cases when the WordPress environment is not loaded), since in WordPress connection The database is accessed through the .
The result will include absolutely all records. How can I find out the number of only those that are published? To do this, we will need to slightly change the SQL query.
