refiking Posted April 23, 2008 Share Posted April 23, 2008 I have some records that are stored in my db twice because they have one entry field that's different. For example. One user might be a part of 3 different "dregs" so they will have 3 separate records. which looks something like this: Uid(User ID) Drid (Dreg Id) 1 15 1 28 1 41 When I pull a query asking for the users in 5 dregs (3 of which are the ones included here), it returns this user 3 times. How can I get it to only retrieve this user once and discard the duplicates? Here is the code snippet: $sql2 = mysql_query("SELECT * FROM downer WHERE lid = '$lgs' ORDER BY drid"); while ($row2 = mysql_fetch_assoc($sql2)){ $drid = $row2['drid']; $sql3 = mysql_query("SELECT * FROM dregusers WHERE drid = '{$row2['drid']}'"); while ($row3 = mysql_fetch_assoc($sql3)){ echo '<option><font color="black">'.$row3['username'].'</font></option>'; Quote Link to comment https://forums.phpfreaks.com/topic/102548-solved-trying-to-stop-retrieving-duplicate-records/ Share on other sites More sharing options...
Spaceman-Spiff Posted April 23, 2008 Share Posted April 23, 2008 Use LIMIT 1 if you only need 1 user, otherwise use SELECT DISTINCT. Example: SELECT DISTINCT * FROM tablename GROUP BY Uid; Quote Link to comment https://forums.phpfreaks.com/topic/102548-solved-trying-to-stop-retrieving-duplicate-records/#findComment-525078 Share on other sites More sharing options...
refiking Posted April 23, 2008 Author Share Posted April 23, 2008 No. I need all the users. I just don't want the duplicates of the same user. Which one of the queries should I use the distinct feature you are talking about? Quote Link to comment https://forums.phpfreaks.com/topic/102548-solved-trying-to-stop-retrieving-duplicate-records/#findComment-525083 Share on other sites More sharing options...
sasa Posted April 23, 2008 Share Posted April 23, 2008 use one query SELECT * FROM dregusers WHERE drid IN (SELECT * FROM downer WHERE lid = '$lgs') GROUP BY uid Quote Link to comment https://forums.phpfreaks.com/topic/102548-solved-trying-to-stop-retrieving-duplicate-records/#findComment-525139 Share on other sites More sharing options...
refiking Posted April 23, 2008 Author Share Posted April 23, 2008 Here is the code I changed it to: $sql3 = mysql_query("SELECT * FROM dregusers WHERE drid IN (SELECT * FROM downer WHERE lid = '$lgs') GROUP BY uid"); Here is what the script returned: Parse error: syntax error, unexpected '}' on line 28 Quote Link to comment https://forums.phpfreaks.com/topic/102548-solved-trying-to-stop-retrieving-duplicate-records/#findComment-525150 Share on other sites More sharing options...
refiking Posted April 23, 2008 Author Share Posted April 23, 2008 I had 1 too many brackets. When I deleted it, the script runs, but now it doesn't return any records. It's the same code as before Quote Link to comment https://forums.phpfreaks.com/topic/102548-solved-trying-to-stop-retrieving-duplicate-records/#findComment-525172 Share on other sites More sharing options...
sasa Posted April 23, 2008 Share Posted April 23, 2008 sory i just copy 1st query and miss to change * in field name $sql3 = mysql_query("SELECT * FROM dregusers WHERE drid IN (SELECT drid FROM downer WHERE lid = '$lgs') GROUP BY uid"); Quote Link to comment https://forums.phpfreaks.com/topic/102548-solved-trying-to-stop-retrieving-duplicate-records/#findComment-525304 Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.