MySQL - count total number of rows in php

Question

What is the best MySQL command to count the total number of rows in a table without any conditions applied to it? I'm doing this through php, so maybe there is a php function which does this for me? I don't know. Here is an example of my php:

<?php
$con = mysql_connect("server.com","user","pswd");
if (!$con) {
  die('Could not connect: ' . mysql_error());
}

mysql_select_db("db", $con);

$result = mysql_query("some command");
$row = mysql_fetch_array($result);

mysql_close($con);
?>

Jim · Accepted Answer · 2012-09-07 15:29:16Z

57

<?php
$con = mysql_connect("server.com","user","pswd");
if (!$con) {
  die('Could not connect: ' . mysql_error());
}

mysql_select_db("db", $con);

$result = mysql_query("select count(1) FROM table");
$row = mysql_fetch_array($result);

$total = $row[0];
echo "Total rows: " . $total;

mysql_close($con);
?>

edited Sep 7, 2012 at 15:29

Jim

18.9k5 gold badges51 silver badges66 bronze badges

answered Jul 11, 2011 at 19:50

fdaines

1,2361 gold badge10 silver badges15 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

User Over a year ago

What about in PHP 5.3?

ehsan wwe Over a year ago

it don't work true with LIMIT in query - this method return real count of array

Syed M. Sannan Over a year ago

**2022: STOP USING MYSQL METHODS, THESE ARE NOT SECURE AND REMOVED FROM PHP! Use MySQLi or PDO instead.

Jules · Accepted Answer · 2011-07-11 19:50:26Z

27

Either use COUNT in your MySQL query or do a SELECT * FROM table and do:

$result = mysql_query("SELECT * FROM table");
$rows = mysql_num_rows($result);
echo "There are " . $rows . " rows in my table.";

answered Jul 11, 2011 at 19:50

Jules

7,2236 gold badges28 silver badges51 bronze badges

3 Comments

George Johnston Over a year ago

You're first part is correct. I would emit the last part pulling back the entire dataset. In this questions scope, that's a waste of resources for a simple count.

Jules Over a year ago

@George I would not suggest using it either. But as he asked if there was any PHP function that could do it, I just shared the option. Just answering the question.

George Johnston Over a year ago

And that's fine, however, it's always good to point out potential issues like this when you answer a question. Not only does it help the user understand the differences, it increases the legitimacy of your answer, and the attention it gets. Which means, more points for you! :)

Suraj Rao · Accepted Answer · 2019-08-13 14:22:20Z

9

mysqli_num_rows is used in php 5 and above.

e.g

<?php
$con=mysqli_connect("localhost","my_user","my_password","my_db");
// Check connection
if (mysqli_connect_errno())
  {
  echo "Failed to connect to MySQL: " . mysqli_connect_error();
  }

$sql="SELECT Lastname,Age FROM Persons ORDER BY Lastname";

if ($result=mysqli_query($con,$sql))
  {
  // Return the number of rows in result set
  $rowcount=mysqli_num_rows($result);
  printf("Result set has %d rows.\n",$rowcount);
  // Free result set
  mysqli_free_result($result);
  }
mysqli_close($con);
?>

edited Aug 13, 2019 at 14:22

Suraj Rao

29.7k11 gold badges96 silver badges104 bronze badges

answered Aug 13, 2019 at 14:19

chobela

3053 silver badges5 bronze badges

Comments

Jon Gauthier · Accepted Answer · 2011-07-11 19:48:46Z

6

Use COUNT in a SELECT query.

$result = mysql_query('SELECT COUNT(1) FROM table');
$num_rows = mysql_result($result, 0, 0);

answered Jul 11, 2011 at 19:48

Jon Gauthier

25.7k6 gold badges66 silver badges69 bronze badges

1 Comment

Jon Gauthier Over a year ago

Use mysql_result (the best for this instance, IMHO) or one of the similar functions for fetching data from result sets.

Shridhar · Accepted Answer · 2021-09-30 17:45:41Z

6

you can do it only in one line as below:

$cnt = mysqli_num_rows(mysql_query("SELECT COUNT(1) FROM TABLE"));
echo $cnt;

edited Sep 30, 2021 at 17:45

answered Nov 14, 2013 at 19:48

Shridhar

3394 silver badges15 bronze badges

5 Comments

Yang Over a year ago

Why are you writing this answer, when there's accepted answer? And this question has been asked in 2011.

Pascamel Over a year ago

...using a deprecated library (mysql). use mysqli instead

sfossen Over a year ago

that is a horrible answer, sending the entire table across to count rows?

sfossen Over a year ago

use the select count(1) from table to get the count and put into a variable

Nick Over a year ago

This answer is wrong. It return always 1 because you count the amount of records returned from the query. Since that you are using an aggregate function like COUNT you will get always an row in the result, and later you count this row thereby you'll get always 1. Furthermore you are using a deprecated library

Toki · Accepted Answer · 2019-03-11 17:09:36Z

4

use num_rows to get correct count for queries with conditions

$result = $connect->query("select * from table where id='$iid'");
$count=$result->num_rows;
echo "$count";

answered Mar 11, 2019 at 17:09

Toki

2944 silver badges14 bronze badges

1 Comment

IndustProg Over a year ago

Good but what is where id='$iid'?!!!!!!!

Asesha · Accepted Answer · 2017-03-03 10:12:05Z

2

for PHP 5.3 using PDO

<?php
    $staff=$dbh->prepare("SELECT count(*) FROM staff_login");
    $staff->execute();
    $staffrow = $staff->fetch(PDO::FETCH_NUM);
    $staffcount = $staffrow[0];


    echo $staffcount;
?>

answered Mar 3, 2017 at 10:12

Asesha

2,2362 gold badges34 silver badges66 bronze badges

1 Comment

Boldewyn Over a year ago

Good addition. A small enhancement: If you use $staffcount = $staff->fetchColumn(); you can get rid of the $staffrow temporary variable.

Amirhosein jaafari · Accepted Answer · 2018-02-24 15:05:45Z

2

<?php
$con=mysqli_connect("localhost","my_user","my_password","my_db");
// Check connection
if (mysqli_connect_errno())
  {
  echo "Failed to connect to MySQL: " . mysqli_connect_error();
  }

$sql="SELECT Lastname,Age FROM Persons ORDER BY Lastname";

if ($result=mysqli_query($con,$sql))
  {
  // Return the number of rows in result set
  $rowcount=mysqli_num_rows($result);
  echo "number of rows: ",$rowcount;
  // Free result set
  mysqli_free_result($result);
  }

mysqli_close($con);
?>

it is best way (I think) to get the number of special row in mysql with php.

answered Feb 24, 2018 at 15:05

Amirhosein jaafari

213 bronze badges

1 Comment

progmatico Over a year ago

This technique has already been answered. Why "special rows"? They are just query result rows.

Divyasundar Sahu · Accepted Answer · 2017-03-22 10:37:10Z

1

<?php
$conn=mysqli_connect("127.0.0.1:3306","root","","admin");
// Check connection
if (mysqli_connect_errno())
{
echo "Failed to connect to MySQL: " . mysqli_connect_error();
}
$sql="select count('user_id') from login_user";
$result=mysqli_query($conn,$sql);
$row=mysqli_fetch_array($result);
echo "$row[0]";
mysqli_close($conn);
?>

Still having problem visit my tutorial http://www.studentstutorial.com/php/php-count-rows.php

answered Mar 22, 2017 at 10:37

Divyasundar Sahu

191 bronze badge

Comments

uadnal · Accepted Answer · 2011-07-11 19:49:37Z

0

$sql = "select count(column_name) as count from table";

answered Jul 11, 2011 at 19:49

uadnal

11.5k2 gold badges35 silver badges40 bronze badges

Comments

ucMax · Accepted Answer · 2024-01-27 23:06:24Z

0

It`s better to count rows directly in the database, it is more efficient for large tables.

$tableName = 'my-table'
$sql = "SELECT COUNT(*) FROM $tableName";
$row = mysqli_fetch_assoc(mysqli_query($conn, $sql));
$row_count = $row['COUNT(*)'];

answered Jan 27, 2024 at 23:06

ucMax

5,5615 gold badges52 silver badges57 bronze badges

Comments

Subhan Raj · Accepted Answer · 2022-12-11 13:55:07Z

-1

Well, I used the following approach to do the same: I have to get a count of many tables for listing the number of services, projects, etc on the dashboard. I hope it helps.

PHP Code

// create a function 'cnt' which accepts '$tableName' as the parameter.

 function cnt($tableName){
        global $conection;
        $itemCount = mysqli_num_rows(mysqli_query($conection, "SELECT * FROM `$tableName`"));
        echo'<h6>'.$itemCount.'</h6>';
    }

Then when I need to get the count of items in the table, I call the function like following

<?php
  cnt($tableName = 'projects');
?>

In my HTML front end, so it renders the count number

It's to be noted that I create the cnt() function as a global function in a separate file which I include in my head, so I can call it from anywhere in my code.

answered Dec 11, 2022 at 13:55

Subhan Raj

95 bronze badges

2 Comments

SeeoX Over a year ago

It is very less optimize than the COUNT(*) approach

Subhan Raj Over a year ago

Well it can be but I can't get it working while this worked for me, so no issues.

Collectives™ on Stack Overflow

MySQL - count total number of rows in php

12 Answers 12

3 Comments

3 Comments

Comments

1 Comment

5 Comments

1 Comment

1 Comment

1 Comment

Comments

Comments

Comments

PHP Code

2 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

12 Answers 12

3 Comments

3 Comments

Comments

1 Comment

5 Comments

1 Comment

1 Comment

1 Comment

Comments

Comments

Comments

PHP Code

2 Comments

Linked

Related