Creating a new database in php and mysql

[StewartKnight]

I'm trying to pass a variable from a form, to create a new database.

something like this...

 

 

$firstname = $_post['firstname'];

 

$query = 'create database $firstname';

 

mysql_query($query);

 

 

I'm using the latest versions of mysql and php.

Can anyone tell me how to create a new database using a variable???

[mac_shinobi]

I'm trying to pass a variable from a form, to create a new database.

something like this...

 

 

$firstname = $_post['firstname'];

 

$query = 'create database $firstname';

 

mysql_query($query);

 

 

I'm using the latest versions of mysql and php.

Can anyone tell me how to create a new database using a variable???

 

 

Just looked at this site

 

http://www.php-mysql-tutorial.com/create-mysql-database-with-php.php

 

From what that is saying it looks like you would have to do something like so :

 

$query = "CREATE DATABASE " & $firstname;

$result = mysql_query($query);

 

'---------

 

you obviously dont have to assign the query to $result but that is how they have dont it so I just left it as is

[webman]

If you use single quotes, you must escape them to include variables.

 

$query = 'create database ' . $firstname;

 

This would also work:

 

$query = "create database $firstname";

 

You might want to look into some validation and security routines for the POST variable (protect against SQL injection etc) as it could potentially allow people to inject rogue queries.

[mac_shinobi]

@ webman

 

Never knew you could include variables inside of a string in php as per

 

$query = "create database $firstname";

 

Maybe I did it wrong above :-S *shrugs*

[Friez]

It would generally be a BAD idea to create anything that makes databases off the hoof, HECK there shouldn't even be a need to dynamically create tables (you can end up with some nasty nasty issues). A properly structured database should have perfectly normalised tables.

 

However, if you're just wanting to create structures first time round and be done with it forever more (possibly with a few tweaks later on), you should check out PHPMyAdmin http://www.phpmyadmin.net/home_page/index.php

 

It'll let you set up databases, tables, and all that malarky and also let you directly enter SQL.

[StewartKnight]

Webman & gecko, neither of your methods worked.

 

What I want is this;

 

The ICT teacher want the kids to produce a form that collects data and stores it in a database.

 

I want a form where the kids create a database with their name as the db name. The fields for the db are setup by the php script once the db has been created.

 

I'm tearing my hair out on what I thought would be a simple thing to achive. I can use the php to create a statically named db, but not a variable.

[webman]

Try echoing the SQL query before running it and see if it is producing the right results.

[mac_shinobi]

does it have to be in php ?

 

Just I have used classic ASP a lot more then I have with php, cant try anything out now but will have a go with php if your insistant on using php.

[StewartKnight]

anything that works really!

[mac_shinobi]

Webman & gecko, neither of your methods worked.

 

What I want is this;

 

The ICT teacher want the kids to produce a form that collects data and stores it in a database.

 

I want a form where the kids create a database with their name as the db name. The fields for the db are setup by the php script once the db has been created.

 

I'm tearing my hair out on what I thought would be a simple thing to achive. I can use the php to create a statically named db, but not a variable.

 

What database format are you wanting to use ?

 

MS Access , SQL or something else ??

 

Just each database format has and uses a different connection string, also with ms access it locks you out after so many people use it unless you do some weird stuff ( which I have never done before ) in which case it creates virtual tables and it slows down the more people that access it.

 

Kind of like sleuth lol.

 

If you want to use SQL then I wont be able to help with creating of the sql database but I can use php to write to and access the info from the sql database, provided I know the design of the tables ie what fields are in the table and in what order ie if you have tblCustomer with fields

 

Customer_ID

Forename

Surname

Age

Date_Of_Birth

 

Then I need to know those fields in the precise order that they are in.

[webman]

StewartKnight: Could you post the code to my pastebin (omitting MySQL username/password) to have a look at it and see where the problem is?

[Friez]

StewartKnight: Make tables!!, not only is there no point in creating (what hundreds? thousands? of) DATABASES but theres no need to even make multiple tables on the fly. Absolute mess! And besides, your SQL engine will probably cry.

 

Create some table structures. Here's some food for thought:

 

 

Table: tblUsers // a list of your lovely students

UserID (autoincrement BIGINT) [Primary Key]

Username

Firstname

Lastname

// yada yada

 

Table: tblStudent_Tables // describes student tables/forms

TableID (autoincrement BIGINT) [Primary Key]

TableName (string)

UserID // the User that this table is associated with

 

// this is the magic bit -- linking up fields to a table

Table: tblStudent_Table_Link // links in individual fields to their table

TableID (unique) // links to the relevant table

FieldID (unique) // links to the relevant field

 

Table: tblStudent_Fields // this table will describe the fields of a students table

FieldID (autoincrement BIGINT)[Primary Key]

FieldTitle (string)

FieldDefaultValue (string) // possibly allow for a default value to be entered

 

// THUSFAR we have only described the students table.

// now lets think about storing that data

 

Table: tblFieldStorage // basically data that people entered in each field

FieldID (unique) // links to the field it is relevant to

FieldValue (string) // the data said person entered into that field

SessionID (unique) // the overall data input session this field is assocaited with

 

Table tblStudent_Form_Sessions

SessionID (autoincrement BIGINT) [primary key]

// plus anything else that you may want to autocollect like names and IP's that are not dynamically created by students HERE

 

So lets re-view:

 

// we describe the students custom form/table structure like so:

 

tblStudent_Tables describes the table

tblStudent_Fields describes all the fields in their table

tblStudent_Table_Link links fields with tables

 

// we store arbitary data that they create like so:

 

tblFieldStorage stores the values of a field and links it to a specific data entry session

tblStudent_Form_Sessions stores a list of sessions

 

 

You should NEVER have the need to create databases or tables on-the-fly structured query language isn't meant for that! Create some structures and you're away. That should be able to store all the data that you want.

 

Of course, you'll have to code a fair bit of PHP to support this kind of stuff. But you're asking for that with such a dynamic system to begin with.

 

PHP / mySQL is a good choice, your life could become hell with other techs.

[PaulBM]

I agree with Friez and others who've suggested tables rather than databases. I'd say the teacher used the word database as a generic term, but didn't actually mean a unique database per student.

 

Find out what the teacher want them to store and set up tables ready to accept that info.

 

The other problem with creating a database in granting permissions to access it. That's probably why you can't create one programmatically. If you don't run the server, your ISP may have blocked you from creating a database.

 

Also worth checking the mysql_error() after trying to create the database.

[mac_shinobi]

I agree with the above, you might want to consider creating tables on the fly with each table having there own name and surname so that way if there are 2 or more students with the name Smith for example then you dont want one student called smith over writing the other smiths table.

[PaulBM]

That's a good point. I'd use LDAP* and use their username as the table name if individual tables are required. But really a pre defined table or tables would be the best way to go.

 

I've just finished the first version of a survey system written is PHP and mySQL, it uses LDAP to find their username and store their results.

 

The first refinement will be to look at group membership and only allow certain group members to take the survey.

 

 

*LDAP is a bit of devil to get working, download this LDAP Browser if you're going to give it a shot, it's the best way to find out all the parameters for your AD.

[Friez]

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;

[Nij.UK]

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?

[contink]

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).

 

$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.

[Friez]

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!