Returning Multiple Copies of Same Entry

[SteveCatbert]

Query:

select DISTINCT * from (sv1ranks as svr1, sv1profile_fields_data as svp1)
inner join
sv1users as svu2 on svr1.rank_id = svu2.user_rank
inner join
sv1users as svu1 on svp1.user_id = svu1.user_id
where site_order between '01A' AND '34D'
order by site_order asc

I am using a basic while loop to display the entries. I am getting all of the information I need from the query above but it duplicates its self.

    while($r_user_rank_search = mysql_fetch_assoc($q_user_rank_search))
    {
    ECHO EVERYTHING;
}

Actual Page

 

http://svcommand.com/personnel.php

 

Any idea what is going on? Also, mysqli does not work due to the php version of the server.

[Muddy_Funster]

Instead of SELECT * list the columns you want to return in the format of tablename.columnname (where tablename is the alias you are using.  Then explicitly define the join for sv1profile_fields_data the way you did for the other two tables.

 

That probably won't fix the issue by itself, but try it and post up the new query if it's still giving duplicates.

[SteveCatbert]

Did that and it did not change anything.

select DISTINCT 
svr1.rank_title, svp1.pf_fleetposition, svp1.pf_usergamesplayed, svu1.username
from (sv1ranks as svr1, sv1profile_fields_data as svp1)
inner join
sv1users as svu2 on svr1.rank_id = svu2.user_rank
inner join 
sv1users as svu1 on svp1.user_id = svu1.user_id
where site_order between '01A' AND '34D'
order by site_order asc

[Muddy_Funster]

Almost - you did part of it, but you didn't sort out the joins.  You're also joining the same users table twice, by giving it two aliases, even though you are only taking a single column from it.  That's the most likely reason for the issue, which is far more apparent now that you cave the column list that you are selecting.

SELECT DISTINCT
  sRank.rank_title,
  sPro.pf_fleetposition,
  sPro.pf_usergamesplayed,
  sUser.username
FROM
  sv1users AS sUser
INNER JOIN 
  sv1profile_fields_data AS sPro
ON
  (sUser.user_id = sPro.user_id)
INNER JOIN
  sv1ranks AS sRank
ON
  (sRank.rank_id = sUser.rank_id)
WHERE 
  site_order BETWEEN '01A' AND '34D'
ORDER BY
  site_order
ASC

Let me know how that works out (will probably need tweaked before it will run)

[Barand]

 

from (sv1ranks as svr1, sv1profile_fields_data as svp1)

That is creating a "cartesian join" on those two tables which will join every record in one table with every record in the other.

 

If that isn't what you want to do, you should use explicit join syntax as you have with the other table (ie FROM A JOIN B ON ....)