• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 485
  • Last Modified:

MySQL Query Entry Count

Hey all,

I'm new to mysql and I have been successfull thus far. I want to add a little feature basically that will query one of my tables for a count of how many entries have been made.

PHP 5 w/ MySQL 4.0

Basically a query that will output how many rows of entries it finds. Any help would be greatly appreciated.

Thanks in advance!
0
ValleyENT
Asked:
ValleyENT
3 Solutions
 
b0lsc0ttCommented:
ValleyENT,

Use ...

SELECT COUNT(*) FROM tablename;

Let me know if you have any questions or need more information.

b0lsc0tt
0
 
shobinsunCommented:
Hello,

you can use either:

Select count (*) from tablename where condition

or

use  mysql_num_rows() function.

ex:

$result=mysql_query('select * from tablename');
echo mysql_num_rows($result);


Regards.
0
 
racekCommented:
be carefull

COUNT(*) - no of all rows
COUNT(column_name) - all not null values
COUNT(distinct coumn_name) - distinct number of non null values
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
Ray PaseurCommented:
And to add to racek's important word of advice, COUNT(*) with a WHERE clause or a LIMIT clause will give you a count of the results set instead of a count of all the rows.

You can use mysql_num_rows() to determine the number of records in a results set.  You can also look for COUNT individually.

http://us.php.net/manual/en/function.mysql-num-rows.php

Some MySQL teaching examples are in the code snippet.  Look it over and post back here if you have any questions.

HTH, ~Ray
<?php // RAY_mysql_example.php
error_reporting(E_ALL);
 
// IMPORTANT PAGES FROM THE MANUALS
// MAN PAGE: http://us2.php.net/manual/en/ref.mysql.php
// MAN PAGE: http://us2.php.net/manual/en/mysql.installation.php
 
 
 
 
// CONNECTION AND SELECTION VARIABLES FOR THE DATABASE
$db_host = "localhost"; // PROBABLY THIS IS OK
$db_name = "??";        // GET THESE FROM YOUR HOSTING COMPANY
$db_user = "??";
$db_word = "??";
 
// OPEN A CONNECTION TO THE DATA BASE SERVER
// MAN PAGE: http://us2.php.net/manual/en/function.mysql-connect.php
if (!$db_connection = mysql_connect("$db_host", "$db_user", "$db_word"))
{
   $errmsg = mysql_errno() . ' ' . mysql_error();
   echo "<br/>NO DB CONNECTION: ";
   echo "<br/> $errmsg <br/>";
}
 
// SELECT THE MYSQL DATA BASE
// MAN PAGE: http://us2.php.net/manual/en/function.mysql-select-db.php
if (!$db_sel = mysql_select_db($db_name, $db_connection))
{
   $errmsg = mysql_errno() . ' ' . mysql_error();
   echo "<br/>NO DB SELECTION: ";
   echo "<br/> $errmsg <br/>";
   die('NO DATA BASE');
}
// IF WE GOT THIS FAR WE CAN DO QUERIES
 
 
 
 
// ESCAPING A DATA FIELD FOR USE IN MYSQL QUERIES
// MAN PAGE: http://us2.php.net/manual/en/function.mysql-real-escape-string.php
$username = mysql_real_escape_string($_POST["username"]);
 
 
 
 
// CREATING AND SENDING A SELECT QUERY AND TESTING THE RESULTS
// MAN PAGE:http://us2.php.net/manual/en/function.mysql-query.php
$sql = "SELECT id FROM my_table WHERE username='$username'";
$res = mysql_query($sql);
 
// IF mysql_query() RETURNS FALSE, GET THE ERROR REASONS
if (!$res)
{
   $errmsg = mysql_errno() . ' ' . mysql_error();
   echo "<br/>QUERY FAIL: ";
   echo "<br/>$sql <br/>";
   die($errmsg);
}
// IF WE GET THIS FAR, THE QUERY SUCCEEDED AND WE HAVE A RESOURCE-ID IN $res SO WE CAN NOW USE $res IN OTHER MYSQL FUNCTIONS
 
 
 
 
// DETERMINE HOW MANY ROWS OF RESULTS WE GOT
// MAN PAGE: http://us2.php.net/manual/en/function.mysql-num-rows.php
$num = mysql_num_rows($res);
if (!$num)
{
   echo "<br/>QUERY FOUND NO DATA: ";
   echo "<br/>$sql <br/>";
}
else
{
   echo "<br/>QUERY FOUND $num ROWS OF DATA ";
   echo "<br/>$sql <br/>";
}
 
 
 
 
// A WAY OF DETERMINING HOW MANY ROWS WE HAVE IN A TABLE
// MAN PAGE:http://us.php.net/mysql_fetch_array
$sql = "SELECT COUNT(*) FROM my_table";
$res = mysql_query($sql);
 
// IF mysql_query() RETURNS FALSE, GET THE ERROR REASONS
if (!$res)
{
   $errmsg = mysql_errno() . ' ' . mysql_error();
   echo "<br/>QUERY FAIL: ";
   echo "<br/>$sql <br/>";
   die($errmsg);
}
// GET THE RESULTS SET ROW IN AN ARRAY WITH A NUMERIC INDEX - POSITION ZERO IS THE COUNT
$row = mysql_fetch_array($res, MYSQL_NUM);
$num = $row[0];
 
 
 
 
// ITERATE OVER THE RESULTS SET TO SHOW WHAT WE FOUND
// MAN PAGE: http://us2.php.net/manual/en/function.mysql-fetch-assoc.php
echo "<pre>\n"; // MAKE IT EASY TO READ
while ($row = mysql_fetch_assoc($res))
{
   var_dump($row); // MAN PAGE: http://us2.php.net/manual/en/function.var-dump.php
}
 
 
 
 
// MAKING AN INSERT QUERY AND TESTING THE RESULTS
$sql = "INSERT INTO my_table (username) VALUES (\"$username\")";
$res = mysql_query($sql);
 
// IF mysql_query() RETURNS FALSE, GET THE ERROR REASONS
if (!$res)
{
   $errmsg = mysql_errno() . ' ' . mysql_error();
   echo "<br/>QUERY FAIL: ";
   echo "<br/>$sql <br/>";
   die($errmsg);
}
// IF WE GET THIS FAR, THE QUERY SUCCEEDED AND WE HAVE A RESOURCE-ID IN $res SO WE CAN NOW USE $res IN OTHER MYSQL FUNCTIONS
 
// GET THE AUTO_INCREMENT ID OF THE RECORD JUST INSERTED
// MAN PAGE: http://us2.php.net/manual/en/function.mysql-insert-id.php
$id  = mysql_insert_id($res);

Open in new window

0
 
b0lsc0ttCommented:
ValleyENT,
Please post and let us know why you accepted just the one expert's comment.  The comment you accepted is great but did build on other expert's posts.  Ray gave credit but if you used COUNT or mysql_num_rows then this should probably be split.  If you made a mistake or didn't know you could accept more than one comment then just let us know.  If there was some reason for the way you closed it then please explain.
Thanks!
bol
0
 
ValleyENTAuthor Commented:
Quick and easy, thanks.
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now