如何使用 PHP 获取 MySQL 表中的行数? [英] How to get count of rows in MySQL table using PHP?

问题描述

我只是想使用 PHP 获取 MySQL 表中总行数的计数，并将该数字存储在名为 $count 的变量中.

I simply want to use PHP to get a count of the total number of rows in a MySQL table, and store the number in a variable called $count.

我更喜欢过程式代码，因为我的思维不以面向对象的方式工作.

I prefer procedural code since my mind doesn't work in object-oriented fashion.

$sql="SELECT COUNT(*) FROM news";
$result = mysqli_query($con, $sql);
$count = mysqli_fetch_assoc($result);
echo $count;

以上是我试过的最新的.它没有给我一个数字，而是给了我数组"这个词.

The above is the latest I tried. Instead of giving me a number, it gives me the word "Array".

推荐答案

如何从 SQL 中获取 COUNT(*) 的值有几个选项.最简单的三个大概是这样的:

You have a couple of options how to get the value of COUNT(*) from the SQL. The easiest three are probably this:

$sql = "SELECT COUNT(*) FROM news";
$result = mysqli_query($con, $sql);
$count = mysqli_fetch_assoc($result)['COUNT(*)'];
echo $count;

或使用列别名:

$sql = "SELECT COUNT(*) as cnt FROM news";
$result = mysqli_query($con, $sql);
$count = mysqli_fetch_assoc($result)['cnt'];
echo $count;

或使用数值数组:

$sql = "SELECT COUNT(*) FROM news";
$result = mysqli_query($con, $sql);
$count = mysqli_fetch_row($result)[0];
echo $count;

如果您使用的是 PHP 8.1，那么您可以做得更简单:

If you are using PHP 8.1, then you can do it even simpler:

$sql = "SELECT COUNT(*) FROM news";
$result = mysqli_query($con, $sql);
$count = mysqli_fetch_column($result);
echo $count;
// or using OO style
echo $con->query("SELECT COUNT(*) FROM news")->fetch_column();

不要按照网络上某些地方的建议使用mysqli_num_rows 来计算数据库中的记录.这个功能用处很小，计数记录绝对不是其中之一.使用 mysqli_num_rows 你会要求 MySQL 从数据库中检索所有匹配的记录，这可能非常消耗资源.最好将记录计数的工作委托给 MySQL，然后在 PHP 中获取返回值，如我的回答所示.

Do not use mysqli_num_rows to count the records in the database as suggested in some places on the web. This function has very little use, and counting records is definitely not one of them. Using mysqli_num_rows you would be asking MySQL to retrieve all matching records from database, which could be very resource consuming. It is much better to delegate the job of counting records to MySQL and then just get the returned value in PHP as shown in my answer.

我还建议您学习 OOP，这会使您的代码更清晰、更易于阅读.与 OOP 相同，可以按如下方式完成:

I would also recommend to learn OOP, which makes your code cleaner and easier to read. The same with OOP could be done as follows:

$sql = "SELECT COUNT(*) FROM news";
$count = $con->query($sql)->fetch_row()[0];
echo $count;

如果您的查询使用变量，那么您可以做类似的事情，但使用准备好的语句.

If your query uses variables, then you could do a similar thing, but using prepared statements.

$sql = "SELECT COUNT(*) FROM news WHERE category=?";
$stmt = $con->prepare($sql);
$stmt->bind_param('s', $category);
$stmt->execute();
$count = $stmt->get_result()->fetch_row()[0];
echo $count;

这篇关于如何使用 PHP 获取 MySQL 表中的行数?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！