mysql query inner join

[showme48]

Hello,

 

I have a function to get a users rank in a contest and need some help

on sorting it by a different means.

 

Right now it take entries*referrals and then sorts based upon that

but I want to be able to sort multiple ways. Right now if the user

has no referrals they are not included in the sorting process and

I can't get my mind wrapped around how to include all of them.

 

For example it should work like this

 

user1   5 entries 2 referrals for a total of 10

user2   0 entries 0 referrals for a total of 0

user3   3 entries 1 referrals for a total of 3

user4   0 entries 0 referrals for a total of 0

user5   100 entries 0 referrals for a total of 100

 

In my mind

user5 should rank #1 as the total is 100

user1 should be 2nd as total is 10

user3 should be third as total is 3

user2 would be 4th as total is zero then (based upon id)

user4would be 5th as total is zero then (based upon id)

 

But what it does is this

user1 is ranked 1st

user3 is ranked 2nd

 

user2 and user4 and user5 are left out because they have no referrals

 

any help would be greatly appreciated.

 

here is the code

function get_user_rank($usrid,$contest_start,$type) {



$conteststart = date('Y-m-d', strtotime(date('Y-1-1')));



$res = mysql_query("SELECT m1.referid, m2.name, m2.lastname, m2.userid, m2.".$type." as entries, count(*) as referrals FROM members m1

inner join members m2 on m1.referid=m2.userid

where m2.verified=1 and m1.verified=1 and m1.joindate > '".$contest_start."'

group by m1.referid, m2.name ORDER BY entries DESC");

 

 while ( $r = mysql_fetch_assoc($res) ) {



    $mdata[$r['userid']]['userid'] = $r['userid'];

    $mdata[$r['userid']]['total_entries'] = $r['entries'] * $r['referrals'];



  }

  usort($mdata, 'sort_leaders');

  for ( $i=0; $i < count($mdata); $i++ )  {

    if ( $mdata[$i]['userid'] == $usrid ) return $i+1;

    next($mdata);

  }

  return 0;

}





function sort_leaders($a, $b) {

  if ( $a['total_entries'] == $b['total_entries'] ) return 0;

  return ( $a['total_entries'] > $b['total_entries'] ) ? -1 : 1;

}
 
$year_start = date('Y-m-d', strtotime(date('Y-1-1')));

get_user_rank($userid,$year_start,"contesty");

[Barand]

A INNER JOIN B

selects all records with a matching value in A and B

 

 

A LEFT JOIN B

selects all records from A joined to data from B where there is a match. (columns selected from B are null if there is no match)

 

Sounds like you need the LEFT JOIN