Please help me to make this query

_____

unfortuently that I am new with mysql 🙁
I have excally like this forum and I want to add this feature

I am turning on referral. So, any new member can insert the name of the existing member that he/she told him/her about the forum.

I will show on the main page the top 5 persons that lead others to the forum

only the table USER has all the staff like

userid | username | referrerid
1 admin 0
2 june 1
3 tom 1
4 bush 3
5 ^_ 0

so how to show that

admin (told 2 member(s))
tom (told 1 member(s))

I tried to use this

SELECT count(*) AS count,referrerid,username FROM user where referrerid > 0 group by referrerid order by count desc, referrerid asc

then echo
username and count
However username comes wrong
any hint 🙁

Danicek

Select 
A.UserId, B.UserName, Count(B.ReffereId) 
From User A, User B
Where A.UserId = B.ReffereId

This should work, however I do not know if this is exact syntax for MySQL (I am currently working with Sybase ASE).

_____

Thanks a lot Danicek for the try 🙁

I think in mysql
you can write
select TableName.userid, TableName.username
where userid and username are columns in TableName

but there is only 1 table (user).
may be there is no need for a and b in mysql.
They cause error of unknown tables

I try to make some change but gave up because the where sentense

Where user.UserId = user.ReffereId

because userid is the id of the old member
refereid is the id of the new member
and looks strange to equalize them
looks like that

I see that like a hard query for any one

any help friends :rolleyes:

tsinka

Hi,

just to understand:

can a user insert more than one name (if e.g. the user told 5 persons about the forum) ?

If this is the case I would suggest the following change:

User table:

USER:

userid------------username
1------------------admin
2------------------june
3------------------tom
4------------------bush
5------------------^_
6------------------zing
7------------------zong
8------------------zung

Then you could have a second table:

REFERRAL:

olduserid--------newuserid
1------------------5
1------------------4
1------------------3
5------------------2
3------------------6
3------------------7
3------------------8

(with an unique index using both fields olduserid and newuserid)

If you want to get the top 5 the query could look like:

  SELECT count(u.userid) as cnt,u.userid,u.username 
    FROM USERS u,REFERRAL r
   WHERE u.userid=r.olduserid 
GROUP BY u.userid ODER BY cnt DESC,u.userid DESC LIMIT 5

Use LIMIT 5 if you want to limit the numbers of rows in the result set.

The tricky part would be to make sure that cross references don't exist like user 1 inserted user 2 and user 2 inserted user 1 which might not be what you want.

I hope that the query is correct...

Thomas

ultraslacker

Originally posted by Danicek
Select 
A.UserId, B.UserName, Count(B.ReffereId) 
From User A, User B
Where A.UserId = B.ReffereId
This should work, however I do not know if this is exact syntax for MySQL (I am currently working with Sybase ASE). [/B]

That almost has it.

SELECT u.userid, u.username, count(r.referrerid) AS referrals
FROM user AS u
JOIN user AS r
ON (u.userid = r.referrerid)
GROUP BY u.userid, u.username
ORDER BY referrals DESC
LIMIT 5

This is a classic self join where a row refers to another row in the same table, thus two references to the table are required.

You may want to handle limit in the script as ties will not be considered in the sql.

Danicek

Originally posted by ultraslacker
That almost has it.

SELECT u.userid, u.username, count(r.referrerid) AS referrals
FROM user AS u
JOIN user AS r
ON (u.userid = r.referrerid)
GROUP BY u.userid, u.username
ORDER BY referrals DESC
LIMIT 5

This is a classic self join where a row refers to another row in the same table, thus two references to the table are required.

You may want to handle limit in the script as ties will not be considered in the sql.

Ok, thats it. It should work almost same as my script in Sybase. So from I see here, mySQL does not support syntax used in my script?

_____

Thanks a lot friends
u did it 😃

in fact i solved it yestarday but in a very BAD way because i was in hurry and I want to do it.
and I don't like to stop in a point.

So, I did 2 queries, 3 loops, arrays :rolleyes:
but I was happy because i did it at the end
overwritting my mysql weekness

look here how bad it was 😃

<marquee direction=right scrolldelay=100>
<?
require("admin/config.php");
$db=mysql_connect($servername,$dbusername,$dbpassword) or die("Can't open connection to MySQL");
mysql_select_db($dbname) or die("Can't select database");

// I will get the count and the old member id
$query = "SELECT count(*) AS count,referrerid FROM user where referrerid > 0 group by referrerid order by count desc, referrerid asc";// LIMIT 0,10
$result = mysql_query($query);

