home / ON/ How to count all rows in mysql. Fetching and Counting Rows in One Query - SQL_CALC_FOUND_ROWS

How to count all rows in mysql. Fetching and Counting Rows in One Query - SQL_CALC_FOUND_ROWS

Starting from version 4.0, the MySQL DBMS has enough convenient opportunity counting the number of all records eligible for the request when the number of records is limited by LIMIT. When working with a database search, as well as when making selections from tables with big amount records, such functionality is simply necessary.

Syntax. In the SELECT query, the SQL_CALC_FOUND_ROWS option must be specified before the list of columns. Here's the beginning of the syntax for the SELECT clause.

SELECT

select_expr, ... ...

Thus, by doing SELECT query SQL_CALC_FOUND_ROWS The DBMS will count the total number of rows matching the query condition and store this number in memory. Naturally, the SELECT SQL_CALC_FOUND_ROWS query only makes sense when the (LIMIT) constraint is used. Immediately after executing a select query to get 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, which will store the number of rows.

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 (print) 10 rows of the tbl_name table 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 a LIMIT clause. Although using the SELECT SQL_CALC_FOUND_ROWS command requires MySQL to recalculate all the rows in the result set, this method is still faster than without LIMIT, since it does not need to send the result to the client.

Sample 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 output 10 rows from table1, and then an integer value for the number of rows that match the query (excluding LIMIT).

In queries with UNION, SQL_CALC_FOUND_ROWS can behave in two ways due to the fact that LIMIT can appear in several places. Row counting can be done for individual SELECT queries, or for the entire query after the concatenation.

The purpose of SQL_CALC_FOUND_ROWS for UNION is that it should return the number of rows that will be returned without a global LIMIT. The conditions for using SQL_CALC_FOUND_ROWS with UNION are as follows:

The SQL_CALC_FOUND_ROWS keyword must appear in the first SELECT statement.
FOUND_ROWS () will only be accurate if UNION ALL is used. If UNION is specified without ALL, deduplication occurs and FOUND_ROWS () is only approximate.
If LIMIT is not present in 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.

Let's remember what messages and in what topics we have. To do this, you can use the usual query:

But what if we just need to find out how many messages there are on the forum. To do this, you can use the built-in function COUNT ()... This function counts the number of rows. Moreover, if * acts as an argument to this function, then all the rows of the table are counted. And if a column name is specified as an argument, then only those rows that have a value in the specified column are counted.

In our example, both arguments will give the same result, since all columns in the table are NOT NULL. Let's write a query using the id_topic column as an argument:

SELECT COUNT (id_topic) FROM posts;

So, there are 4 posts in our threads. But what if we want to know how many posts are in each topic. To do this, we need to group our messages by topic and calculate the number of messages for each group. For grouping in SQL, use the operator GROUP BY... Our request will now look like this:

SELECT id_topic, COUNT (id_topic) FROM posts GROUP BY id_topic;

Operator GROUP BY tells the DBMS to group data by the id_topic column (i.e., each topic is a separate group) and for each group to count the number of rows:

Well, in the topic with id = 1 we have 3 messages, and with id = 4 - one. By the way, if there were no values in the id_topic field, then such rows would be combined into a separate group with a NULL value.

Suppose we are only interested in groups with more than two messages. In a normal query, we would specify a condition using the operator WHERE, but this operator can work only with strings, and for groups the same functions are performed by the operator HAVING:

SELECT id_topic, COUNT (id_topic) FROM posts GROUP BY id_topic HAVING COUNT (id_topic)> 2;

As a result, we have:

In lesson 4, we considered what conditions can be set by the operator WHERE, the same conditions can be set by the operator HAVING, you just need to remember that WHERE filters the lines, and HAVING- groups.

So today we learned how to create groups and how to count the number of rows in a table and in groups. Generally, together with the operator GROUP BY you can use other built-in functions, but we will study them later.

To determine the number of records in a MySQL table, you need to use the special function COUNT ().

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 where expr evaluates to NOT NULL.

An exception to this rule is the use of the COUNT () function with an asterisk as an argument - COUNT (*). In this case, all rows are considered, regardless of whether they are NULL or NOT NULL.

For example, the COUNT (*) function returns the total number of records in a table:

SELECT COUNT (*) FROM table_name

How to count the number of entries and display

An 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 with this function, you can perform other tasks as well.

By specifying a specific column in a table as a parameter, the COUNT (column_name) function returns the number of records in that column that do not contain NULL. NULL entries are ignored.

SELECT COUNT (column_name) FROM table_name

You cannot use the mysql_num_rows () function, because in order to find out the total number of records, you need to execute the SELECT * FROM db query, that is, get all the records, which is undesirable, therefore, it is preferable to use the count function.

$ result = mysql_query ("SELECT COUNT (*) as rec FROM db");

Using the COUNT () function by example

Here's another example of using the COUNT () function. Let's say you have an ice_cream table with an ice cream catalog that contains category IDs and ice cream names.

Once I encountered 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 one.

Method 1.phpMyAdmin

Let's start with the simplest and most obvious. Let's say you don't need to use the result in the 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 need to find out, and we see the following message:

Method 2. COUNT

This is how the SQL query itself looks like:

SQL query with condition:

PHP implementation:

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 the connection to the database is done through the class.