Multiple Users on one database

[gevensen]

I am using php to develop a financial database tracking income/expenses ect

 

I am to the point where it is working perfectly and I would like to add the ability to have 2 or 3 concurrent users

 

I have added a user in mysql through my webhost and linked the connect db to the specific users

 

My webhost says I need a vpn or dedicated host, i cannot have php add users to the mysql.user table

 

Am I missing something?

 

I can create a pool of them manually to be linked to new users as they are added but I wanted to to it automatically through the signup process and

not have to set up a pool periodically but it doesnt seem possible to do it any other way.

[gizmola]

Why do you want to make an entirely different mysql user?  Will this user be used to create a new database?

[gevensen]

its a financial database for charitable contributions and expenses

now I have one of my testers want to be able to have 2 people entering data

 

I have been looking but cannot find the answers I am looking for

 

For example

 

If one goes to edit a contribution in the table and the other enters a new contribution on the same table but a different record number

will the table be inaccessible to him because the other table has a record being edited.

 

Each person has a username in the system (not mysql) for use with sessions

 

or would i be better of looking at the pages and make sure the mysql connection is closed after each page load, the pages do use a lot of jquery with ajax now also

 

[gizmola]

Ok glad we cleared that up.

 

There is no reason for you to have mulitple "mysql user accounts" .  Your application is the one connecting to mysql, not the users themselves.  The concept of individual users should be something that exists in your application.  Typically people implement a user table.

 

Mysql is built for concurrent access.  There is nothing special you need to do. Someone editing a row does not stop another person from reading information or inserting new rows.

 

With that said, I don't want to write a novel here, but I highly recommend that you use the innodb engine type for your tables.  If you did not start that way it is not a major problem -- you can alter the tables to change them to innodb using the ALTER TABLE command.

 

For a system dealing with finance you will inevitably want to have transactions and mysql with the default isam engine does not support transactions.  Also innodb has constraints and implements row level locking, whereas myisam only can do table locking.  That does not mean that you will have contention problems because the way the table locks in myisam work is extremely fast.  However in general innodb performs better and has a lot of features that are considered essential in most other RDBMS's. 

 

In your code you can close your mysql connections if you want to, and it's good practice to have clean code, but it's not necessary as php has page scope and the connections will be garbage collected when the script has executed.  Unlike many other rdbms's the mysql connection process is extremely lightweight so making and closing connections in every script is not a performance concern.

 

 

[gevensen]

thanks for the heads up