+ Post New Thread
Page 2 of 2 FirstFirst 12
Results 16 to 19 of 19
Web Development Thread, Creating a new database in php and mysql in Coding and Web Development; The biggest problem with a bunch of dynamically named tables is that it is not so intuitive to perform queries ...
  1. #16
    Friez's Avatar
    Join Date
    Dec 2006
    Posts
    839
    Thank Post
    22
    Thanked 22 Times in 21 Posts
    Rep Power
    24

    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;

  2. #17

    Join Date
    May 2006
    Location
    Leicestershire
    Posts
    345
    Thank Post
    0
    Thanked 1 Time in 1 Post
    Rep Power
    0

    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?

  3. #18
    contink's Avatar
    Join Date
    Jul 2006
    Location
    South Yorkshire
    Posts
    3,791
    Thank Post
    303
    Thanked 327 Times in 233 Posts
    Rep Power
    119

    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.

  4. #19
    Friez's Avatar
    Join Date
    Dec 2006
    Posts
    839
    Thank Post
    22
    Thanked 22 Times in 21 Posts
    Rep Power
    24

    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!



SHARE:
+ Post New Thread
Page 2 of 2 FirstFirst 12

Similar Threads

  1. MYSQL Help
    By danIT in forum General Chat
    Replies: 2
    Last Post: 18th December 2007, 10:52 PM
  2. Replies: 4
    Last Post: 28th February 2007, 10:37 AM
  3. using mysql & php on a W2K SP2 server
    By beeswax in forum Web Development
    Replies: 44
    Last Post: 29th June 2006, 09:54 AM
  4. Replies: 2
    Last Post: 28th March 2006, 01:15 PM
  5. php MySql
    By iking in forum Books and Manuals
    Replies: 0
    Last Post: 11th July 2005, 04:35 PM

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •