select count(*) from table of mysql in php

Question

I am able to get both the value and row of the mysql query result.

But I am struggling to get the single output of a query. e.g.:

$result = mysql_query("SELECT COUNT(*) FROM Students;");

I need the result to display. But I am not getting the result.

I have tried with the following methods:

mysql_fetch_assoc()
mysql_free_result()
mysql_fetch_row()

But I didn't succeed to display (get) the actual value.

By now you should be using [PDO](http://stackoverflow.com/q/883365/1505348). — Lucio, Sep 13 '13 at 19:09

score 227 · Accepted Answer · edited May 12 '13 at 20:52

227

You need to alias the aggregate using the as keyword in order to call it from mysql_fetch_assoc

$result=mysql_query("SELECT count(*) as total from Students");
$data=mysql_fetch_assoc($result);
echo $data['total'];

edited May 12 '13 at 20:52

Kermit

33,827
13
85
121

answered Aug 02 '11 at 05:48

Shakti Singh

84,385
21
134
153

14

After 5.x version this query will not work just replace mysql with mysqli to make it work. – ajay verma Apr 02 '18 at 16:09

score 32 · Answer 2 · answered May 12 '13 at 20:50

32

If you only need the value:

$result = mysql_query("SELECT count(*) from Students;");
echo mysql_result($result, 0);

answered May 12 '13 at 20:50

bmaupin

14,427
5
89
94

2

`echo mysql_result(mysql_query("SELECT count(*) from Students;"),0);` is saving one unnecessary variable – Jan 08 '16 at 18:35
Correct; my answer was tailored to the question. – bmaupin Jan 08 '16 at 21:09
5

@eichertc the php interpreter has the variable always internal, as he needs to have the result in memory one way or the other. So your way just makes the code worser to read and maintain, IMHO. – Tom Mar 14 '17 at 13:49

score 17 · Answer 3 · edited Mar 18 '16 at 14:56

17

$result = mysql_query("SELECT COUNT(*) AS `count` FROM `Students`");
$row = mysql_fetch_assoc($result);
$count = $row['count'];

Try this code.

edited Mar 18 '16 at 14:56

ban-geoengineering

18,324
27
171
253

answered Aug 02 '11 at 05:48

avetarman

1,244
9
8

score 14 · Answer 4 · edited Nov 21 '20 at 20:51

14

Please start using PDO.

mysql_* is deprecated as of PHP 5.5.0 and will be removed entirely in 7. Let's make it easier to upgrade and start using it now.

$dbh = new \PDO($dsn, $user, $password);
$sth = $dbh->prepare('SELECT count(*) as total from Students');
$sth->execute();
print_r($sth->fetchColumn());

edited Nov 21 '20 at 20:51

Dharman

30,962
25
85
135

answered Aug 19 '15 at 16:29

Lee Davis

4,685
3
28
39

score 9 · Answer 5 · edited Dec 26 '18 at 13:02

9

here is the code for showing no of rows in the table with PHP

$sql="select count(*) as total from student_table";
$result=mysqli_query($con,$sql);
$data=mysqli_fetch_assoc($result);
echo $data['total'];

edited Dec 26 '18 at 13:02

Sani Kamal

1,208
16
26

answered May 26 '18 at 13:26

Raja Sekhar

99
1
2

I like the use of `AS` here, instead of needing to type `echo $data['count(*)'];` – Typewar Dec 31 '19 at 03:27

score 7 · Answer 6 · answered Aug 02 '11 at 05:48

7

$num_result = mysql_query("SELECT count(*) as total_count from Students ") or exit(mysql_error());
$row = mysql_fetch_object($num_result);
echo $row->total_count;

answered Aug 02 '11 at 05:48

fatnjazzy

6,070
12
57
83

score 4 · Answer 7 · edited Nov 21 '20 at 20:56

4

You can as well use this and upgrade to mysqli_ (stop using mysql_* extension...)

$result = mysqli_query($conn, "SELECT COUNT(*) AS `count` FROM `Students`");
$row = mysqli_fetch_array($result);
$count = $row['count'];
echo $count;

edited Nov 21 '20 at 20:56

Dharman

30,962
25
85
135

answered Aug 02 '17 at 22:19

MK Smith

111
10

score 3 · Answer 8 · answered Nov 05 '17 at 18:59

With mysql v5.7.20, here is how I was able to get the row count from a table using PHP v7.0.22:

$query = "select count(*) from bigtable";
$qresult = mysqli_query($this->conn, $query);
$row = mysqli_fetch_assoc($qresult);
$count = $row["count(*)"];
echo $count;

The third line will return a structure that looks like this:

array(1) {
   ["count(*)"]=>string(4) "1570"
}

In which case the ending echo statement will return:

score 3 · Answer 9 · answered Sep 12 '18 at 10:40

For mysqli users, the code will look like this:

$mysqli = new mysqli($db_host, $db_user, $db_pass, $db_name);

$result = $mysqli->query("SELECT COUNT(*) AS Students_count FROM Students")->fetch_array();
var_dump($result['Students_count']);

or:

$mysqli = new mysqli($db_host, $db_user, $db_pass, $db_name);

$result = $mysqli->query("SELECT COUNT(*) FROM Students")->fetch_array();
var_dump($result[0]);

score 2 · Answer 10 · edited Nov 21 '20 at 20:52

2

$db  = new PDO('mysql:host=localhost;dbname=java_db', 'root', 'pass');
$Sql = "SELECT count(*) as `total` FROM users";
$stmt = $db->query($Sql);
$stmt->execute();
$total = $stmt->fetch(PDO::FETCH_ASSOC);
print '<pre>';
print_r($total);
print '</pre>';

Result:

edited Nov 21 '20 at 20:52

Dharman

30,962
25
85
135

answered Sep 19 '19 at 13:11

Ram Pukar

1,583
15
17

What's the need for line 5 ? The compiler/IDE should warn about unused '$data' variable. – ManuelJE Oct 20 '20 at 12:12

score 1 · Answer 11 · answered Dec 26 '18 at 12:14

1

You need to alias the aggregate using the as keyword in order to call it from mysqli_fetch_assoc

$result=mysqli_query($conn,"SELECT count(*) as total from Students");
$data=mysqli_fetch_assoc($result);
echo $data['total'];

answered Dec 26 '18 at 12:14

Sani Kamal

1,208
16
26

Umut Sürmeli · Answer 12 · 2015-12-01T20:08:01.220

0

 $howmanyuser_query=$conn->query('SELECT COUNT(uno)  FROM userentry;');
 $howmanyuser=$howmanyuser_query->fetch_array(MYSQLI_NUM); 
 echo $howmanyuser[0];

after the so many hours excellent :)

edited Dec 01 '15 at 20:08

answered Dec 01 '15 at 18:51

Umut Sürmeli

19
2

select count(*) from table of mysql in php

12 Answers12

Linked

Related