Counting rows in mysql

[Matt Schroeder]

Does anyone know how to count how many rows are in a mysql table? This is
what I have, but it doesn't work right:

<?
$db = mysql_connect(" localhost", "username", "password") ;
mysql_select_db ("database",$db );
$sql = "SELECT COUNT(*) FROM table";
$result = mysql_query($sq l);
echo "$result";
?>

This returns "Resource id #3". All it should say is '2'. Can anyone tell me
what I'm doing wrong?

Thanks,
Matt

[Andy Hassall]

Re: Counting rows in mysql

On Thu, 17 Jul 2003 23:10:29 GMT, "Matt Schroeder" <wnawombat41@co mcast.net>
wrote:
[color=blue]
>Does anyone know how to count how many rows are in a mysql table? This is
>what I have, but it doesn't work right:
>
><?
>$db = mysql_connect(" localhost", "username", "password") ;
>mysql_select_d b("database",$d b);
>$sql = "SELECT COUNT(*) FROM table";
>$result = mysql_query($sq l);
>echo "$result";
>?>
>
>This returns "Resource id #3". All it should say is '2'. Can anyone tell me
>what I'm doing wrong?[/color]

What makes you think that mysql_query should return 2?

Please read the manual: http://uk.php.net/mysql_query

The next to last paragraph in particular.

"Only for SELECT,SHOW,DES CRIBE or EXPLAIN statements, mysql_query() returns a
new result identifier that you can pass to mysql_fetch_arr ay() and other
functions dealing with result tables."

etc.

--
Andy Hassall (andy@andyh.co. uk) icq(5747695) (http://www.andyh.co.uk)
Space: disk usage analysis tool (http://www.andyhsoftware.co.uk/space)

[T. Relyea]

Re: Counting rows in mysql

Matt Schroeder wrote:
[color=blue]
> Does anyone know how to count how many rows are in a mysql table? This is
> what I have, but it doesn't work right:
>
> <?
> $db = mysql_connect(" localhost", "username", "password") ;
> mysql_select_db ("database",$db );
> $sql = "SELECT COUNT(*) FROM table";
> $result = mysql_query($sq l);
> echo "$result";
> ?>
>
> This returns "Resource id #3". All it should say is '2'. Can anyone tell me
> what I'm doing wrong?
>
> Thanks,
> Matt[/color]

You want to use:

mysql_num_rows( $result);

http://www.php.net/manual/en/function.mysql-num-rows.php

Todd

[R. Rajesh Jeba Anbiah]

Re: Counting rows in mysql

"T. Relyea" <nospam@nospam. com> wrote in message news:<DeGRa.260 35$G%5.15711@tw ister.nyroc.rr. com>...[color=blue]
> Matt Schroeder wrote:
>[color=green]
> > Does anyone know how to count how many rows are in a mysql table? This is
> > what I have, but it doesn't work right:
> >
> > <?
> > $db = mysql_connect(" localhost", "username", "password") ;
> > mysql_select_db ("database",$db );
> > $sql = "SELECT COUNT(*) FROM table";
> > $result = mysql_query($sq l);
> > echo "$result";
> > ?>
> >
> > This returns "Resource id #3". All it should say is '2'. Can anyone tell me
> > what I'm doing wrong?
> >
> > Thanks,
> > Matt[/color]
>
> You want to use:
>
> mysql_num_rows( $result);[/color]


Use mysql_num_rows( $result) when your query is "SELECT * FROM table";

If your query is "SELECT COUNT(*)...", use

$sql = "SELECT COUNT(*) FROM table";
$result = mysql_query($sq l);
$num = mysql_result($r esult, 0);
echo $num;


---
"If there is a God, he must be a sadist!"
Email: rrjanbiah-at-Y!com

[enigma]

and what if i have WHERE value NOT NULL
at the end of the query?

i always get "not a valid MySQL result resource" for mysql_result