How to display total # of records

[phipsi545]

I have a table with an auto_increment column. I\'d like to be able to find the highest number there (basically, I need to display the total number of records in a table) and put that number into a variable.

 

Is there a way to do this?

 

Basically:

 

ID    Name

1     Thomas

2     Chris

3     Jonathan

 

and have \"3\" put into $totalrecords

[Kriek]

See the SQL functions SUM() and/or COUNT()

 

SELECT SUM(ID) AS totalrecords FROM table

 

Then access totalrecords from the mysql_fetch_assoc() function

 

[php:1:826a0f1673]<?php

$conn = mysql_connect(\'dbhost\', \'dbuser\', \'dbpass\') or die(mysql_error());

mysql_select_db(\'dbname\', $conn) or die(mysql_error());

$query = \"SELECT SUM(ID) AS totalrecords FROM table\";

$result = mysql_query($query, $conn) or die(mysql_error());

if (mysql_num_rows($result) > 0) {

$row = mysql_fetch_assoc($result) or die(mysql_error());

$totalrecords = $row[\'totalrecords\'];

echo \'Total Records \' . $totalrecords;

}

mysql_close($conn) or die(mysql_error());

?>[/php:1:826a0f1673]

[PHPcadet]

If I\'m not mistaken this will give you the sum of the ID field which is 1 + 2 + 3 = 6. I think you should use the COUNT function. Change this line:

    $query = "SELECT SUM(ID) AS totalrecords FROM table"; 

To this:

    $query = "SELECT COUNT(*) AS totalrecords FROM table"; 

Or you could simplify it using the mysql_num_rows function like this:

[php:1:9228aa23a2]<?php

$conn = mysql_connect(\'dbhost\', \'dbuser\', \'dbpass\') or die(mysql_error());

mysql_select_db(\'dbname\', $conn) or die(mysql_error());

$query = \"SELECT * FROM table\";

$result = mysql_query($query, $conn) or die(mysql_error());

$totalrecords = mysql_num_rows($result);

echo \'Total Records \' . $totalrecords;

mysql_close($conn) or die(mysql_error());

?>[/php:1:9228aa23a2]

[Kriek]

Right in this case use COUNT() rather than SUM(), however as long as you have an AUTO_INCREMENT field asterisks are NOT needed. Also note that COUNT(expr) returns a count of the number of non-NULL values in the rows retrieved by a SELECT statement, while COUNT(*) returns a count of the number of rows retrieved, whether or not they contain NULL values. Do not be tempted to use mysql_num_rows(), using COUNT(expr) or COUNT(*) is a lot faster.

 

[php:1:3b0788305a]<?php

$conn = mysql_connect(\'dbhost\', \'dbuser\', \'dbpass\') or die(mysql_error());

mysql_select_db(\'dbname\', $conn) or die(mysql_error());

$query = \"SELECT COUNT(ID) AS totalrecords FROM table\";

$result = mysql_query($query, $conn) or die(mysql_error());

$row = mysql_fetch_assoc($result) or die(mysql_error());

$totalrecords = $row[\'totalrecords\'];

echo \'Total Records \' . $totalrecords;

mysql_close($conn) or die(mysql_error());

?>[/php:1:3b0788305a]