Php количество данных в базе

How can I count the numbers of rows that a MySQL query returned?

You could just iterate the result and count them. You don’t say what language or client library you are using, but the API does provide a mysql_num_rows function which can tell you the number of rows in a result.

This is exposed in PHP, for example, as the mysqli_num_rows function. As you’ve edited the question to mention you’re using PHP, here’s a simple example using mysqli functions:

$link = mysqli_connect("localhost", "user", "password", "database"); $result = mysqli_query($link, "SELECT * FROM table1"); $num_rows = mysqli_num_rows($result); echo "$num_rows Rows\n"; 

Getting a count of rows matching some criteria.

Just use COUNT(*) — see Counting Rows in the MySQL manual. For example:

SELECT COUNT(*) FROM foo WHERE bar= 'value'; 

Get total rows when LIMIT is used.

If you’d used a LIMIT clause but want to know how many rows you’d get without it, use SQL_CALC_FOUND_ROWS in your query, followed by SELECT FOUND_ROWS();

SELECT SQL_CALC_FOUND_ROWS * FROM foo WHERE bar="value" LIMIT 10; SELECT FOUND_ROWS(); 

For very large tables, this isn’t going to be particularly efficient, and you’re better off running a simpler query to obtain a count and caching it before running your queries to get pages of data.

-1 SQL CALC FOUND ROWS is a VERY demanding operation. This should be avoided. and can be avoided by creating the same query as before, only selecting the ID and not . Also select * should be avoided as well. view here: parseerror.com/sql/selectisevil.html

Please be wary about using mysql_num_rows. It’s depricated as of PHP 5.5 and will be removed in the future: php.net/manual/en/function.mysql-num-rows.php

In the event you have to solve the problem with simple SQL you might use an inline view.

select count(*) from (select * from foo) as x; 

This is just an example, but if you use something to build queries, and those queries can get quite complex and you just want to count the result (when there may be groupings and joins, etc. that make counting more difficult), then this approach is a very simple and effective approach.

If your SQL query has a LIMIT clause and you want to know how many results total are in that data set you can use SQL_CALC_FOUND_ROWS followed by SELECT FOUND_ROWS(); This returns the number of rows A LOT more efficiently than using COUNT(*)
Example (straight from MySQL docs):

mysql> SELECT SQL_CALC_FOUND_ROWS * FROM tbl_name -> WHERE id > 100 LIMIT 10; mysql> SELECT FOUND_ROWS(); 

You’ve got it upside down. It’s count(*) returns the number of rows A LOT more efficient than SQL_CALC_FOUND_ROWS. Which is even deprecated for this exact reason.

Obviously, count(*) returned the number of rows A LOT more efficient than SQL_CALC_FOUND_ROWS even 20 years ago because the way it works hasn’t changed since then.

Use 2 queries as below, One to fetch the data with limit and other to get the no of total matched rows.

SELECT * FROM tbl_name WHERE id > 1000 LIMIT 10; SELECT COUNT(*) FROM tbl_name WHERE id > 1000; 

As described by Mysql guide , this is the most optimized way, and also SQL_CALC_FOUND_ROWS query modifier and FOUND_ROWS() function are deprecated as of MySQL 8.0.17

Hey Hasitha, the question you are answering is 12 years old. Your answer would be even more useful if you emphasized the deprecation of methods used in the accepted answer. I think a better order for your answer would be 1) mention of deprecation 2) modern query 3) further information (links, citations) . Also note, that it’s generally a good idea to cite one or two important sentences out of any documentation you link to, in case the link becomes invalid or the documentation changes.

i am using this approach. But the problem is i am having around 2Lkah rows in MySQL. and single query taking more then 60Seconds and both are taking around 120 seconds. I am looking to minimize the execution time

FOUND_ROWS and SQL_CALC_FOUND_ROWS are depreciated and won’t be used in versions beyond MySQL 8.0.17. dev.mysql.com/doc/refman/5.0/en/…

SELECT SQL_CALC_FOUND_ROWS * FROM table1 WHERE . ; SELECT FOUND_ROWS(); 

