RE: [PHP] File upload and database optimization questions

> > 3. At the time of upload, it is possible to specify a list of > > users who can "view" and "modify" the file. Currently I am storing > > this list as a whitespace-separated list in a mySQL TEXT column. > > > > Column "view" data: 3 11 1 > > > > At the time of displaying the list, I first SELECT * the entire list > > of files, and then use explode() in combination with a "for" loop to > > split up the "view" field and match each element againt the current > > user's ID. Only if a match is found will I display the filename. > > > > I suspect this approach is not optimal. Can anyone suggest a > > better approach? > > Rather than a whitespace-separated list, store one ID as a "|ID|". So > your field might look like: > > Column "view" data: '|3||11||1|' > > That way, knowing the user's ID who is viewing docs, you can select > <<blah blah>> WHERE view LIKE '|$thisusersidnumber|' ... no longer > any need to step through and explode the array. > > Jason No.. very bad. Not good. I suggest that you read this article on database optimization (not to sound rude, but I honestly think you might learn something, heck, I did when I was writing it =)): http://www.newbienetwork.net/sections.php?op=viewarticle&artid=17 Using what you described above with the ||, you have already broken the first normal form. Serious problems can develop from that implementation. Rathers, use this format: document_id users_allowed ----------- ------------- 001 1 002 2 003 1 003 2 003 3 Basically the primary key is in fact both the docid and users_allowed columns. This was is the proper way of displaying the above information. Jason Lotito www.NewbieNetwork.net Where those who can, teach; and those who can, learn.