Multi user php mysql

What is the best way to make a multi user database & PHP script

[syndeticFT]

I can make a mysql data base and PHP script to access it.

Now I want to share the functionality with other users. I could add an extra field to every table and re code the PHP to include an extra test in the mysql where clause but this will necessitate a lot of re coding and even then there is always the risk that due to a bug or a malicious user that data belonging to one user will be served out to another. So before I start re coding I figured I would ask if there is a good way to make multi user applications.

For example a web site like hotmail where each user is independant of every other user.

So is there a recomended way of making multi user web sites. Any suggestions or references to articles or books on this would also be appreciated.

[ergophobe]

You should not add fields to each DB table. What you want to do is create a login system and have everyone log in and then, if they are authenticated, give them access to the DB.

If you don't know how to make your own login system, there are a zillion tutorials on the subject if you do a web search on "php user authentication".

As it turns out, yesterday I was evaluating a bunch of GPL user management tools from the very complex (phpgacl) to the relatively simple (patUser) and the PEAR package LiveUser, which I could not get to run through a successful login with their example files, so I gave up.

Anyway, if I didn't know how to write one and wanted a fairly decent yet relatively simple package to manage user authentication, I would probably go with patUser.

[syndeticFT]

Yes a login system is needed and I have written and use a simple system. No problems in that regard.

The thing is though that the user data needs to be kept completely separate so that each user thinks that they are the only user.

Like in hotmail, each user is independant of all other users.

The difficulty I find with simply adding a column to each table (to signify ownership) is that the SQL can become much more tricky especially if the sql queries include table joins.

So the question has to do with how does one best structure the tables and databases for multiple users. For example one could have a new database for each user and then based on the login/session open only that database and then obviously all mysql queries would only relate to that users data. The down side of this is that if I change the program such that a table structure has to change, then I have to find and update that table in many data bases (one for each user).

[ironik]

Definitely avoid creating multiple databases, what you need can be achieved much, much more simply.

In most cases user accounts should have allocated 1 table, with a column set as a `unique identifier` (primary key). Even if you have multiple systems, most of the time you will want a central store of user accounts (unless for security reasons you need to have them logically gapped). I'm involved in a project at the moment aligning multiple accounts across systems... it's a real pain, and when your system gets large then you are in for a world of hurt if everything isn't designed properly from the start.

If you want to restrict what the user can do, you'll need something that defines their level of access... just add an access level column.

There are hundreds of tutorials out there, my only tip would be stick to 1 table for unique user account information, don't disclose a unique primary key to the user and do your research on cross site exploits and SQL injection attacks so your login system is fairly safe.

[ergophobe]

Just filter based on user id. The user only sees what he has permission to see

[syndeticFT]

Can someone point to a relevant tutorial or article. Google provides many many many hits.

When you say filter I take that to mean a SQL query like
$query = select all from tablename WHERE user = abc

Does this scale reasonably? It seemed to me that when I attempted to simply add a WHERE clause to my existing code, that in some of the more complicated SQL statement, especially those that involved table joins, that there was the potential for errors.

[ironik]

That's exactly what you'll need to do to filter for your unique user id.

Here's some decent articles/tutorials on login systems:

[url=http://www.sitepoint.com/article/great-client-login-system]Sitepoint: Great Client Login System[/url]
[url=http://www.zend.com/zend/tut/authentication.php]Zend: mimic http authentication with php[/url]

You could also check out tutorialized.com or pixel2life.com who have directory listings for php.

Using a where clause to find data is essential to find anything from your database and if your statement is correctly formed it won't cause any problems (are you trying to integrate this into existing, more complex code?).

[syndeticFT]

Is there a way to do the SQL in two steps so an ititial query that returns only the rows owned by the particular user and then a second query on that data that would extract the particular information that I want.

If this were possible then it would make converting a single user php mysql script to multi user much easier. As i indicated before, simply adding to a where clause the bit to only select rows related to a particular user is OK for simple SQL statements but becomes much more difficult when there are complex sql queries involving multiple tables.

[freeflight2]

$query = select all from tablename WHERE user = abc

Does this scale reasonably?

With an "add Index(user)" to the table it scales to a couple 100M+ rows