Dan Lewis
Member
Registered: 31st Jan 05
Location: Leicestershire
User status: Offline
|
Another one again 
I have a table the coloums ID,USER_NAME,ROLE
The results from this table are displayed on the html page. But in the table there are duplicate entries. I am not bother about the duplicates but i dont wish the user to see them.
code:
$query = "SELECT ID, USER_NAME, ROLE, FROM FOX_MASTER_ROLE_OF_USERS ORDER BY ROLE, USER_NAME";
$result = mysql_query($query) or die(mysql_error());
echo "<br />";
echo "<table>";
while($row = mysql_fetch_array($result,))
{
echo "<tr>";
echo "<td width=200>" . $row['USER_NAME']. "</td><td width=200>". $row['ROLE']. "</td width=200><td>". "<a href=javascript:confirmDelete('delete.php?id=$row[ID]')>[Delete]</a> "</td>" ;
echo "</tr>";
}
echo "</table>";
Thats the code. If i use SELECT DISTINCT, It still shows the duplicates as im all the ID's are different. But i need to remove the duplication but still pass the ID.
|
James
Member
Registered: 1st Jun 02
Location: Surrey
User status: Offline
|
Well if they are actual duplicates in the table, which one do you want to see?
Say you have this:
ID = 1, User_Name = James, Role = Admin
ID = 2, User_Name = James, Role = Admin
ID = 3, User_Name = James, Role = Admin
ID = 4, User_Name = James, Role = Admin
Which one do you want to display?
Also, why do you have duplicates?
|
Ian
Site Administrator
Registered: 28th Aug 99
Location: Liverpool
User status: Online
|
You can remove ID and use SELECT DISTINCT.
If you want ID you then have the question of which ID it is that you want to display.
You would GROUP BY in this instance, and choose your aggregate function.
SELECT MIN(ID), MAX(ID), GROUP_CONTACT(ID), USER_NAME, ROLE FROM FOX_MASTER_ROLE_OF_USERS GROUP BY USER_NAME ORDER BY ROLE, USER_NAME
(I've also deleted the redundant comma after ROLE in the field list)
Note you may also alias the aggregate fields -
GROUP_CONCAT(ID) AS IDLIST
Not entirely sure I like your data structure though! Multiple records for the same user? Not ideal. If the delete operation is actually to delete all the rows which relate to that user then USER_NAME is the field on which you should do the delete, not ID. Otherwise you start hacking in loops to your delete operation just because you data is bad. Or you have the issue that if you delete once on one ID, the user name is still displayed to the user next time they run the app, so it will look like the delete operation has not been successful.
|
Dan Lewis
Member
Registered: 31st Jan 05
Location: Leicestershire
User status: Offline
|
Thanks for the replies. I agree yes if i do not show the duplicates then the delete key is a pretty stupid Idea. I think i may start again and add some validation before the submit to the table as that would stop the duplication and the need for an extra table.
Thanks though
|
Paul_J
Member
Registered: 6th Jun 02
Location: London
User status: Offline
|
Just make the Username unique...
So when the user registers, it runs a function to check availability, and if it returns False then ask for a new username from user and not add it to database.
Function IsAvailabile(Username As String) As Boolean
Dim result As Boolean = false
(Connect to DB bla bla bla)
Execute SQL query 'Select Username from Table where username=username'
if the select returns nothing then set result = true
else result = false
return result
end function
then in your code you just have:
Take user input for username
If IsAvailable(username) = true Then add it to database
else
prompt user for new username
end if
(obv convert this to your language / actually write the proper code), but you get the idea.
|
Dan Lewis
Member
Registered: 31st Jan 05
Location: Leicestershire
User status: Offline
|
Cant make the username unique as the username could have multiple roles assigned to it.
|
Paul_J
Member
Registered: 6th Jun 02
Location: London
User status: Offline
|
I don't really understand what your thing is, or what you're trying to achieve...
but why have the roles in the same table as the user list?
Have a table for users and a table for roles
User Table:
Unique ID
Username
Role Table:
Role ID
Role Name
User ID
Then you can have:
User Table
ID: 1 - Name Paul_J
ID: 2 - Name Ian
ID: 3 - Name James
and in Role Table
ID: 1 - Role: Admin - User ID: 1
ID: 2 - Role: Mod - User ID: 1
ID: 3 - Role: User - User ID: 1
ID: 4 - Role: Mod - User ID: 2
ID: 5 - Role: User - User ID: 2
ID: 6 - Role: User - User ID: 3
Paul_J is assigned role of User, Mod and Admin
Ian is Mod and User
James is just a User
If you want to do it that way...
Or just have a different column for each 'Role Type' as a boolean, so you have the user and then a tick in the admin, mod and user box...
etc
|
James
Member
Registered: 1st Jun 02
Location: Surrey
User status: Offline
|
Ideally you should have 3 tables.
Role = List of roles with an ID for each.
User = List of users with an ID for each.
UserRoleMappings = Table to link the 2 together. Columns would be UserId, RoleId. (Could optionally have an ID for the role mapping too).
This way your DB is fully normalised.
|
Paul_J
Member
Registered: 6th Jun 02
Location: London
User status: Offline
|
yeh that's the best ^^
Especially if you want to put a lot of columns about your roles, like 'Name', 'Description', etc
|
Ian
Site Administrator
Registered: 28th Aug 99
Location: Liverpool
User status: Online
|
Or store a decimal and do a binary conversion on it to get a load of bools.
|
