adding up a column by username

[testing7]

basically i have a referral column that saves the username of the referral now what i would like to do is add up that persons referral count. so say their name comes up 10 times in the column i would be able to output a 10 so they can see how many referrals they have gotten.

 

not even sure this is the right way to start but this is what i came up with (let me know if it looks right or completely wrong because i suck)

 

<?php
$total="SELECT SUM(user) AS user_total FROM referals WHERE user = user"; // user = user cant be right can it?
$result=mysql_query($total);
print "<tr>";
while($row = mysql_fetch_array($result))
{
	//Here is where im confused
               $sum = $row['user_total'];
	print "<td>Sum: $sum </td>";
}
?>

 

am i way off? or can it even be done?

[john-formby]

Hi testing7,

 

Do you want to display the count for an individual user (i.e. you are passing a value to the database for one user) or do you want to return the count for all users in the database?

 

For the first option, you can use something like this:

<?php
$user = 'John';
$total= mysql_query("SELECT user, COUNT(user) AS user_total FROM referrals WHERE user = '$user'");
echo '<tr>';
while($row = mysql_fetch_array($total))	{		               		
    echo '<td>User: '.$row['user'].', Count: '.$row['user_total'].'</td>';	
}
?>

 

For the second option you can use something like this:

<?php
$total= mysql_query("SELECT user, COUNT(user) AS user_total FROM referrals GROUP BY user");
echo '<tr>';
while($row = mysql_fetch_array($total))	{		               		
    echo '<td>User: '.$row['user'].', Count: '.$row['user_total'].' / </td>';	
}
?>

 

Hope this helps,

 

John

[testing7]

hmm im getting an error with that code:

Warning: mysql_fetch_array() expects parameter 1 to be resource, boolean given in /home/public_html/board21.php on line 52

[Nodral]

Can you post all code around the given line so we can see where the error may be

[testing7]

i posted this code:

 

<?php
$total= mysql_query("SELECT user, COUNT(user) AS user_total FROM referrals GROUP BY user");
echo '<tr>';
while($row = mysql_fetch_array($total))	{		               		
    echo '<td>User: '.$row['user'].', Count: '.$row['user_total'].' / </td>';	
}
?>

 

getting the error on this line:

 

echo '<tr>';

[Nodral]

Your error indicates that $total is not a valid SQL result.

 

Check your column titles etc and ensure you are actually getting some information coming back out of the query

[john-formby]

Hi testing7,

 

Sorry, it is the table name I mis-spelled in my example.  Your table is: referals and in my example I wrote referrals (extra R).  If you change this, it should be ok.

 

John