Page 1 of 1
Total number of rows
Posted: Wed Jun 14, 2006 11:28 pm
by SidewinderX
How to i store the total number of rows i have in a particular table as a variable?
Posted: Wed Jun 14, 2006 11:32 pm
by bdlang
What's the context? Rows in a database table? What database? Please be specific.
Posted: Thu Jun 15, 2006 12:07 am
by SidewinderX
the database name is 'nuke' the table i called 'nsnts_categories'
every time i add information through a backend, the information is stored in this table, each new piece of information is given a number which is 1 more then the previous. Its a real basic operation. The first entry is 1, the second is 2, ect....
I would like to create something that calculates the total number of entrys in that table so i can then create an if/else statement.
Code: Select all
$next = $tid+1;
if($next < $totalentrys) {
$forward = "<a href=\"modules.php?name=Theme_System&op=TSDetails&tid=" . $next . "\">Next</a>";
}
else {
$forward = "This is the last entry.";
}
so i need the $totalentrys so i can compare...
Posted: Thu Jun 15, 2006 1:10 am
by bdlang
Ok, since you didn't mention, I'll assume MySQL, the most popular RDBMS.
Code: Select all
$sql= 'SELECT COUNT(*) AS total FROM nsnts_categories';
$total= mysql_result(mysql_query($sql), 0, 'total');
SQL statement counts the number of total records in the table, mysql_query() queries the database with said statement, and mysql_result() pulls the data (in this case the value of the COUNT()).
Posted: Thu Jun 15, 2006 3:54 am
by www.freemysql.net
Pimptastic | Please use Code: Select all
and [syntax="..."] tags where appropriate when posting code. Your post has been edited to reflect how we'd like it posted. Please read: [url=http://forums.devnetwork.net/viewtopic.php?t=21171]Posting Code in the Forums[/url] to learn how to do it too.[/color]
Code: Select all
//connect to MySQL
$connect= mysql_connect("$cfg_host", "$cfg_username", "$cfg_password");
mysql_select_db("$cfg_database", $connect);
//Count Rows
$result = mysql_query("SELECT * FROM `nsnts_categories` WHERE `feild`='1' ", $connect);
$total_count = mysql_num_rows($result);
$total_count, would be your variable for number of rows in the table.
*NOTE: If you wish to just count select rows use the WHERE, you can also leave out the WHERE if you wish to select all rows in a table.
Code: Select all
//without WHERE
$result = mysql_query("SELECT * FROM `nsnts_categories`", $connect);
$total_count = mysql_num_rows($result);
Pimptastic | Please use Code: Select all
and [syntax="..."] tags where appropriate when posting code. Your post has been edited to reflect how we'd like it posted. Please read: [url=http://forums.devnetwork.net/viewtopic.php?t=21171]Posting Code in the Forums[/url] to learn how to do it too.[/color]
Posted: Thu Jun 15, 2006 8:41 am
by bdlang
FYI, running a query on the database to
select all records is highly inefficient compared to running a query to retrieve
a single result of the record count; the more records you have, the less efficient it becomes. Use
mysql_num_rows() when you actually want to retrieve data from the resultset, to count the results returned. Use COUNT() to retrieve the count (note you can combine any JOIN statement, WHERE clause or otherwise in your COUNT() statement to retrieve a specific count of that resultset).