By vosko on

Hi there

I'm trying to count the number of buddies by user

Have tried this snippet but I'm getting an error database (Column 'uid' in field list is ambiguous query...)

<?php
$buddycount =   db_query('SELECT COUNT(uid) AS number FROM {buddylist}  u JOIN  {users} ur ON u.uid=ur.uid  WHERE ur.status=1 AND u.received=1');     
echo "Buddies: $buddycount <br>";
?>

Any solution?

Thanks in advance

⋅ Categories: Drupal 5.x

Comments

tom_o_t’s picture

tom_o_t commented

My sql knowledge is a little patchy, but have you tried changing the count from COUNT(uid) to COUNT(u.uid) ?

Cheers,

Thomas

(Coding bugs on a friday afternoon - not fun)

__________________________
http://www.thomasturnbull.com
http://twitter.com/thomasturnbull

vosko’s picture

vosko commented

Thanks for quick response

But the result is Resource id #245 in all my users.
Obviously I don't have 245 buddies by user, may be some day... ;)

And I have tried to put (ur.uid) instead of (uid) or (u.uid) but displays the same wrong data

Best

coreyp_1’s picture

coreyp_1 commented

SELECT COUNT(ru.uid) AS number FROM {buddylist} u JOIN {users} ur ON u.uid=ur.uid GROUP BY ru.uid WHERE ur.status=1 AND u.received=1 AND u.uid=1

notice the change of uid to ru.uid, adding the GROUP BY clause, and adding a final u.uid=???? at the end (to limit the results to one user).

I didn't try it, b/c I don't use this module, but it should work.

- Corey

vosko’s picture

vosko commented

Not solve the problem

I'm getting again a mysql error:

user warning: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'WHERE ur.status=1 AND u.received=1 AND u.uid=1' at line 1 query: SELECT COUNT(ru.uid) AS number FROM buddylist u JOIN users ur ON u.uid=ur.uid GROUP BY ru.uid WHERE ur.status=1 AND u.received=1 AND u.uid=1

Thanks for help

coreyp_1’s picture

coreyp_1 commented

Try changing the order. I'll look again when I get back in town tonight.

SELECT COUNT(ru.uid) AS number FROM {buddylist} u JOIN {users} ur ON u.uid=ur.uid WHERE ur.status=1 AND u.received=1 AND u.uid=1 GROUP BY ru.uid

- Corey

vosko’s picture

vosko commented

Again mysql error

user warning: Unknown table 'ru' in field list query: SELECT COUNT(ru.uid) AS number FROM buddylist u JOIN users ur ON u.uid=ur.uid WHERE ur.status=1 AND u.received=1 AND u.uid=1 GROUP BY ru.uid

coreyp_1’s picture

coreyp_1 commented

Sometimes I wonder why I even try...

It's ur, not ru! I should've caught that. :-\

I made a few other changes as well.

SELECT COUNT(u.uid) AS number FROM users ur LEFT JOIN buddylist u ON u.uid=ur.uid WHERE ur.status=1 AND u.received=1 AND u.uid=1 GROUP BY ur.uid

- Corey

vosko’s picture

vosko commented

Hi Corey

First of all thanks for your help

But again I'm geetting this weird data: Resource id #245.

Best

coreyp_1’s picture

coreyp_1 commented

I think now the error is in your PHP code. Try this:


$resource = db_query('SELECT COUNT(u.uid) AS number FROM users ur LEFT JOIN buddylist u ON u.uid=ur.uid WHERE ur.status=1 AND u.received=1 AND u.uid=1 GROUP BY ur.uid');

$result = db_fetch_array($resource);

echo "You have ".$result['number']." buddies.  Don't you feel special?";

The SQL had to be changed, too, but if I also overlooked that PHP error, then I think I'll go shoot myself (or at least get more sleep!!!). :)

- Corey

vosko’s picture

vosko commented

Hi Corey

Trying your last code I'm getting a blank result, but if I delete u.uid=1 always list 1 buddy for all the users. And at least 2 users have 2 buddies

We are near...

Best

vosko’s picture

vosko commented

Hi there

Finally I have got it! I complicated too using INNER JOIN, LEFT JOIN and others variables....

For user-profile page you can use this snippet:

<?php
$uid = arg(1);
$buddies = db_fetch_object(db_query("SELECT COUNT(buddy) AS count FROM {buddylist} WHERE uid= %d AND received=0", $uid));
echo "You have $buddies->count buddies";
?>

For front-page you can use this other snippet, combining with additional info as username and userpicture:

<?php
 $count = 5;
global $user;
$output = '<div>';
$result = db_query_range("SELECT *,COUNT(ur.buddy) AS count FROM {users} as u, {buddylist} as ur where u.status=1 AND u.uid=ur.uid AND u.picture <> '' GROUP BY u.created DESC",0,$count); 
while ($user_info = db_fetch_object($result)) {
$output .= '<div style="float: left; color: #efefef"><a href="/profile/'.$user_info->name.'"><img src="'.$user_info->picture.'"  hspace=0  height="70" ></div><div style="padding-left: 90px"><b>'.$user_info->name.'</b></div></a><div style="padding-left: 90px">'.$user_info->count.' buddies</div><div style="padding-left: 90px">&nbsp;&nbsp;</div><br><br>';
}
  $output .= '</div>';
  print $output;
?>

Best