如何使用PHP获取MySQL表中的行数?

问题描述

我只是想使用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;

以上是我尝试过的最新内容.而不是给我一个数字，而是给我一个单词"Array".

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;

请勿像在网络上的某些位置所建议的那样，使用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表中的行数?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！