Problem with joins

[gurpreet94]

I have created a file with this basic structure in the referals table:

refREFER = the referer, person who sent the referal link out

refREFED = the refered person, the one who signed up with the link

refREFERIP = the referer's IP

refREFEDIP = the refered person's IP

 

I'm trying to create a code that will list any user that has signed up on the same IP with a referal link. Everything works except the Refered column, which I can't seem to figure out how to do. Basically I have made the refREFER = the userid in the users table, so it can be output as a username. However I want to do the same with the refREFED but don't know how. Here's my code so far:

 

<?php
include "globals.php";
if($ir['user_level'] != 2 && $ir['user_level'] != 3) //User level check
{
    die("You can't access this page");
}

$q=mysql_query("SELECT u.userid, u.username, u.laston, r.* 
			FROM users u 
			LEFT JOIN referals r	ON r.refREFER=u.userid 
			WHERE r.refREFERIP=r.refREFEDIP 
			ORDER BY userid ASC",$c); //Selecting everything that's needed

print "<center><br /><b><font color=white><h1>Referral Multis</h1></b><br />

<table width=75% border=1>
<tr style='background:black'>
<th><font color=grey>Referer</font></th>
<th><font color=grey>Refered</font></th>
<th><font color=grey>IP</font></th> 
<th><font color=grey>Time</font></th>
</tr>";
while(($r=mysql_fetch_array($q) or die(mysql_error()))) //Making all of the select stuff into $r
{
$reftime = date('F j, Y g:i:s a', $r['refTIME']);
if($r['laston'] >= time()-60*60) { $on="<font color=green><b>Online</b></font>"; } else { $on="<font color=red><b>Offline</b></font>"; }
print "
<tr> 
<td><a href='viewuser.php?u={$r['userid']}'><font color=brown><b>{$r['username']}</b></font></a> [{$r['refREFER']}]</td>
<td> {$r['username']} - {$r['refREFED']}</td>
<td>{$r['refREFERIP']}</td>
<td>$reftime</td>
</tr>";
}
print "</table>";

$h->endpage();

?>

 

At the moment under the Refered column it shows the wrong username, but the correct ID. Any ideas?

[btherl]

Is the issue that you want to look up 2 usernames with the one query?  You can join the users table twice like this:

 

SELECT u.userid, u.username, u.laston,
  u2.userid AS u2userid, u2.username AS u2username, u2.laston AS u2laston,
  r.*
FROM referals r
JOIN users u ON u.userid = r.refREFER
JOIN users u2 ON u2.userid = r.refREFED 
			WHERE r.refREFERIP=r.refREFEDIP 
			ORDER BY u.userid ASC

 

I've made it a JOIN because I don't understand why you used a LEFT JOIN there.  If you do have a good reason for doing so then you can add it back in.

[jcbones]

I'm going to take a stab at this, even though I am a little foggy on the database structure.

 

"SELECT 
       u.userid, u.username, u.laston, n.userid AS referredid, n.username AS referredusername, n.laston AS referredlaston, r.* 
FROM
       users AS u 
JOIN 
       referals AS r
JOIN
      users AS n
ON 
      r.refREFER=u.userid 
AND
     r.refREFED=n.userid
WHERE 
     r.refREFERIP=r.refREFEDIP 
ORDER BY 
   userid"

 

JOIN each and every row in the first table is joined to each and every row in the second table.

If there is no matching row for the right table in the ON or USING part in a LEFT JOIN, a row with all columns set to NULL is used for the right table.

[gurpreet94]

Neither of those worked. Here is my table structure:

 

Users: userid, username, lastip, and more

referals: refID (auto_increment), refREFER, refREFED, refTIME, refREFERIP, refREFEDIP, refCREDITED

 

I want it so that under the Referer column on the page it has the username of the refREFER, and under the column of Refered it has the username of the refREFED, as long as they are both the same IP - refREFEDIP

 

[btherl]

The "or die(mysql_error())" should go after mysql_query(), not after mysql_fetch_array().  What happened when you tried the queries?

[gurpreet94]

With both of the above queries, under the column Refered, the referer's username came up, but the refered ID is correct. Also, the list is limited to 1, even though I have set up 3 entries with the same IP's. Here's an example:

 

 

refID  refREFER refREFED                 refTIME refREFERIP refREFEDIP refCREDITED

37         2 7 1193519079 127.0.0.1 127.0.0.1          0

 

This is displayed as:

 

Referer Refered IP         Time

Test [2] Test - 7 127.0.0.1 October 27, 2007 9:04:39 pm

 

However, ID 7 belongs to Klikoka

[btherl]

If you are using my query, replace $r['username'] with $r['u2username'] in both places it appears.  That's because I used "AS u2username" to name the column fetched from joining with the users table the second time to get the refREFED username.

[gurpreet94]

Oh right, I didn't spot you done that. That works perfectly now, however it is still only loading up 1 record. There are a few in my database with the same IP, but it's only showing the first one. If I change the order from ASC to DESC it will show the same one ando nly one. I have tried limit but that doesn't work either

 

If I run this:

 

$q2=mysql_num_rows($q);

echo "$q2";

 

It prints out the number 1.

[btherl]

Do all the entires with the same IP have matching records for both refREFER and refREFED in the users table?  By default, a join will ignore anything which doesn't match in all tables.

 

If that is the case, use a LEFT JOIN for both joins with the users table.  You won't get a username though because there isn't one, you'll just get a user id.

[gurpreet94]

Sorry for the confusion, it seems when I edited the IP's I accidentally put a space after the IP. D'oh! Thanks for all your help!

 

Solved

[gurpreet94]

I've been trying to add a bit more to this script, to make it so that if someone has referred more than 1 person, their name will come up in a table and a number showing how many referrals they have. I can't get the count function working properly as I've never really used it. Any help would be great

 

<?php
include "globals.php";
if($ir['user_level'] != 2 && $ir['user_level'] != 3) //User level check
{
    die("You can't access this page");
}

$q=mysql_query("SELECT u.userid, u.username, u.laston,
  u2.userid AS u2userid, u2.username AS u2username, u2.laston AS u2laston,
  r.*
FROM referals r
JOIN users u ON u.userid = r.refREFER
JOIN users u2 ON u2.userid = r.refREFED 
			WHERE r.refREFERIP=r.refREFEDIP 
			ORDER BY u.userid ASC"); //Selecting everything that's needed

print "<center><br /><b><font color=white><h1>Referral Multis</h1></b><br />

<table width=75% border=1>
<tr style='background:black'>
<th><font color=grey>Referer</font></th>
<th><font color=grey>Refered</font></th>
<th><font color=grey>IP</font></th> 
<th><font color=grey>Time</font></th>
</tr>";
while($r=mysql_fetch_array($q)) //Making all of the select stuff into $r
{
$reftime = date('F j, Y g:i:s a', $r['refTIME']);
if($r['laston'] >= time()-60*60) { $on="<font color=green><b>Online</b></font>"; } else { $on="<font color=red><b>Offline</b></font>"; }
print "
<tr> 
<td><a href='viewuser.php?u={$r['userid']}'><font color=brown><b>{$r['username']}</b></font></a> [{$r['refREFER']}]</td>
<td><a href='viewuser.php?u={$r['u2userid']}'><font color=brown><b>{$r['u2username']}</b></font></a> [{$r['refREFED']}]</td>
<td>{$r['refREFERIP']}</td>
<td>$reftime</td>
</tr>";
}
print "</table>";



$q2=mysql_query("SELECT u.userid, u.username, r.refREFERIP, r.refREFER, COUNT(*)
FROM referals r
JOIN users u ON u.userid = r.refREFER
			GROUP BY r.refREFER
			ORDER BY u.userid ASC") or die(mysql_error()); //Selecting everything that's needed for the 2nd part

print "<center><br /><b><font color=white><h1>Multiple referals (check these)</h1></b><br />

<table width=75% border=1>
<tr style='background:black'>
<th><font color=grey>Refered</font></th>
<th><font color=grey>IP</font></th> 
<th><font color=grey>No. of referrals</font></th> 
<th><font color=grey>Time</font></th>
</tr>";

while($r2=mysql_fetch_array($q2)) //Making all of the select stuff into $r
{
$reftime = date('F j, Y g:i:s a', $r['refTIME']);


$num_rows = mysql_num_rows($q2); 


if($r['laston'] >= time()-60*60) { $on="<font color=green><b>Online</b></font>"; } else { $on="<font color=red><b>Offline</b></font>"; }
print "
<tr> 
<td><a href='viewuser.php?u={$r2['userid']}'><font color=brown><b>{$r2['username']}</b></font></a> [{$r2['userid']}]</td>
<td>{$r2['refREFERIP']}</td>
<td>count({$r2['refREFER']})</td>
<td>$reftime</td>
</tr>";
}
print "</table>";

echo "$num_rows";
$h->endpage();

?>

 

The problem parts:

$q2=mysql_query("SELECT u.userid, u.username, r.refREFERIP, r.refREFER, COUNT(*)


<td>count({$r2['refREFER']})</td>

The echo "$num_rows" shows the number 11 at the bottom, but that's how many users have more than 1 referral. Would anyone like to provide any help so the number of referrals column could show how many referrals that user has? Thanks in advance

[btherl]

The count will be named "count" by default, and can be accessed as $r2['count'].  But you can also rename it, eg:

 

$q2=mysql_query("SELECT u.userid, u.username, r.refREFERIP, r.refREFER, COUNT(*) AS referred_count

...

<td>{$r2['referred_count']}</td>

 

There's no need to use php's count() as MySQL has already counted the rows for you.

[gurpreet94]

Works perfectly, thanks once again.

Could you explain how it works because I don't understand it? The count(*) hasn't been pointed to the refREFER? Or does the count(*) count whatever the GROUP BY is?

[btherl]

The count(*) is based on the group by.  So if you group by r.refREFER, count(*) will be the number of result for each r.refREFER.

 

If the question is "How results are there for each X", then the solution is "SELECT X, count(*) FROM table GROUP BY X"

 

If the question is "What is the total of Y for each X", then the solution is "SELECT X, sum(Y) FROM table GROUP BY X"

[gurpreet94]

Thanks for that great explanation