MySQL Num Rows Inside a Loop

[BlueCharge]

Hi Everyone,

I am currently trying to list a leaderboard for refferals. The table is set up with the following columns:

id | refferer | user

So obviously if someone reffers someone else they will be in a new row so there are multiple rows per person.

I am trying to use the following code which echos the user the loop is one but wont echo the amount of times they are in the table

<?php
include("includes/db_connect.php");
$select = mysql_query("SELECT refferer FROM refferals");
while($fetch = mysql_fetch_array($select)) {
    
	$current = $fetch['refferer'];
    $selectcount = ("SELECT * FROM refferals WHERE refferer = '$current'");
    $fetchall = mysql_num_rows($selectcount);
    echo $current;
	echo " | ";
	echo $fetchall;
	
}
?>

Thanks in advance!

[hielo]

On line 7, you forgot to execute the query. You need to use mysql_query():

...
 $selectcount = mysql_query("SELECT * FROM refferals WHERE refferer = '$current'") or die( mysql_error() );
...

[hielo]

try:

<?php
include("includes/db_connect.php");
$result = mysql_query("SELECT `refferer`, COUNT(refferer) as `total` FROM refferals GROUP BY `refferer`") or die(sprintf('Line: %s <br>$s',__LINE__.mysql_error()));
while($row = mysql_fetch_assoc($result)) {
    echo sprintf('%s | %s', $row['refferer'], $row['total'] );
}
?>

$query = ("SELECT * FROM refferals WHERE refferer = '$current'";
$result = mysql_query($query) or die ("Error in query: $query. ".mysql_error());
$fetchall = mysql_num_rows($result);

[BlueCharge]

Thank you all for your replies, particularly hielo. It's now counting and working and the list is below, but i dont want to list the same user more than once... PLease help:

opticl3gendz | 1
Mase93 | 1
JVLattyxD | 2
JVLaTTyxD | 2
JVLatty xD | 3
HandLegements | 1
dazzarfc12 | 5
dazzarfc12 | 5
dazzarfc12 | 5
dazzarfc12 | 5
JVLaTTy | 1
jtagtagger | 1
JVLaTTy xD | 3
dazzarfc12 | 5
Smoggie | 10
Smoggie | 10
Smoggie | 10
Smoggie | 10
zSheehan | 2
Smoggie | 10
zSheehan | 2
Smoggie | 10
jordan30001 | 3
Smoggie | 10
Smoggie | 10
Smoggie | 10
TheKvBeast | 2
Smoggie | 10
Smoggie | 10
Smoggie | 10
Smoggie | 10
jordan30001 | 3
Smoggie | 10
Smoggie | 10
Smoggie | 10
Smoggie | 10
Smoggie | 10
Smoggie | 10
Smoggie | 10
thekvJosh | 9
Smoggie | 10
Smoggie | 10
Smoggie | 10
Smoggie | 10
Smoggie | 10
Smoggie | 10
Smoggie | 10
Smoggie | 10
Smoggie | 10
thekvjosh | 9
thekvjosh | 9
thekvjosh | 9
Smoggie | 10
Smoggie | 10
thekvjosh | 9
Smoggie | 10
Smoggie | 10
Smoggie | 10
Smoggie | 10
Smoggie | 10
Smoggie | 10
Smoggie | 10
Smoggie | 10
glock2shot | 1
Smoggie | 10
Smoggie | 10
Smoggie | 10
Smoggie | 10
Smoggie | 10
Smoggie | 10
Smoggie | 10
TheKvBeast | 2
SmithyxD | 2
NyZmEx | 9
NyZmEx | 9
NyZmEx | 9
Smoggie | 10
NyZmEx | 9
xX44 | 1
hass | 10
hass | 10
hass | 10
hass | 10
hass | 10
hass | 10
hass | 10
hass | 10
hass | 10
hass | 10
hass | 10
hass | 10
hass | 10
hass | 10
hass | 10
hass | 10
hass | 10
NyZmEx | 9
QuantumMods | 1
UKz I Waring | 3
UKz I Waring | 3
Smoggie | 10
UKz I Baisley | 3
UKz I Baisley | 3
NyZmEx | 9
UKz I Baisley | 3
iTzZ GaZzA | 1
II20dLuXee | 1
hass | 10
NyZmEx | 9
hass | 10
UKz I Waring | 3
NyZmEx | 9
v abuzar | 1
nyzmex | 9
Revokeyy | 1
no 07761416106 | 1
KnG | 1
thekvJosh | 9
thekvJosh | 9
thekvJosh | 9
thekvJosh | 9
JVLaTTy xD | 3
DansLobby | 3
jordan30001 | 3
iPro v SNiiP3z | 1
SmithyxD | 2
DansLobby | 3
DansLobby | 3
II | 1
II dLuXee | 1
II eMuzR | 2
iProvSNiiP3z | 1
II eMuzR | 2
iidLuXee | 1
iRuuSH | 1

as you can see smoggie has a lot and i only want the user name to come out once so i get a nice top 10 board... Thanks again guys!

Use distinct keyword to filter a single record as like below sample query

SELECT  distinct PRODUCT_LinE  FROM mytable  ORDER BY PRODUCT_LinE ;

[hielo]

It's now counting and working and the list is below

Based on that result, I suspect you opted to apply the fix on my first post. Try my second post. It's a complete re-write of your code. It should do what you are after.

[boorsekala]

use select COUNT(...)AS referals_num from ......
is better than num_rows
and use one sql command instead of tow same discint or
select count()AS ..... GROUP BY

if your page is loaded more in one day try to cach result to another table and run
file cacher every 5min by cronjob in cpanel or webmin or whm(if dedicate)
php -q /fixtable.php
or php -q /home/daniweb/public_html/fixtable.php

in the nex usage just use simple select sql
running more sql having discint and orderby rand and count and group by and left join command in one day use cpu and ram more than needed.
andwhen your site grow up to morevisitor for this script loaded time is grow to 4 5 or 10 second.
every day use repair tools from phpmyadmin for each table or try cron job to repair in some times in day. to speed up mysql.