Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 6 of 6
  1. #1
    New Coder
    Join Date
    Nov 2013
    Posts
    12
    Thanks
    6
    Thanked 0 Times in 0 Posts

    PHP CREATE TABLE not actually Creating Table

    Hey all,

    I have another issue that is boggling my mind right now.

    So I have a "signup" form on my site. Once a user signs up I want to insert applicable info into a specific table in the database, create a new table with the userid as the table name, and create a new directory named userid as well.

    The info is getting inserted correctly into the current table correctly. The directory is being created as it should. However, the new table is not being created and I get no error when I run the script. I just can't figure out why the new table isn't getting created in the mysql database.

    Here is the applicable code:

    PHP Code:
    $connection=mysql_connect($host,$uname,$pass) or die("connection in not ready <br>");
    $result=mysql_select_db($database) or die("database cannot be selected <br>");

    mysql_query("INSERT INTO user (username, password, userid, signup, login) VALUES ( '$username','$password','$userid',now(),now())");

    mysql_query('CREATE TABLE '.$userid.' ( UID VARCHAR(255), User_Avatar VARCHAR(255), 
     Page_Title VARCHAR(255), User_Info VARCHAR(255), User_Likes VARCHAR(255), User_Category VARCHAR(255), User_Time VARCHAR(255))'
    );
     
     if (!
    file_exists("$userid")) {
                
    mkdir('../Rusers/'.$userid.''0755true);
            } 
    Any ideas or any help you could give would be greatly appreciated!

    Thanks!

  • #2
    Master Coder felgall's Avatar
    Join Date
    Sep 2005
    Location
    Sydney, Australia
    Posts
    6,640
    Thanks
    0
    Thanked 649 Times in 639 Posts
    Does the username you are using to connect to the database have authorisation to create tables? It isn't something that user would normally be able to do as you shouldn't need to create a new table in the database dynamically - they should already all be there as part of the initial design.
    Stephen
    Learn Modern JavaScript - http://javascriptexample.net/
    Helping others to solve their computer problem at http://www.felgall.com/

    Don't forget to start your JavaScript code with "use strict"; which makes it easier to find errors in your code.

  • Users who have thanked felgall for this post:

    froppo (12-18-2013)

  • #3
    New Coder
    Join Date
    Jul 2012
    Location
    Cape Town
    Posts
    12
    Thanks
    0
    Thanked 3 Times in 3 Posts
    I agree whit felgall.. When you site grows and you get for example 1000 users then you will have 1000 tables in the database which will make things really slow.

    create on table for everyones profiles then use for example
    INSERT INTO user_profiles ( username, avar etc

    Now you will have two tables
    1. user
    2. profiles

    in both you will have a row called username which you will use to match the two with each other to print the right things out. This will makes things much faster and easy er to maintain.

    That is just what i think...

  • Users who have thanked cyber-ninja for this post:

    froppo (12-18-2013)

  • #4
    New Coder
    Join Date
    Nov 2013
    Posts
    12
    Thanks
    6
    Thanked 0 Times in 0 Posts
    Hey Guys,

    Thanks for your response. So I checked the username that I'm connecting to the database with and it has all possible privileges (create, create temporary tables, etc.).

    Also, thank you for your advice on structure. The code I have included is a much more simplified version of what I'm actually building. I need to create a new table because each user will likely have hundreds of lines of data tied to that user's unique id. I figured I could either create a new table for each new user OR i'd have a table with hundreds of columns of data. i decided having a new table per user would work better for what I'm going for.

    So any other ideas why the table isn't being created? Here are all of the privileges that the user has:

    ALTER
    CREATE ROUTINE
    CREATE VIEW
    DROP
    INDEX
    LOCK TABLES
    SELECT
    TRIGGER
    CREATE
    CREATE TEMPORARY TABLES
    DELETE
    EXECUTE
    INSERT
    REFERENCES
    SHOW VIEW
    UPDATE

    Do either of you see a privilege missing that would prevent from creating a table? Is there anything blatantly wrong that you can see with the "Table Creation" portion of the scripting above? Could it be something to do with the version of PHP I have set up in htaccess?

    Thanks again for taking the time to look at this. Any additional ideas you could give would be greatly appreciated!

  • #5
    New Coder
    Join Date
    Nov 2013
    Posts
    12
    Thanks
    6
    Thanked 0 Times in 0 Posts
    I figured it out you guys! Apparently the variable I was using to name the table had a space in it. Table names don't like spaces in them. At any rate thanks for taking the time to look at this. I really appreciate it!

  • #6
    Master Coder felgall's Avatar
    Join Date
    Sep 2005
    Location
    Sydney, Australia
    Posts
    6,640
    Thanks
    0
    Thanked 649 Times in 639 Posts
    Quote Originally Posted by froppo View Post
    I figured I could either create a new table for each new user OR i'd have a table with hundreds of columns of data. i decided having a new table per user would work better for what I'm going for.
    You don't need a separate table to do that - all you need is an extra column as part of the key that identifies the user. That way you still have all the data as separate rows but instead of having one table per user they are all in the one table.

    So create the table once using:

    CREATE TABLE user_data(userid INT, UID VARCHAR(255), User_Avatar VARCHAR(255),
    Page_Title VARCHAR(255), User_Info VARCHAR(255), User_Likes VARCHAR(255), User_Category VARCHAR(255), User_Time VARCHAR(255)
    PRIMARY KEY (userid, UID))


    You also need to read up on database normalization. Adding lots of data should never result in mneeding to add hundreds of columns. At most it adds an extra column to the primary key so that you can insert hundreds of rows for the data in the same table.
    Last edited by felgall; 12-18-2013 at 05:52 AM.
    Stephen
    Learn Modern JavaScript - http://javascriptexample.net/
    Helping others to solve their computer problem at http://www.felgall.com/

    Don't forget to start your JavaScript code with "use strict"; which makes it easier to find errors in your code.


  •  

    Tags for this Thread

    Posting Permissions

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