How many rows in a table

[bill]

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.

bill

[Andy Hassall]

Re: How many rows in a table

On Sat, 30 Dec 2006 17:04:58 -0500, bill <nobody@spamcop .netwrote:

>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

--
Andy Hassall :: andy@andyh.co.u k :: http://www.andyh.co.uk
http://www.andyhsoftware.co.uk/space :: disk and FTP usage analysis tool

[bill]

Re: How many rows in a table

Andy Hassall wrote:

On Sat, 30 Dec 2006 17:04:58 -0500, bill <nobody@spamcop .netwrote:
>

>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.
However,

$sql = "Select id, kennel_name, location, real_names from Kennel
ORDER BY REPLACE(kennel_ name,'The ','') LIMIT 100";
$result = mysql_query($sq l, $connection) or die(mysql_error ());
echo "Rows in table-2: " . mysql_num_rows ($result) . "<br />";

does give the correct answer.

what am I doing wrong with the first query ?

[GT]

Re: How many rows in a table

bill wrote:

Andy Hassall wrote:

>On Sat, 30 Dec 2006 17:04:58 -0500, bill <nobody@spamcop .netwrote:
>>

>>I am sure there must be an easy way to determine the number of rows
>>in a table, but I can't find it.
>>>

>>
> select count(*) from your_table
>>

>
$total_rows = mysql_query ("SELECT COUNT(*) FROM Kennel");
>
gives me a resource, not the count.

I guess it would. If you were to do it that way:

$result = mysql_query ("SELECT COUNT(*) FROM Kennel");
$array = mysql_fetch_arr ay($result);
$rows = $array[0];

(or similar - I've not tested, but something akin to that will work)

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.

No no!
$result = mysql_query ("SELECT * FROM Kennel");
$rows = mysql_num_rows ($result);

HTH
--
GT

[Andy Hassall]

Re: How many rows in a table

On Sun, 31 Dec 2006 10:32:02 -0500, bill <nobody@spamcop .netwrote:

>Andy Hassall wrote:

>On Sat, 30 Dec 2006 17:04:58 -0500, bill <nobody@spamcop .netwrote:
>>

>>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_arr ay 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($sq l, $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.u k :: http://www.andyh.co.uk
http://www.andyhsoftware.co.uk/space :: disk and FTP usage analysis tool

[bill]

Re: How many rows in a table

Andy Hassall wrote:

On Sun, 31 Dec 2006 10:32:02 -0500, bill <nobody@spamcop .netwrote:
>

>Andy Hassall wrote:

>>On Sat, 30 Dec 2006 17:04:58 -0500, bill <nobody@spamcop .netwrote:
>>>
>>>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_arr ay 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($sq l, $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

[bill]

Re: How many rows in a table

Andy Hassall wrote:

On Sun, 31 Dec 2006 10:32:02 -0500, bill <nobody@spamcop .netwrote:
>

>Andy Hassall wrote:

>>On Sat, 30 Dec 2006 17:04:58 -0500, bill <nobody@spamcop .netwrote:
>>>
>>>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_arr ay 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.
>

Ah, works like a charm.
Thanks. I appreciate your assistance a lot.

bill

[bill]

Re: How many rows in a table

GT wrote:

bill wrote:

>Andy Hassall wrote:

>>On Sat, 30 Dec 2006 17:04:58 -0500, bill <nobody@spamcop .netwrote:
>>>
>>>I am sure there must be an easy way to determine the number of rows
>>>in a table, but I can't find it.
>>>>
>>>
>> select count(*) from your_table
>>>

>>
>$total_rows = mysql_query ("SELECT COUNT(*) FROM Kennel");
>>
>gives me a resource, not the count.

>
I guess it would. If you were to do it that way:
>
$result = mysql_query ("SELECT COUNT(*) FROM Kennel");
$array = mysql_fetch_arr ay($result);
$rows = $array[0];
>

yup, worked fine.

Many thanks.

bill

[Jerry Stuckle]

Re: How many rows in a table

GT wrote:

bill wrote:
>

>Andy Hassall wrote:
>>

>>On Sat, 30 Dec 2006 17:04:58 -0500, bill <nobody@spamcop .netwrote:
>>>
>>>I am sure there must be an easy way to determine the number of rows
>>>in a table, but I can't find it.
>>>>
>>>
>> select count(*) from your_table
>>>

>>
>$total_rows = mysql_query ("SELECT COUNT(*) FROM Kennel");
>>
>gives me a resource, not the count.

>
>
I guess it would. If you were to do it that way:
>
$result = mysql_query ("SELECT COUNT(*) FROM Kennel");
$array = mysql_fetch_arr ay($result);
$rows = $array[0];
>

Yes, this would be the correct way to do it.

(or similar - I've not tested, but something akin to that will work)
>

>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.

>
>
No no!
$result = mysql_query ("SELECT * FROM Kennel");
$rows = mysql_num_rows ($result);
>

Definitely not! What if the table has 10M rows? You're asking they all
be returned to the program so they can be counted.

Let MySQL do the counting. That's what COUNT(*) does.

HTH

--
=============== ===
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex@attgl obal.net
=============== ===

[Mateusz Papiernik]

Re: How many rows in a table

Jerry Stuckle wrote:

Definitely not! What if the table has 10M rows? You're asking they all
be returned to the program so they can be counted.

I guess that was just the idea how to make the thing mentioned by bill
work - not the advice for using it :) However, you are certainly right -
the right way to do that is COUNT(*) built-in.


--
Mateusz Papiernik, Maticomp Webdesign
mati@maticomp.n et, http://www.maticomp.net
"One man can make a difference" - Wilton Knight