Dealing with Comma Delimited Data?

[Jamez]

Ok I'm trying to create a management system where an admin can check selections from a database:

Table1
id | password
1 | pass1
2 | pass2
3 | pass3
4 | pass4
5 | pass5

Table2
uID | User | Access
1 | user1 | 2,3
2 | user2 | 1,2,3,4
3 | user3 | 5

I need to figure out how to add the data into a single cell in comma delimits so the admin can check id so and so and it will add them in seperated by commas.

Then i need to know how to query that cell and seperate each number so i can display all the passwords they have access to...

I've never really dealt with delimits befor so i'm hoping there is a simple function i can use? I already know how to query the database just to pull up regular text and numbers...

Anyone have a page that can walk me through this? 🙂

[AstroTeg]

Its super rare that you'd have a need to store comma-delimited data in a database. It implies your table setup isn't correct.

Its very difficult to do anything with comma-delimited data once its in a database field. So, instead of shoving it all in one field, you should think about adding another table which will store the relationship of those IDs your putting in the comma-delimited field.

For example, Table2 might just be:

Table2
uID | User |
1 | user1 |
2 | user2 |
3 | user3 |

(Notice "Access" has been removed)

Now setup an additional table. This table can be called access. It'll contain 2 fields. The uID from table 2 and the ID from table 1.

Access
uID | ID
1 | 2
1 | 3
2 | 1
2 | 2
2 | 3
2 | 4
3 | 5

Now you'll be able to easily query who has access to what. You'll want to look up inner join, left join, and one-to-one joins to make this all work (or ask more specific questions here).

[Jamez]

Thanks for the reply, if using comma delimits is more difficult then i anticipated then the 3rd table is deff. the way to go.

I have a second question however, i have an excel file filled with data just like a database would be setup, if i save it as a CSV file can i use phpmyadmin to simply "upload" the file into a table?

[Roger_Ramjet]

Of course. In phpmyadmin there is a link for each table right below the alter table box that says 'Insert data from a textfile into table' click on this and fill in the form. It will import the file for you. The file can be comma delimited, or tab, or semi-colon, etc. You just specify which.