FOUND_ROWS() must be called immediately after the query.

The SQL_CALC_FOUND_ROWS query modifier and accompanying FOUND_ROWS() function are deprecated as of MySQL 8.0.17; expect them to be removed in a future version of MySQL. As a replacement, considering executing your query with LIMIT, and then a second query with COUNT(*) and without LIMIT to determine whether there are additional rows.

If you want the result plus the number of rows returned do something like this. Using PHP.

$query = "SELECT * FROM Employee"; $result = mysql_query($query); echo "There are ".mysql_num_rows($result)." Employee(s)."; 

Assuming you’re using the mysql_ or mysqli_ functions, your question should already have been answered by others.

However if you’re using PDO, there is no easy function to return the number of rows retrieved by a select statement, unfortunately. You have to use count() on the resultset (after assigning it to a local variable, usually).

Or if you’re only interested in the number and not the data, PDOStatement::fetchColumn() on your SELECT COUNT(1). result.

Q read: «How can I count the numbers of rows that a mysql query returned? using PHP..» when I found it. =^)

The basics

To get the number of matching rows in SQL you would usually use COUNT(*) . For example:

SELECT COUNT(*) FROM some_table 

To get that in value in PHP you need to fetch the value from the first column in the first row of the returned result. An example using PDO and mysqli is demonstrated below.

However, if you want to fetch the results and then still know how many records you fetched using PHP, you could use count() or avail of the pre-populated count in the result object if your DB API offers it e.g. mysqli’s num_rows .

Using MySQLi

Using mysqli you can fetch the first row using fetch_row() and then access the 0 column, which should contain the value of COUNT(*) .

// your connection code mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT); $mysqli = new \mysqli('localhost', 'dbuser', 'yourdbpassword', 'db_name'); $mysqli->set_charset('utf8mb4'); // your SQL statement $stmt = $mysqli->prepare('SELECT COUNT(*) FROM some_table WHERE col1=?'); $stmt->bind_param('s', $someVariable); $stmt->execute(); $result = $stmt->get_result(); // now fetch 1st column of the 1st row $count = $result->fetch_row()[0]; echo $count; 

If you want to fetch all the rows, but still know the number of rows then you can use num_rows or count() .

// your SQL statement $stmt = $mysqli->prepare('SELECT col1, col2 FROM some_table WHERE col1=?'); $stmt->bind_param('s', $someVariable); $stmt->execute(); $result = $stmt->get_result(); // If you want to use the results, but still know how many records were fetched $rows = $result->fetch_all(MYSQLI_ASSOC); echo $result->num_rows; // or echo count($rows); 

Using PDO

Using PDO is much simpler. You can directly call fetchColumn() on the statement to get a single column value.

// your connection code $pdo = new \PDO('mysql:host=localhost;dbname=test;charset=utf8mb4', 'root', '', [ \PDO::ATTR_EMULATE_PREPARES => false, \PDO::ATTR_ERRMODE => \PDO::ERRMODE_EXCEPTION ]); // your SQL statement $stmt = $pdo->prepare('SELECT COUNT(*) FROM some_table WHERE col1=?'); $stmt->execute([ $someVariable ]); // Fetch the first column of the first row $count = $stmt->fetchColumn(); echo $count; 

Again, if you need to fetch all the rows anyway, then you can get it using count() function.

// your SQL statement $stmt = $pdo->prepare('SELECT col1, col2 FROM some_table WHERE col1=?'); $stmt->execute([ $someVariable ]); // If you want to use the results, but still know how many records were fetched $rows = $stmt->fetchAll(); echo count($rows); 

PDO’s statement doesn’t offer pre-computed property with the number of rows fetched, but it has a method called rowCount() . This method can tell you the number of rows returned in the result, but it cannot be relied upon and it is generally not recommended to use.

If you’re fetching data using WordPress, then you can access the number of rows returned using $wpdb->num_rows:

$wpdb->get_results( $wpdb->prepare('select * from mytable where foo = %s', $searchstring)); echo $wpdb->num_rows; 

If you want a specific count based on a mysql count query then you do this:

