Counting the number of records in a database table?

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
Netroxy
Forum Commoner
Posts: 29
Joined: Sat Apr 09, 2011 5:38 pm

Counting the number of records in a database table?

Post by Netroxy »

Hello. I'm having a problem how to count how many records there are in my mysql table with 'SELECT COUNT(*) FROM table_name'. the column is 'fldID', and I want to count the number of records from the 'fldID' column. The result should be like this:

'There are currently 15000 games on this website', where 15000 is the number of records in the database.

I also can't seem to put it around with <? php ?>
fugix
Forum Contributor
Posts: 207
Joined: Fri Mar 18, 2011 8:01 pm

Re: Counting the number of records in a database table?

Post by fugix »

i would make a query, retrieving all the rows that there is a value for fldID, then i would echo the number of rows using the mysql_num_rows() function
Netroxy
Forum Commoner
Posts: 29
Joined: Sat Apr 09, 2011 5:38 pm

Re: Counting the number of records in a database table?

Post by Netroxy »

Can you show an example please?
fugix
Forum Contributor
Posts: 207
Joined: Fri Mar 18, 2011 8:01 pm

Re: Counting the number of records in a database table?

Post by fugix »

Code: Select all

$query = mysql_query("select * from table_name where field='fldID'");
$num_rows = mysql_num_rows($query);
echo $num_rows;
Netroxy
Forum Commoner
Posts: 29
Joined: Sat Apr 09, 2011 5:38 pm

Re: Counting the number of records in a database table?

Post by Netroxy »

Would this be correct?

<?php
{
$conn = mysql_connect("host", "username", "password");
mysql_select_db("database");
$query = mysql_query("select * FROM games WHERE field='fldID'");
$num_rows = mysql_num_rows($query);
echo $num_rows;
}
?>

<?php echo '<font face="Arial" color="#FF0000">' . $num_rows. '</font>'; ?>
fugix
Forum Contributor
Posts: 207
Joined: Fri Mar 18, 2011 8:01 pm

Re: Counting the number of records in a database table?

Post by fugix »

one second...is fldID your column name? and values are stored under that column?
Netroxy
Forum Commoner
Posts: 29
Joined: Sat Apr 09, 2011 5:38 pm

Re: Counting the number of records in a database table?

Post by Netroxy »

Yes that's correct. I'm thinking you can count total records for an entire database using 1 column? Or is there an easier or quicker way to do this? I'm still new to PHP so I'm learning. I was thinking it wouldn't be searching for rows, but rather total records in database.

Example:



fldID |fldName
==================
01 |John
02 |Big Sally
03 |Tiny Tim
04 |(Normal Ned


Basically a code that will retrieve the number of records. So The results would be a total of 4 records in the database and displayed as text in the webpage.

Your also right about echoing out the number of records, like using: <?php echo '<font face="Arial" color="#FF0000">' . $records . '</font>'; ?>
Last edited by Netroxy on Thu Apr 21, 2011 4:08 pm, edited 1 time in total.
fugix
Forum Contributor
Posts: 207
Joined: Fri Mar 18, 2011 8:01 pm

Re: Counting the number of records in a database table?

Post by fugix »

ok then what you want is this.

Code: Select all

$query = mysql_query("select * from games where fldID!=''");
$num_rows = mysql_num_rows($query);
echo $num_rows;
basically your telling it to get all the rows where fldID has a value..then display the number of rows where this occurs..is that what you are looking for?
Netroxy
Forum Commoner
Posts: 29
Joined: Sat Apr 09, 2011 5:38 pm

Re: Counting the number of records in a database table?

Post by Netroxy »

Yes that's correct.I currently have about 30 games in my database, and I would like to display on the website that there are 30 games available from the mysql database. The fldID column holds about 30 ID numbers for the 30 games
Netroxy
Forum Commoner
Posts: 29
Joined: Sat Apr 09, 2011 5:38 pm

Re: Counting the number of records in a database table?

Post by Netroxy »

And thanks a lot. it works!! :D
User avatar
califdon
Jack of Zircons
Posts: 4484
Joined: Thu Nov 09, 2006 8:30 pm
Location: California, USA

Re: Counting the number of records in a database table?

Post by califdon »

"Rows" and "Records" mean precisely the same thing. If you really want a count of how many records (rows) are in a table, it's simpler than any of the above. There is no need for a WHERE clause unless you want to count the number of records where some condition exists (= some value, or != some value, or < some value, etc.). Theoretically, it would run a tiny bit faster if you only select a single column (ANY column), but you probably couldn't measure the difference unless your table held millions of records. So this would suffice:

Code: Select all

$query = mysql_query("select * from games");
$num_rows = mysql_num_rows($query);
echo $num_rows;
Netroxy
Forum Commoner
Posts: 29
Joined: Sat Apr 09, 2011 5:38 pm

Re: Counting the number of records in a database table?

Post by Netroxy »

Hello Jack. For the code that you placed, would this work:

<?php
{
$conn = mysql_connect("host", "username", "password");
mysql_select_db("database");
$query = mysql_query("select * FROM games");
$num_rows = mysql_num_rows($query);
echo $num_rows;
}
?>
<?php echo '<font face="Arial" color="#FF0000">' . $num_rows. '</font>'; ?>
User avatar
califdon
Jack of Zircons
Posts: 4484
Joined: Thu Nov 09, 2006 8:30 pm
Location: California, USA

Re: Counting the number of records in a database table?

Post by califdon »

First of all, there's no purpose in closing the PHP tag and immediately opening it again.

Second, unless you want to repeat the number, there's no purpose in echoing it twice.

Third, you can simplify the syntax by using double-quotes for the echo, which will then interpret simple variables without using concatenation.

So this is all you need:

Code: Select all

<?php
$conn = mysql_connect("host", "username", "password");
mysql_select_db("database");
$query = mysql_query("select * FROM games");
$num_rows = mysql_num_rows($query);
echo "<font face='Arial' color='#FF0000'> $num_rows</font>"; 
?>
Finally, don't be afraid to TRY things. Instead of asking whether it will work, just enter those couple of lines and see what happens!
fugix
Forum Contributor
Posts: 207
Joined: Fri Mar 18, 2011 8:01 pm

Re: Counting the number of records in a database table?

Post by fugix »

Glad I could help
Post Reply