Is there any way to tell user how many rows are in a table?

Question

Let's say I have a table full of names and I want users to know how many names are there.

What would be the output process be?

Answer 1

$result = mysql_query('SELECT Count(*) FROM table') or die('error');
$count = mysql_result($result, 0, 'count(*)');
echo $count;

Answer 2

If you're filling the table from an array, you can use count($array) in your php code to list the number of elements in the array.

Answer 3

If you wanted unique names only,

SELECT COUNT(DISTINCT name_column) FROM table_name

Answer 4

<?php
    $r = mysql_query('SELECT COUNT(*) as cnt FROM table;');
    $v = mysql_fetch_assoc($r);
    echo 'There are ' . $v['cnt'] . ' rows in the table.';
?>

Answer 5

Just in case you're implementing some kind of pagination and want to also show the total amount of records you could use MySQL's FOUND_ROWS().
e.g.

$start = $page*$itemsPerPage;
$query = "
    SELECT SQL_CALC_FOUND_ROWS
     x,y,z
    FROM
     foo
    LIMIT
     $start, $itemsPerPage
";
$result = mysql_query($query, $mysql) or die(mysql_error());
while( false!==($row=mysql_fetch_array($result, MYSQL_ASSOC)) ) {
    echo
     htmlspecialchars($row['x'], ENT_QUOTES, 'utf-8'), ' ',
     htmlspecialchars($row['y'], ENT_QUOTES, 'utf-8'), ' ',
     htmlspecialchars($row['z'], ENT_QUOTES, 'utf-8'), ' ',
     "<br />\n";
}

$query = 'SELECT FOUND_ROWS()';
$result = mysql_query($query, $mysql) or die(mysql_error());
$total = mysql_result($result, 0, 0);
echo $total, ' record(s) found';

Answer 6

You can simply do:

$sql = 'SELECT Count(`id`) as c FROM table';
$ok = mysql_query($sql);
if($ok){
   $v = mysql_fetch_assoc($ok);
   echo 'There are '.$v['c'].' names in the table.';
}else{
   echo 'MySQL error: '.mysql_error();
}