$numrows = $wpdb->get_var($wpdb->prepare('SELECT COUNT(*) FROM mytable where foo = %s', $searchstring ); echo $numrows; 

If you’re running updates or deletes then the count of rows affected is returned directly from the function call:

$numrowsaffected = $wpdb->query($wpdb->prepare( 'update mytable set val=%s where myid = %d', $valuetoupdate, $myid)); 

This applies also to $wpdb->update and $wpdb->delete.

As it is 2015, and deprecation of mysql_* functionality, this is a PDO -only visualization.

'; try < $theCategory="fruit"; // value from user, hard-coded here to get one in $dbh = new PDO("mysql:host=$host;dbname=$dbname;charset=utf8", $username, $password); $dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); // prepared statement with named placeholders $stmt = $dbh->prepare("select id,foodName from foods where category=:theCat and perishable=1"); $stmt->bindParam(':theCat', $theCategory, PDO::PARAM_STR,20); $stmt->execute(); echo "rowCount() returns: ".$stmt->rowCount().$b; // See comments below from the Manual, varies from driver to driver $stmt = $dbh->prepare("select count(*) as theCount from foods where category=:theCat and perishable=1"); $stmt->bindParam(':theCat', $theCategory, PDO::PARAM_STR,20); $stmt->execute(); $row=$stmt->fetch(); // fetches just one row, which is all we expect echo "count(*) returns: ".$row['theCount'].$b; $stmt = null; // PDO closes connection at end of script > catch (PDOException $e) < echo 'PDO Exception: ' . $e->getMessage(); exit(); > ?> 

Schema for testing

create table foods ( id int auto_increment primary key, foodName varchar(100) not null, category varchar(20) not null, perishable int not null ); insert foods (foodName,category,perishable) values ('kiwi','fruit',1),('ground hamburger','meat',1), ('canned pears','fruit',0),('concord grapes','fruit',1); 

For my implementation, I get the output of 2 for both echos above. The purpose of the above 2 strategies is to determine if your driver implementation emits the rowCount, and if not, to seek a fall-back strategy.

PDOStatement::rowCount() returns the number of rows affected by a DELETE, INSERT, or UPDATE statement.

For most databases , PDOStatement::rowCount() does not return the number of rows affected by a SELECT statement. Instead, use PDO::query() to issue a SELECT COUNT(*) statement with the same predicates as your intended SELECT statement, then use PDOStatement::fetchColumn() to retrieve the number of rows that will be returned. Your application can then perform the correct action.

Источник

Узнать количество записей в таблице MySQL

Нужно с помощью PHP узнать сколько в таблице users пользователей, у которых значение rating больше 10. Подскажите пожалуйста, как это сделать?

1 ответ 1

Если нужно узнать только количество строк, но не нужны сами строки

Для того чтобы узнать количество записей в таблице, нужно использовать SQL функцию COUNT() :

Если нужно подсчитать количество записей по условию, то его надо добавить в запрос:

SELECT count(*) FROM users WHERE rating > 10 

Для получения этого значения в РНР можно выполнить запрос обычным порядком

$res = $conn->query("SELECT count(*) FROM users WHERE rating > 10"); $row = $res->fetch_row(); $count = $row[0]; 

Если условие задается динамически, то при запросе необходимо использовать подготовленные выражения:

$rating = 10; $stmt = $conn->prepare("SELECT count(*) FROM users WHERE rating > ?"); $stmt->bind_param("s", $rating); $stmt->execute(); $res = $stmt->get_result(); $row = $res->fetch_row(); $count = $row[0]; 

Если нужно получить сами строки, а также узнать, сколько их

Здесь ещё проще. Просто получаем все строки в массив:

$res = $conn->query("SELECT * FROM users WHERE rating > 10"); $rows = $res->fetch_all(MYSQLI_ASSOC); 

Здесь мы получили обычный массив, с которым можем делать то же самое что и с любым другим массивом. например, если нам надо узнать есть ли в массиве что-нибудь, мы можем подставить его в прямо в условие:

Если вдруг надо получить именно количество строк, то просто выполняем обычную функцию count()

Источник