MYSQL num rows wrong!

Counting each row individually, need a fix.

[GamingLoft]

K, ive been trying to fix this problem for a while.
I have a file setup to count the amount of rows in my one database where post_id='$VAR' but its been counting every row individually, heres what i mean... i have a echo, but it echos...

"111"

Its doing each row individually. EXAMPLE

1 row = 1
2 rows = 11
3 rows = 111
4 rows = 1111
and so on... what am i doing wrong,

____________________
here is my code:

$fidcheck = mysql_query("SELECT * FROM phpbb_posts
WHERE forum_id='2'");

while($row = mysql_fetch_array($fidcheck))
{
$pid = $row['post_id'];
$posterid = $row['poster_id'];
$topicid = $row['topic_id'];

$first = mysql_query("SELECT * FROM phpbb_topics
WHERE topic_first_post_id=$pid");

$vidnum = mysql_num_rows($first);

while($row = mysql_fetch_array($first))
{
$pid = $row['topic_first_post_id'];
$posterid = $row['topic_poster'];
$topicid = $row['topic_id'];

$getname = mysql_query("SELECT * FROM phpbb_users
WHERE user_id='$posterid'");

echo $vidnum;
}
}
?>

[PHP_Chimp]

So I am guessing your '111' is coming from your echo $vidnum?

Seeing as $vidnum comes from -

$first = mysql_query("SELECT * FROM phpbb_topics WHERE topic_first_post_id=$pid");
$vidnum = mysql_num_rows($first);

$first will be returning the resource result
$vidnum will be returning a interger corresponding to the number of rows in $first's result set.

So your $vidnum of '111' is telling me that there are 111 rows in your php_topics table where topic_first_post_id=$pid.
Is that not what you wanted to do?

<edit>
Oops missed the while loop.
See the much better suggestion below :)

[edited by: PHP_Chimp at 10:39 am (utc) on Nov. 29, 2007]

[omoutop]

actually his '111' is in fact 3 repeats of '1'.
in the echo i see no line break, br or something.
it just echos a number next to a number

try this and see if it works

$fidcheck = mysql_query("SELECT pid FROM phpbb_posts WHERE forum_id='2'");
while($row = mysql_fetch_array($fidcheck))
{
$pid = $row['post_id'];

$first = mysql_query("SELECT * FROM phpbb_topics WHERE topic_first_post_id='$pid' ");
$vidnum = mysql_num_rows($first);

echo "for pid=".$pid." we have ".$vidnum." records<br>";
}

[GamingLoft]

k i did what you said here is my new code, but it is still repeating the 1's (echoing 111)

my code is..

$fidcheck = mysql_query("SELECT post_id FROM phpbb_posts WHERE forum_id='2'");
while($row = mysql_fetch_array($fidcheck))
{
$pid = $row['post_id'];

$first = mysql_query("SELECT * FROM phpbb_topics WHERE topic_first_post_id='$pid' ");
$vidnum = mysql_num_rows($first);

echo $vidnum;
}
?>

[PHP_Chimp]

Try -
echo $vidnum."<br />\n";
As this should echo -
1
1
1

Or if you want a total number then you could do something like -

$totvidnum = 0;
$totvidnum += $vidnum;
} // end of while loop
echo $totvidnum."<br />\n";

As this should add each of your $vidnum calls together to give you a total.

[GamingLoft]

Ok, i did what you said [PHP_Chimp] but now it echos 1, meanwhile it should echo the number 3...

here is my code

$fidcheck = mysql_query("SELECT post_id FROM phpbb_posts WHERE forum_id='2'");
while($row = mysql_fetch_array($fidcheck))
{
$pid = $row['post_id'];

$first = mysql_query("SELECT * FROM phpbb_topics WHERE topic_first_post_id='$pid' ");
$vidnum = mysql_num_rows($first);

$totvidnum = 0;
$totvidnum += $vidnum;
} // end of while loop
echo $totvidnum."<br />\n";
?>

[PHP_Chimp]

Sorry as I left the $totalvid inside the while loop it will get reset to 0 before adding 1 to it...hence ending up with 1 each time.
So it should look like -

// initialise $totalvidnum [b]before[/b] the while loop
$totalvidnum = 0;
while($row = mysql_fetch_array($fidcheck))
{
$pid = $row['post_id'];
$first = mysql_query("SELECT * FROM phpbb_topics WHERE topic_first_post_id='$pid' ");
$vidnum = mysql_num_rows($first);
// REMOVE $totvidnum = 0;
$totvidnum += $vidnum;
}
echo $totvidnum."<br />\n";
?> 

[GamingLoft]

K it finally works, thanks a bunch, i've had so much trouble with this.

MUCH APPRECIATED!