Jump to content


Photo

How to display total # of records


  • Please log in to reply
3 replies to this topic

#1 phipsi545

phipsi545
  • New Members
  • Pip
  • Newbie
  • 1 posts

Posted 05 October 2003 - 09:22 PM

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

#2 Kriek

Kriek
  • Members
  • PipPipPip
  • Advanced Member
  • 31 posts
  • LocationFlorida

Posted 05 October 2003 - 11:58 PM

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]
Jon Kriek · PHP Freaks
Sometimes the need to mess with their heads ...
... outweighs the millstone of humiliation.


#3 PHPcadet

PHPcadet
  • Members
  • PipPip
  • Member
  • 14 posts
  • LocationPlant City, Florida

Posted 06 October 2003 - 04:09 PM

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]
If builders built buildings the way programmers write programs, then the first termite to come along would destroy civilization -- Anonymous

#4 Kriek

Kriek
  • Members
  • PipPipPip
  • Advanced Member
  • 31 posts
  • LocationFlorida

Posted 06 October 2003 - 07:37 PM

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]
Jon Kriek · PHP Freaks
Sometimes the need to mess with their heads ...
... outweighs the millstone of humiliation.





0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users