Page 1 of 1
quickly get number of rows in a table
Posted: Tue Dec 10, 2002 9:26 pm
by Sevengraff
I looked in the php manual, and there didn't seem to be a function for this.
The way I do it is to have a query that will run throught all the rows
$sql = "SELECT * FROM USERS";
and then use mysql_affected_rows as my count.
but this seems like an inefficent way of doing it. how should i do it?
Posted: Tue Dec 10, 2002 10:03 pm
by nieve
mysql_num_rows?
$query = "SELECT * FROM users";
$query1 = mysql_query($query);
$q_r = mysql_num_rows($query1);
echo "$q_r";
Posted: Tue Dec 10, 2002 10:30 pm
by Sevengraff
Is that way any better?
Posted: Tue Dec 10, 2002 10:38 pm
by hedge
select count(*) from tablename
Posted: Wed Dec 11, 2002 8:05 am
by Rob the R
You can improve performance
slightly if you know of a column in your table that has a value (is non-null) for every row. Then you can use:
Code: Select all
select count(column_name) from tablename
Posted: Wed Dec 11, 2002 11:47 am
by Sevengraff
iv'e never used count() before, so i'll read up on it and just keep doing what i do for now. Thanks for the replies.
COUNT() function revisited
Posted: Tue Dec 17, 2002 2:53 pm
by puckeye
HI,
Is there a quick way to count the total rows that would be returned when LIMIT is used.
I need to do something like XX out of XXX records.
Code: Select all
SELECT * FROM TABLE WHERE X = Y LIMIT 0, 10
Taking into account that there is more then 10 records meeting the X = Y condition how would I know how many actually met the condition without having to call a second query like
Thanks for help
Posted: Tue Dec 17, 2002 4:17 pm
by Sevengraff
i think you could just use mysql_affected_rows.
Code: Select all
$sql = "SELECT * FROM TABLE WHERE X = Y LIMIT 0, 10";
$runsql = mysql_query($sql);
$returned_rows=mysql_affected_rows();
Posted: Mon Jan 20, 2003 1:47 pm
by puckeye
It took a long time for me to get back to this thread... LOL
mysql_affected_rows(); can only be used to count the number of INSERT, UPDATE and DELETE not SELECT.
Thanks though
Posted: Mon Jan 20, 2003 3:02 pm
by fractalvibes
select count(*) from table where X=Y
Much more efficient than select * and checking numrows.
With select count(*) you just return an integer, with select (*) you return
the entire table - unnecessary network traffic with that!
Phil J.
Posted: Mon Jan 20, 2003 3:12 pm
by puckeye
fractalvibes wrote:select count(*) from table where X=Y
Much more efficient than select * and checking numrows.
With select count(*) you just return an integer, with select (*) you return
the entire table - unnecessary network traffic with that!
Phil J.
But that means a second query.
Posted: Mon Jan 20, 2003 4:51 pm
by fractalvibes
If you are just wanting a count, the one query should suffice.