Re: Creating a new database in php and mysql
The biggest problem with a bunch of dynamically named tables is that it is not so intuitive to perform queries on (if at all).
I.e. to list what tables a student has made with my above example you'dd simply:
SELECT TableName FROM tblStudent_Tables, tblUsers WHERE tblUsers.UserID = tblStudent_Tables.UserID AND tblUsers.Username = 'BOB';
A simple SQL join.
Thats the beauty of Structured Query Language, since with a bit of design it becomes quick and easy to get data out of a database, sorted and in a form you *want*. Start making a whole slew of tables you'll end up with a massive database full of redundant data and things get rather hard for you to handle/keep track of, let alone trying to write decent queries for it.
They teach (or are supposed to) this kinda stuff at GCSE, I'm surprised people even consider abusing databases like the way thats suggested above o.O;
Re: Creating a new database in php and mysql
its a bit of a long shot, but the username and password that u supplied in the PHP file to connect to the database. have you used the GRANT option to give the user approate rights to create the database?
Re: Creating a new database in php and mysql
Right.. sorry I couldn't respond sooner but here's some input.
1. You need to have a mysql user that has been "GRANT"ed create privs for tables, databases, etc... so first off check that.
2. You then need to be sure you're connecting to MySQL (mysql_connect) before you try to create any tables so the system knows who you are and that you have the relevant privs'
3. When it comes to creating the database it helps if you have some debug output to help you figure out what's going wrong (assuming it is).
Code:
$query = "CREATE DATABASE phpcake";
$result = mysql_query($query);
if(!$result) {
echo mysql_error;
}
As to the discussion on creating databases vs' tables I can heartily recommend this as well.. just provide a different prefix for each set of tables you want to create and you can easily deal with them.
Perhaps the way to go is to have a special table which keeps track of the tables being created along with datestamp info, IP request logged, etc.. which can be used to automate administration (ie: deletion) of any spurious table/data-set creation.
Re: Creating a new database in php and mysql
Quote:
Originally Posted by contink
Perhaps the way to go is to have a special table which keeps track of the tables being created along with datestamp info, IP request logged, etc.. which can be used to automate administration (ie: deletion) of any spurious table/data-set creation.
What you've suggested is similar to the solution I posted above. You store meta-data (data about data) as well as the ACTUAL data, it means you can easily structure your stuff the way the database engine was designed for and still get the result you're after.
Also todays DailyWTF has a great example why you should NOT make thousands of databases/tables on-the-fly.
Go Check It!
