Page 1 of 1
how to count rows in mysql?
Posted: Mon May 24, 2004 7:01 am
by kevin7
how can i know the total of row in a table by using sql?
Posted: Mon May 24, 2004 7:09 am
by duk
i use mysql_affected_rows();
Posted: Mon May 24, 2004 7:15 am
by mendingo
I'd use:
Code: Select all
$result = mysql_query("Select * from table");
$numRows = mysql_num_rows($result);
Posted: Mon May 24, 2004 7:15 am
by d3ad1ysp0rk
Code: Select all
<?php
$result = mysql_query("SELECT * FROM table");
echo mysql_num_rows($result);
?>
edit: beat to it

Posted: Mon May 24, 2004 2:10 pm
by magicrobotmonkey
Code: Select all
<?php
$query = "SELECT COUNT(*) FROM tablename";
?>
Posted: Mon May 24, 2004 3:41 pm
by John Cartwright
duk wrote:i use mysql_affected_rows();
Thats bad practice, uses a lot of CPU that you shouldn't be and don't ahve to.
Don't select everything
Posted: Mon May 24, 2004 10:22 pm
by hydrocomputer
select * from table
is not only horrendous for the client, it hits EVERY SINGLE ROW IN THE TABLE.
select count(*) from table
is the only clean way to go, and incidentally it works on every SQL, not just MYsql.
If you tried some of the other variants on a 1-million row table, you'd have serious problems.
Re: Don't select everything
Posted: Tue May 25, 2004 2:52 am
by Radical
hydrocomputer wrote:select * from table
is not only horrendous for the client, it hits EVERY SINGLE ROW IN THE TABLE.
If you're on MySQL (maybe aplicable to others) you can try:
SHOW TABLE STATUS LIKE `tablename`
As an answer you'll get a lot of columns:
Name, Type, Row_format, Rows, Avg_row_length, Data_length, Max_data_length, Index_length, Data_free, Auto_increment, Create_time, Update_time, Check_time, Create_options, Comment.
You are interested in: Rows.
I do not remember, though, if SHOW TABLE STATUS opens the table... if it does there would be problems with big tables (over 1 milion entries...) and only if "Row_format" is DYNAMIC... cuz' FIXED tables are very fast. If it only opens the table definition file (as in "SHOW COLUMNS FROM `tablename`") then you'll see:
0 rows affected; 15 field(s); 1 record(s); Time: 0,00 sec
Preety neat... the part with
Time: 0,00 sec