echo "Thanks for those members that told others about the forum: ";
$x=0;$where="where";
while ($line = mysql_fetch_array($result)) {
	$userid[$x]=$line[referrerid];
	$count[$x]=$line[count];
	if ($x) $where .= " or";
	$where .= " userid = " . $userid[$x];
	$x++;  

}

// i will now try to find the username for each userid (old member)
$query = "SELECT userid,username from user $where";
$result = mysql_query($query) or die(mysql_error());

while ($line = mysql_fetch_array($result)) {
	$id=$line[userid];
	$username[$id]=$line[username];
}

//echo $userid[0];echo" $x";echo " " . $username[60];exit;
// now echo the line
for ($y=0;$y<=$x;$x--) {
	if($count[$y]==1) $word ="1 member";
	else $word = $count[$y] . " members";
	echo $username[$userid[$y]] . "( told $word) &nbsp&nbsp&nbsp&nbsp::&nbsp&nbsp&nbsp&nbsp";
	$y++;
}

?>
</marquee>

bad isn't it? :rolleyes:

all that rubbish has been done by the query that you wrote

SELECT u.userid, u.username, count(r.referrerid) AS referrals
FROM user AS u 
JOIN user AS r
ON (u.userid = r.referrerid)
GROUP BY u.userid, u.username
ORDER BY referrals DESC
LIMIT 5

thanks a lot friends 🙂

now i will change it to looks smarter 😃

_____

Originally posted by Danicek
Ok, thats it. It should work almost same as my script in Sybase. So from I see here, mySQL does not support syntax used in my script?

Danicek, you still have the power of knowing database and the syntax of mysql can be learn easily by u

not like me
till now i c it hard to know the JOIN story with mysql :rolleyes:

I tried to change your query to make it works mysql, but
there was an error given that with
count()
u must use GROUP BY clause :rolleyes:

Thanks a lot Danicek 🙂

ultraslacker, ur query works fine
can u advice me on a site or a book that I can use to learn more
about mysql especially the JOIN and other hard staff for me

and thanks for u, u make the script looks smarter and simple 🙂

tsinka thanks
I even planed to modify the database
but i make it the last plan
as the forum script come with a lot of php files
and there is upgrade happens from a time to time
and keeping the modification in mind later will be taugh

thanks again

Danicek

Originally posted by ^___
I tried to change your query to make it works mysql, but
there was an error given that with
count()
u must use GROUP BY clause :rolleyes:

Thanks a lot Danicek 🙂

Of course you have to use Group By. There was mistake in my query.
Correct query should be:

Select 
A.UserId, B.UserName, Count(B.ReffereId) 
From User A, User B
Where A.UserId = B.ReffereId
Group By A.UserId

I forgot to add group by. Every agregate function must use group by to be able to produce more than one outputline.

_____

hi Danicek
I liked to change and play
may be i will learn by that 🙂

I tried to modify urs and to use this

Select 
A.userid, B.username, count(B.referrerid) 
From user as A, user as B
Where A.userid = B.referrerid
Group By A.userid

no syntax error
shows count correctly
shows userid correctly
but it shows the username of the new members, not the old one that its id is the userid

Thanks again Danicek 🙂

Danicek

version 1.3

Select 
A.UserId, A.UserName, Count(B.ReffereId) 
From User A, User B
Where A.UserId = B.ReffereId
Group By A.UserId

_____

WELL DONE Danicek
version 1.3 worked 😃

thins one works fine too

Select  

A.userid, A.username, count(B.referrerid)
From user as A , user as B 
where A.userid = B.referrerid 
Group By A.userid

changing the letters was the case
and may be u can c that mysql uses (as)

WELL DONE FRIEND 🙂

Danicek

Just to add little to this...

MySQL manual:

mysql> SELECT CONCAT(last_name,', ',first_name) AS full_name
FROM mytable ORDER BY full_name;

The AS keyword is optional when aliasing a SELECT expression. The preceding example could have been written like this:

mysql> SELECT CONCAT(last_name,', ',first_name) full_name
FROM mytable ORDER BY full_name;

_____

nice to c u here again Danicek 🙂

I checked that and it was totally right
as was optional
looks like I confuse u and others :s

I tried to know why was the first query that u wrote was giving me a doesn't exist table error
which I discovered later during the post
it was because the table User is different than user
and i used ur query directly giving me that

Table 'llkl_vb.User' doesn't exist

first I thought that because A or B

u r totally right friend
thanks a lot for this information
that is new for me
thanks Danicek 🙂