problems with DISTINCT

[sparedog]

I am having problems with Distinct. It's not working for me in this code. i'm struggling hard to work out why. please help

 

$myfriend_list = mysql_query("SELECT friendwith FROM friends WHERE member = '$myuname'");
while ($row1 = mysql_fetch_assoc($myfriend_list)) {
$result1= "$row1[friendwith]";

$myoutput       = mysql_query("SELECT DISTINCT hockey FROM teams WHERE member ='$result1'");
while ($row2 = mysql_fetch_assoc($myoutput)) {
$result2= "$row2[team]";

$myoutput1       = mysql_query("SELECT DISTINCT * FROM players WHERE barcode ='$result2'");
while ($row3 = mysql_fetch_assoc($myoutput1)) {
$result3= "$row3[positions]";
echo $result3;}}}

[gizmola]

Let's take a look at your assumptions:

 

-people know my database (no we don't)

-I have 3 queries here, something is wrong, but I haven't explained what it is yet.

-I'm assuming it's the distinct, but again I have 3 quereies and some PHP code, although I've provided no output.

-"It's not working" is a good description of my problem

 

I'd love to help you really, as I've helped hundreds and probably thousands of people before you.  But you have to articulate your problem better.

[sparedog]

lol sorry about that, i'll try with this

 

i have the following databases:

friends: member, friendwith

teams: member, hockey

players: positions, hockey

 

here's a slightly different code to make it clearer.

$myfriend_list = mysql_query("SELECT friendwith FROM friends WHERE member = '$myuname'");
while ($row1 = mysql_fetch_assoc($myfriend_list)) {
$result1= "$row1[friendwith]";

$myoutput       = mysql_query("SELECT DISTINCT hockey FROM teams WHERE member ='$result1'");
while ($row2 = mysql_fetch_assoc($myoutput)) {
$result2= "$row2[hockey]";

$myoutput1       = mysql_query("SELECT DISTINCT * FROM players WHERE hockey ='$result2'");
while ($row3 = mysql_fetch_assoc($myoutput1)) {
$result3= "$row3[positions]";
echo $result3;}}}

 

when echoing $result2 or $result3, if there is more than one entry in the 'teams' table with the same 'member' name, if the member has 2 or more friends in the same team, then this is reflected. a duplicate in the hockey or positions list. i would like the echoed list to not show the duplicates.

 

[gizmola]

Ok, a lot better explanation.  So the 2nd query, if I understand you correctly, is working correctly, only you are thinking that DISTINCT does something different than it does.

 

Consider this:

 

mysql> select * from teams;
+--------+-------------+
| member | hockey      |
+--------+-------------+
| bob    | Black Hawks | 
| bob    | Flyers      | 
| bob    | Kings       | 
| joe    | Caps        | 
| joe    | Penguins    | 
+--------+-------------+
5 rows in set (0.00 sec)

mysql> select distinct hockey from teams where member = 'bob';
+-------------+
| hockey      |
+-------------+
| Black Hawks | 
| Flyers      | 
| Kings       | 
+-------------+
3 rows in set (0.00 sec)

 

Now if your complaint is that you are getting 3 rows in this result set, then you don't understand what distinct does.  Distinct removes duplicates from the result set.  Since your result set only includes teams, you are getting the results that are expected -- any unique teams.

 

This can be further illustrated if we have a true duplicate row for bob:

 

mysql> select * from teams;                 
+--------+-------------+
| member | hockey      |
+--------+-------------+
| bob    | Black Hawks | 
| bob    | Flyers      | 
| bob    | Kings       | 
| joe    | Caps        | 
| joe    | Penguins    | 
| bob    | Flyers      | 
+--------+-------------+
6 rows in set (0.00 sec)

 

Now you can see clearly the effect of the distinct:

 

mysql> select hockey from teams where member = 'bob';                           
+-------------+
| hockey      |
+-------------+
| Black Hawks | 
| Flyers      | 
| Kings       | 
| Flyers      | 
+-------------+
4 rows in set (0.00 sec)

mysql> select distinct hockey from teams where member = 'bob';                  
+-------------+
| hockey      |
+-------------+
| Black Hawks | 
| Flyers      | 
| Kings       | 
+-------------+
3 rows in set (0.00 sec)

 

In fact distinct is internally the same mechanism in mysql as using a GROUP BY clause on the columns specified.

 

 

So, I'm actually not 100% clear on what it is that you really want, in plain english.  It occurs to me that you have relationships between these 3 tables, such that you should be able to eliminate the need to do 3 seperate questions, by simply using joins -- and probably simple inner joins, but that's a bigger topic, and I'm again not clear on exactly what you're trying to do with this set of loops. 

 

 

 

 

 

 

 

I might also point out that your outside query, is going to cause your internal query to be run multiple times, if a member has multiple friends -- but hopefully you were clear on that originally.

 

 

 

 

 

[sparedog]

Thanks, that was quite clear.

 

The example you gave is what happens, if 'bob' has no friends. But if 'bob' is friends with 'joe', and 'joe' has also played with the 'flyers', then the results for:

 

mysql> select distinct hockey from teams where member = 'bob';

 

Is black hawks, flyers, kings, flyers. I get a list with 'flyers' in it twice.

 

The last sentence you wrote:

"I might also point out that your outside query, is going to cause your internal query to be run multiple times, if a member has multiple friends -- but hopefully you were clear on that originally."

I didn't understand this, would this be the reason for the duplicate in the list?

[gizmola]

Yes absolutely, because you have 3 nested queries.  The first query is going to produce a row for every "Friend" the particular member has, and for each friend, there's the teams, and for each team, you then will run the players query.

[sparedog]

Great, i think i'm getting it.

 

So do i have to unnest the queries? if so, how would i do that? foolishly, I have tried putting each curly bracket after each query, instead of putting them all at the end of the three queries as in the code i posted, but this makes the echo blank. i am assuming this is because '$result1' would be only one field long, and so would be '$result2'.

 

if anyone knows of a tutorial that'll help me achieve this, i'd be most greatfull.

[sparedog]

i still can't remove the duplicate results. How would i un-nest these queries and not show any duplicates, yet still show all the relevant entries in the last table queried?

[gizmola]

sparedog:

 

The simplest answer is that instead of looping through the queries, you simply take the first result.  How to do this?  It's extremely basic, which concerns me, because you presented this code and it doesn't seem you understand it yourself.  The answer to how to do that is programming 101 - change the while to an if.  I'm concerned you won't be able to understand what I'm trying to help you with if you don't understand what a while loop does.

 

I also still don't understand what it is that you are trying to do, or why.  From what I see --

 

1. You have a column that defines a member and that member's "Friends" ie. other members.  I'm not sure if there could be multiples, but if so, your database structure isn't really well thought out, because friends should be in a separate table.  Despite this fact, I can still work with what you have, so long as I'm clear on the design.

 

2. You have a table that defines the "Teams"  (fantasy hockey i'm guessing?) that a person could be associated with.

 

3. For any team, there are many players on the team.

 

Now it seems that you are saying:

 

- Get me 1 friend (I don't care if there are many)  AND for that friend get me 1 Team (again there could be many teams associated with that person) and then get me the players for that team.

 

This approach doesn't make sense to me, in terms of -- I don't see a valid application of this.  Databases have no implied ordering of result sets, so this seems entirely arbitrary -- I have 5 friends but just give me "Fred" and "Fred" has 4 teams, but just give me the "Bruins".  How is this useful?

 

Secondarily, as I said previously multiple queries isn't the approach to take here -- the approach is to do this in 1 query which joins the related tables together.

 

If you can explain, in plain language, what result set you are looking for, what columns should appear in the result set, and the basic logic of what you're trying to get from the tables, I can probably concoct said query for you, which would make the issue of these nested queries go away, and also possibly help you think through your application, not to mention future additions to your app.

[sparedog]

please bear with me, i think my english is breaking up lol

 

i would like it to work like this

a player enters his or a friends playing 'id'.

a list is made that shows all the playing 'id's done by a user and all the friends on a friendlist of that user. the list needs to be in the order of the most requested 'id' first, with the least requested 'id's at the bottom.

 

the player 'id' is linked via a sport table 'teams', to a players table ' players'. the list should show the position this user has played in order of how many times it has been entered by either the user or one of the user's friends.

 

i hope this makes it clearer.

 

the hockey game is just a example i was using to help the post (it didn't work lol). i want to know how to get this kind of (using an output from one table to effect what is searched for in another table) to work, as i need to use it for a few projects.

[sparedog]

Aaargh, i asked my friend to translte this into english and they posted the worng thing 

 

it is meant to read this. i appologise. please ignore or remove the last posting by me.

 

i would like it to work like this

 

a player enters his or a friends playing 'id'. these are preassign numbers, they dont change.

 

list is made that shows all the positions played by a user and all the positions played by friends on a friendlist of that user. the list needs to be in the order of the most entered 'id' first, with the least entered 'id's at the bottom.

 

the player 'id' is linked via a table 'teams', to another table ' players'. this 'players' table contains the positions column.

 

let me know if this clears it up?