Quick Question

TheMayhem

I'm not even sure if their is a php or mysql function for this but maybe there is. I have a simple table. It is a match table. The two columns are like this.

id | uid1 | uid2

Basically what I want to do is figure out a user's rival. So in order to do that I simply want to be able to see who each user's rivals are. Basically whoever 1 user has faced the most is their rival. So below is an example of what a table might look like.

id | uid1 | uid2
1 | 1 | 2
2 | 3 | 1
3 | 1 | 2

If I try to find for a user id of 1 he has 3 total rows in the data. Out of that he has faced a user with the id of two twice, and the id of 3 once. So therefor his rival would be the user id 2. Is there any simple way of doing this, the only catch is there is about 5,000 entries in the table.

lucasrd

Something like this should work:

select uid1,uid2,count(*) as rival_count from rivals group by uid1,uid2 order by rival_count

TheMayhem

A little confused on the statement but it appears from query I entered in the db that you were on the right track. I'll give a little more info and hopefully you can help me complete this task.

$user[id] is going to equal the id of the user who I'm trying to find out for his rival is.
Btw the table name is livebattle not rivals.

So should we just do a normal sql query here to find out exactly what id or number is the $user[id]'s rival or a fetch array.

Let's say $rival will either be the name of the sql query or sql fetch array, so how should it look like on the finish?

lucasrd

Do you have a users table? We should be able to get all this done in one query.

TheMayhem

Of course the users table name is user. The users row for id is id. And the users name is username...

But remember everything for match record is being stored in the livebattle table.

lucasrd

This query might have some potential

$sql = "select r.uid1,r.uid2,u.username,u2.username, count(*) as rival_count from livebattle as r ";
$sql .= "left join user as u on r.uid1=u.id ";
$sql .= "left join user as u2 on r.uid2=u2.id ";
$sql .= " where r.uid1='".$user[id]."' group by r.uid1,r.uid2 order by count(*) desc";

TheMayhem

Originally posted by lucasrd
This query might have some potential

$sql = "select r.uid1,r.uid2,u.username,u2.username, count(*) as rival_count from livebattle as r ";
$sql .= "left join user as u on r.uid1=u.id ";
$sql .= "left join user as u2 on r.uid2=u2.id ";
$sql .= " where r.uid1='".$user[id]."' group by r.uid1,r.uid2 order by count(*) desc";

[/B]

Why is the query split up into 4 sql statements?

lucasrd

broken up just for readability...the "dot equals" (.=) is how you concatenate strings in PHP...

when you call mysql_query($sql) it will evaluate as one sql statement.