compare data from multiple tables?

[ffxpwns]

This is going to be complicated to explain, so please bear with me.  I have a tables called 'users'  within that table, I have columns 'reg_date' (when the user registered) and 'email'.  Then, I have a table called 'refer'.  Within that, I have 'referrer' (who sent out a referral) and 'email' (who the referral email was sent to).  When displaying this information, I want to display the day that someone registered (reg_date) when the email from 'users' equals the email from 'refer'.  And it only displays this information to the 'referrer'.  If that needs clarification, let me know.  Here is my code so far: (The first query works totally fine. it's the second one)

 

$userName = $user->user_data['nick'];



// Get details
$query = "SELECT email, date FROM " . $DBPrefix . "refer WHERE referrer  =  '".$userName."'";
$res = mysql_query($query);
$system->check_mysql($res, $query, __LINE__, __FILE__);
while($row = mysql_fetch_assoc($res))
{
$template->assign_block_vars('refer, array(
		'EMAIL' => $row['email'],
		'DATE' => date('d/m/Y', $row['date'])

		));
}

$query = "SELECT reg_date FROM " . $DBPrefix . "users WHERE email  =  'email FROM " .$DBPrefix. " refer AND referrer = '".$userName."'";
$res = mysql_query($query);
$system->check_mysql($res, $query, __LINE__, __FILE__);
while($row = mysql_fetch_assoc($res))
{
$template->assign_block_vars('refer', array(
		'REFEMAIL' => $row['email'],


		));
}

[Psycho]

Well, you are doing it wrong. You need to learn how to do JOINS in your queries. that is the whole point of having a relational database. But, before I give you a possible solution I do have one comment. Your current structure relies upon the fact that the person registering is using the same email as the one in which they received the referral. I use many different email addresses for different purposes. Just because someone sends me a referral on one email address doesn't mean I will want to resister on that email. A better approach, IMHO, is to have a referral code sent to the user. Then you could JOIN on that code. Also, depending on the collation you are using for your email fields you may or may not be able to JOIN the email fields.

 

Also, why are you using the use's username in the referrer table instead of an ID? I think you shoudl take some time and do some reading on database schemas.

 

This query should show you all the information relevant to the referrals made by a particular user.

SELECT u.reg_date, u.username, u.email
FROM users AS u
JOIN refer AS r
WHERE r.referrer = '$userName'

[ffxpwns]

Well, you are doing it wrong. You need to learn how to do JOINS in your queries. that is the whole point of having a relational database. But, before I give you a possible solution I do have one comment. Your current structure relies upon the fact that the person registering is using the same email as the one in which they received the referral. I use many different email addresses for different purposes. Just because someone sends me a referral on one email address doesn't mean I will want to resister on that email. A better approach, IMHO, is to have a referral code sent to the user. Then you could JOIN on that code. Also, depending on the collation you are using for your email fields you may or may not be able to JOIN the email fields.

 

Also, why are you using the use's username in the referrer table instead of an ID? I think you shoudl take some time and do some reading on database schemas.

 

This query should show you all the information relevant to the referrals made by a particular user.

SELECT u.reg_date, u.username, u.email
FROM users AS u
JOIN refer AS r
WHERE r.referrer = '$userName'

 

Alrighty.  I modified the code to use ID's, but I may work on the code thing later.  But how would I put these results on the TPL file?  Currently it's

$query = "SELECT u.reg_date, u.username, u.email
FROM". $DBPrefix. "users AS u
JOIN". $DBPrefix ."refer AS r
WHERE r.referrer = '$userName'";

$res = mysql_query($query);
$system->check_mysql($res, $query, __LINE__, __FILE__);
while($row = mysql_fetch_assoc($res))
{
$template->assign_block_vars('fbs', array(
		'REGDATE' => $row['reg_date'],


		));
}

 

But it doesn't work, this error comes up:

Database access error. SELECT u.reg_date, u.nick, u.email
FROM Auc_users AS u
JOIN Auc_refer AS r
WHERE r.referrer = 'testuser'
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 'AS u
JOIN Auc_ refer AS r
WHERE r.referrer = 'testuser'' at line 2
page:/.../reflist.php line:42

 

Also, how would I display the data?  Before I used

$template->assign_block_vars('refer', array(
		'REFEMAIL' => $row['email'],

  But I assume that would have to change

[ffxpwns]

Oh, and because I'm lazy, $userName = $user->user_data['id'];  so userName is ID.

[Psycho]

Well, something doesn't make sense. The query look s fine, but according to the error message there is an errant space between "Auc_" and "refer". Are you sure you built the query correctly?

[ffxpwns]

Well, something doesn't make sense. The query look s fine, but according to the error message there is an errant space between "Auc_" and "refer". Are you sure you built the query correctly?

 

Well, I feel like a right proper dumbass.  one weird thing though,  I only have one row in 'refer' and the email has an account attached to it, but it shows up like this 

[Psycho]

The output doesn't look right even if you had multiple records returned (the first record has an invite date but no reg date?). You should always test and debug your queries in MySQL - not in the PHP code. You may have a bug in the code that produces the output.

[ffxpwns]

The output doesn't look right even if you had multiple records returned (the first record has an invite date but no reg date?). You should always test and debug your queries in MySQL - not in the PHP code. You may have a bug in the code that produces the output.

 

Oh, the dates are every other users registration dates, but it leaves the one we wanted empty.  Any ideas?  Agin, my code is

$query = "SELECT u.reg_date, u.nick, u.email
FROM ". $DBPrefix."users AS u
JOIN ". $DBPrefix."refer AS r
WHERE r.referrer = '$userName'";

$res = mysql_query($query);
$system->check_mysql($res, $query, __LINE__, __FILE__);
while($row = mysql_fetch_assoc($res))
{
$template->assign_block_vars('refer', array(
		'REGDATE => $row['reg_date']


		));
}

[Psycho]

Ah, I left off the JOIN condition. Try this

$query = "SELECT u.reg_date, u.nick, u.email
          FROM ". $DBPrefix."users AS u
          JOIN ". $DBPrefix."refer AS r ON u.email = r.email
          WHERE r.referrer = '$userName'";

[ffxpwns]

Ah, I left off the JOIN condition. Try this

$query = "SELECT u.reg_date, u.nick, u.email
          FROM ". $DBPrefix."users AS u
          JOIN ". $DBPrefix."refer AS r ON u.email = r.email
          WHERE r.referrer = '$userName'";

 

Closer, here is the table it creates.  That date registered value is someone else in the refer table with absolutely no relation to the user we're trying to to show the details for. 

[Psycho]

It is very difficult to try and debug database issues such as this without first hand understanding of the data. Please show the complete record from the users table and the associated record from the refer table that should be associated with one another. Did you test the query in PHPMyAdmin or some other DB utility before trying to run it and output the results in PHP?

[ffxpwns]

IGNORE THIS, I'M AN IDIOT

[Psycho]

IGNORE THIS, I'M AN IDIOT

 

It was an error in the code that was generating the output, wasn't it?

[ffxpwns]

IGNORE THIS, I'M AN IDIOT

 

It was an error in the code that was generating the output, wasn't it?

 

No, I thought I had a solution, and I didn't, so I edited my post.  But I'll try adding this :and r.referrer = u.nick after the where clause, see what happens.

[ffxpwns]

IGNORE THIS, I'M AN IDIOT

 

It was an error in the code that was generating the output, wasn't it?

 

No, I thought I had a solution, and I didn't, so I edited my post.  But I'll try adding this :and r.referrer = u.nick after the where clause, see what happens.

 

I think it's working, I think adding and r.email = u.email fixed it, but I'll do some testing.