|
|
Posted by bill on 12/31/06 16:22
Andy Hassall wrote:
> 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.
>
Actually I need both. I need the total rows initially to
structure the next query. The limit 100 is a stopgap while I am
writing the rest of it.
The general idea is that for up to 100 rows I will present it as
one html table, over 100 I will divide it alphabetically to have
each segment be under 100.
Thanks
bill
Navigation:
[Reply to this message]
|