|
|
Posted by Andy Hassall on 12/31/06 16:18
On Sun, 31 Dec 2006 10:32:02 -0500, bill <nobody@spamcop.net> wrote:
>Andy Hassall wrote:
>> On Sat, 30 Dec 2006 17:04:58 -0500, bill <nobody@spamcop.net> wrote:
>>
>>> I am sure there must be an easy way to determine the number of
>>> rows in a table, but I can't find it.
>>>
>>> I appreciate the courtesy and patience ng members have shown this
>>> mysql novice.
>>
>> select count(*) from your_table
>
>$total_rows = mysql_query ("SELECT COUNT(*) FROM Kennel");
>
>gives me a resource, not the count.
>
>Fine, so I use:
>
>$total_rows = mysql_query ("SELECT COUNT(*) FROM Kennel");
>echo "Rows in table: " . mysql_num_rows ($total_rows) . "<br />";
>
>but that returns 1, and there are 4 rows in the table.
The query gives a 1 column result set with 1 row, which will have the count in
it. mysql_num_rows gives you the number of rows in a result set - therefore
it's 1 here.
If you fetched the first row with mysql_fetch_array or one of the variants of
it then you can get at the value for the first column in the row - which will
be 4.
>However,
>
>$sql = "Select id, kennel_name, location, real_names from Kennel
> ORDER BY REPLACE(kennel_name,'The ','') LIMIT 100";
>$result = mysql_query($sql, $connection) or die(mysql_error());
>echo "Rows in table-2: " . mysql_num_rows ($result) . "<br />";
>
>does give the correct answer.
It doesn't really, since you have a LIMIT clause in there, so if the number of
rows in the table goes over 100 it'll always say 100. It also fetches all the
columns you named out of the database into PHP, and does some ordering.
Perhaps you've asked the wrong question for what you want; if you want the
number of rows in the table but don't need the data itself, then you use an SQL
query such as "select count(*) from t" as above to get you a result set
containing a row with the count in it.
If you have already fetched the data as a result set from an SQL statement
because you need it at the same time, but you want to know how many rows are in
the result set you've fetched (which rarely corresponds exactly to a table
anyway since you'll be doing filters and joins) then you can use mysql_num_rows
on a result set resource.
--
Andy Hassall :: andy@andyh.co.uk :: http://www.andyh.co.uk
http://www.andyhsoftware.co.uk/space :: disk and FTP usage analysis tool
Navigation:
[Reply to this message]
|