CODEWITHSUNDEEP
phpmysqlmysqli
Obcure
Obcure

Reputation: 991

Counting Number of Rows

I have the following code that used to echo the number of columns that was an accident and I wanted it to count rows now that I've changed it its not echoing anything. Any ideas? By the way I have already connected to the database even though it is not shown.

<p>
<?php
$result = mysqli_query("SELECT * FROM User_info");
$num_rows = mysqli_num_rows($result);
echo $num_rows[0];

mysqli_close($con);
?>
 records in our database start your search <a href="#">here</a> today. 
</p>

Upvotes: 1

Views: 24772

Answers (4)

Sylvain Leroux
Sylvain Leroux

Reputation: 52000

As a general rule, you should minimize the traffic between your DB server and your application server.

For your specific case, that means the recommended way of "counting" is:

<?php
$result = mysqli_query("SELECT count(*) FROM User_info");
$row = mysqli_fetch_row($result);
$num = $row[0];
?>

With that request (SELECT COUNT(*) ...), the DB server will count rows for you. And just return that total -- possibly using indexes or internal data structure to return directly the result. With SELECT *, the server has to retrieve and then send all the data in your table. Quite expensive and rather inefficient since you don't use all these data after that in your application...

EDIT -- if you want a "simple" explanation: what is the most efficient to know the number of pages in a book? To count them one by one, or to look at the page numbers in the footer?

With your initial solution (counting in PHP), it is like making a xerox of the book, and then counting the pages in the copy. With COUNT(*) you let the DB server use the page numbers or even an index (the "TOC" of the book) to find the result.

Upvotes: 7

Borniet
Borniet

Reputation: 3546

mysqli_num_rows() returns its result as an integer (or string if the value is too large for an integer), not as an array, so you need to change your code from:

echo $num_rows[0];

to

echo $num_rows;

Upvotes: 1

Your Common Sense
Your Common Sense

Reputation: 157863

Here is the only right way to count rows:

<?php
$result = mysqli_query("SELECT count(*) FROM User_info");
$row = mysqli_fetch_row($result);
$num = $row[0];
?>
<p>
<?=$num?> records in our database start your search <a href="#">here</a> today. 
</p>

Upvotes: 4

ciruvan
ciruvan

Reputation: 5213

Change that one line to

echo $num_rows;

Upvotes: 0

Related Questions