How to count rows in MySQL table in PHP ? (original) (raw)
Last Updated : 23 Jul, 2025
PHP stands for hypertext preprocessor. MySQL is a database query language used to manage databases.
In this article, we are going to discuss how to get the count of rows in a particular table present in the database using PHP and MySQL.
Requirements:
Approach: By using PHP and MySQL, one can perform database operations. We can get the total number of rows in a table by using the MySQL mysqli_num_rows()function**.**
Syntax:
mysqli_num_rows( result );
The result is to specify the result set identifier returned by mysqli_query() function.
Example: The following table has 5 rows.
To count the number of rows in the building table, the following code snippet is used.
$sql = "SELECT * from building";
if ($result = mysqli_query($con, $sql)) {
// Return the number of rows in result set
<span class="katex"><span class="katex-mathml"><math xmlns="http://www.w3.org/1998/Math/MathML"><semantics><mrow><mi>r</mi><mi>o</mi><mi>w</mi><mi>c</mi><mi>o</mi><mi>u</mi><mi>n</mi><mi>t</mi><mo>=</mo><mi>m</mi><mi>y</mi><mi>s</mi><mi>q</mi><mi>l</mi><msub><mi>i</mi><mi>n</mi></msub><mi>u</mi><msub><mi>m</mi><mi>r</mi></msub><mi>o</mi><mi>w</mi><mi>s</mi><mo stretchy="false">(</mo></mrow><annotation encoding="application/x-tex">rowcount = mysqli_num_rows( </annotation></semantics></math></span><span class="katex-html" aria-hidden="true"><span class="base"><span class="strut" style="height:0.6151em;"></span><span class="mord mathnormal">ro</span><span class="mord mathnormal" style="margin-right:0.02691em;">w</span><span class="mord mathnormal">co</span><span class="mord mathnormal">u</span><span class="mord mathnormal">n</span><span class="mord mathnormal">t</span><span class="mspace" style="margin-right:0.2778em;"></span><span class="mrel">=</span><span class="mspace" style="margin-right:0.2778em;"></span></span><span class="base"><span class="strut" style="height:1em;vertical-align:-0.25em;"></span><span class="mord mathnormal">m</span><span class="mord mathnormal">ys</span><span class="mord mathnormal" style="margin-right:0.01968em;">ql</span><span class="mord"><span class="mord mathnormal">i</span><span class="msupsub"><span class="vlist-t vlist-t2"><span class="vlist-r"><span class="vlist" style="height:0.1514em;"><span style="top:-2.55em;margin-left:0em;margin-right:0.05em;"><span class="pstrut" style="height:2.7em;"></span><span class="sizing reset-size6 size3 mtight"><span class="mord mathnormal mtight">n</span></span></span></span><span class="vlist-s"></span></span><span class="vlist-r"><span class="vlist" style="height:0.15em;"><span></span></span></span></span></span></span><span class="mord mathnormal">u</span><span class="mord"><span class="mord mathnormal">m</span><span class="msupsub"><span class="vlist-t vlist-t2"><span class="vlist-r"><span class="vlist" style="height:0.1514em;"><span style="top:-2.55em;margin-left:0em;margin-right:0.05em;"><span class="pstrut" style="height:2.7em;"></span><span class="sizing reset-size6 size3 mtight"><span class="mord mathnormal mtight" style="margin-right:0.02778em;">r</span></span></span></span><span class="vlist-s"></span></span><span class="vlist-r"><span class="vlist" style="height:0.15em;"><span></span></span></span></span></span></span><span class="mord mathnormal">o</span><span class="mord mathnormal" style="margin-right:0.02691em;">w</span><span class="mord mathnormal">s</span><span class="mopen">(</span></span></span></span>result );
// Display result
printf("Total rows in this table : %d\n", $rowcount);}
Output: The expected result is as follows.
Total rows in this table : 5
Steps for the approach:
- Create a database named database.
- Create a table named building inside the database.
- Insert records into it.
- Write PHP code to count rows.
Steps:
- Start XAMPP server.
XAMPP server
- Create a database named database and create a table named building inside the database.
- Insert records into it
building table
- Write PHP code to count rows.
PHP code:
PHP `
rowcount=mysqlinumrows(rowcount = mysqli_num_rows( rowcount=mysqlinumrows(result ); // Display result printf("Total rows in this table : %d\n", $rowcount); } // Close the connection mysqli_close($con); ?>`
Output: After running the above PHP file in localhost, the following result is achieved.
Total rows in this table : 5
Example 2: In the following example, we count the table rows using MySQL count() function. It's an aggregate function used to count rows.
Syntax:
select count(*) from table;
Consider the table.
PHP code:
PHP `
conn=newmysqli(conn = new mysqli(conn=newmysqli(servername, username,username, username,password, $dbname); // SQL query to find total count // of college_data table sql="SELECTcount(∗)FROMcollegedata";<spanclass="katex"><spanclass="katex−mathml"><mathxmlns="http://www.w3.org/1998/Math/MathML"><semantics><mrow><mi>r</mi><mi>e</mi><mi>s</mi><mi>u</mi><mi>l</mi><mi>t</mi><mo>=</mo></mrow><annotationencoding="application/x−tex">result=</annotation></semantics></math></span><spanclass="katex−html"aria−hidden="true"><spanclass="base"><spanclass="strut"style="height:0.6944em;"></span><spanclass="mordmathnormal">res</span><spanclass="mordmathnormal">u</span><spanclass="mordmathnormal">lt</span><spanclass="mspace"style="margin−right:0.2778em;"></span><spanclass="mrel">=</span></span></span></span>conn−>query(sql = "SELECT count(*) FROM college_data "; <span class="katex"><span class="katex-mathml"><math xmlns="http://www.w3.org/1998/Math/MathML"><semantics><mrow><mi>r</mi><mi>e</mi><mi>s</mi><mi>u</mi><mi>l</mi><mi>t</mi><mo>=</mo></mrow><annotation encoding="application/x-tex">result = </annotation></semantics></math></span><span class="katex-html" aria-hidden="true"><span class="base"><span class="strut" style="height:0.6944em;"></span><span class="mord mathnormal">res</span><span class="mord mathnormal">u</span><span class="mord mathnormal">lt</span><span class="mspace" style="margin-right:0.2778em;"></span><span class="mrel">=</span></span></span></span>conn->query(sql="SELECTcount(∗)FROMcollegedata";<spanclass="katex"><spanclass="katex−mathml"><mathxmlns="http://www.w3.org/1998/Math/MathML"><semantics><mrow><mi>r</mi><mi>e</mi><mi>s</mi><mi>u</mi><mi>l</mi><mi>t</mi><mo>=</mo></mrow><annotationencoding="application/x−tex">result=</annotation></semantics></math></span><spanclass="katex−html"aria−hidden="true"><spanclass="base"><spanclass="strut"style="height:0.6944em;"></span><spanclass="mordmathnormal">res</span><spanclass="mordmathnormal">u</span><spanclass="mordmathnormal">lt</span><spanclass="mspace"style="margin−right:0.2778em;"></span><spanclass="mrel">=</span></span></span></span>conn−>query(sql); // Display data on web page while($row = mysqli_fetch_array($result)) { echo "Total Rows is ". $row['count(*)']; echo ""; } // Close the connection $conn->close(); ?>
`
Output:
Total Rows is 8