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 221 · Accepted Answer · edited May 12 '13 at 20:52

221

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,206
11
83
119

answered Aug 02 '11 at 05:48

Shakti Singh

81,083
20
131
150

10

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

12,667
4
75
88

2

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

@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 16 · Answer 3 · edited Mar 18 '16 at 14:56

16

$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

17,070
21
157
242

answered Aug 02 '11 at 05:48

avetarman

1,234
9
8

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

13

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

26,923
21
73
125

answered Aug 19 '15 at 16:29

Lee Davis

4,507
2
26
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,061
14
25

answered May 26 '18 at 13:26

Raja Sekhar

101
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

5,840
11
54
82

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

3

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

26,923
21
73
125

answered Aug 02 '17 at 22:19

MK Smith

101
9

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 1 · Answer 10 · 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,061
14
25

score 1 · Answer 11 · edited Nov 21 '20 at 20:52

1

$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

26,923
21
73
125

answered Sep 19 '19 at 13:11

Ram Pukar

1,377
11
15

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

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