1. Advertising
    y u no do it?

    Advertising (learn more)

    Advertise virtually anything here, with CPM banner ads, CPM email ads and CPC contextual links. You can target relevant areas of the site and show ads based on geographical location of the user if you wish.

    Starts at just $1 per CPM or $0.10 per CPC.
  2. Better Analytics for WordPress Get It Free

Creating a user specified number of tables.

Discussion in 'PHP' started by blueparukia, Nov 2, 2007.

  1. #1
    What I want to do is specify a number in a form, and it creates that many tables in a database. So at the start $counter is set to 0, and $limit is the number from the Form, the first table is called 1, the second one is called 2 etc...


    Using while ($counter <= $limit) doesn't seem to work, and even if I substitute $limit with a constant number, it just creates the one table that many number of times.


    I need serious help on this,


    Thanks,

    BP
     
    blueparukia, Nov 2, 2007 IP
  2. tamen

    tamen Peon

    Messages:
    182
    Likes Received:
    4
    Best Answers:
    0
    Trophy Points:
    0
    #2
    Give us more code to work with, and we will be able to help you much better.
     
    tamen, Nov 2, 2007 IP
  3. exodus

    exodus Well-Known Member

    Messages:
    1,902
    Likes Received:
    35
    Best Answers:
    0
    Trophy Points:
    165
    #3
    Post the code that you have thus far. It would help us debug the script for you.

    I think maybe it has a bug in your insert statement instead of the while statement (maybe).. Oh Oh, are you $counter++; the counter? Cause maybe it is just stuck at 0 all the time and inserting into the same record.

    Without the code snippet we can't really help you.
     
    exodus, Nov 2, 2007 IP
  4. blueparukia

    blueparukia Well-Known Member

    Messages:
    1,548
    Likes Received:
    66
    Best Answers:
    7
    Trophy Points:
    110
    #4
    OK, my code:
    
    <?php
    $counter = 0;
    $limit = $_POST['rooms'];
    $name = $_POST['name'];
    $table = "CREATE TABLE guestdata (
     room INT NOT NULL AUTO_INCREMENT,
     PRIMARY KEY(room),
     filler VARCHAR(30),
     firstname VARCHAR(30), 
     surname VARCHAR(30),
     roomkey INT)";
    
    
    function connect(){
    mysql_connect('localhost', 'pma', '');
    mysql_select_db('hotel') or die('Database "hotel" could not be found'); 
    }
    
    function createtable(){
    connect();
    while ($counter <= $limit) {
    mysql_query($table);
    $counter = $counter + 1;
    	}
    }
                
    if (isset($_POST['install'])){
    if ($name == ""){
    echo "You must enter a name. Go <a href='index.php'>back</a>";
    }
    else {
     createtable(); 
    };
    }else{
    echo "Please use index.php to navigate to this file";
    };
    
    PHP:
    I want to do something like CREATE TABLE ".$counter." for $table but that screws up the SQL syntax.


    Cheers,

    BP

    Thanks,

    BP
     
    blueparukia, Nov 3, 2007 IP
  5. exodus

    exodus Well-Known Member

    Messages:
    1,902
    Likes Received:
    35
    Best Answers:
    0
    Trophy Points:
    165
    #5
    In this case you do not understand sql properly.

    table is the data structure and the entry's are the data. What I think you should do is create a table that will hold a number of entry's.

    The way have it. it is trying to create the same table over and over again. Which it will not be able to do.

    http://www.w3schools.com/php/php_mysql_insert.asp

    The above link test you what mysql insert does.
     
    exodus, Nov 3, 2007 IP
  6. blueparukia

    blueparukia Well-Known Member

    Messages:
    1,548
    Likes Received:
    66
    Best Answers:
    7
    Trophy Points:
    110
    #6
    I want to create tables, not rows.

    So if you type in 10 into the form box, it will create ten tables called:

    1,2,3,4,5,6,7,8,9 and 10.

    As you say, all that code is doing is creating the same table over and over again,

    Thanks,

    BP
     
    blueparukia, Nov 3, 2007 IP
  7. exodus

    exodus Well-Known Member

    Messages:
    1,902
    Likes Received:
    35
    Best Answers:
    0
    Trophy Points:
    165
    #7
    Why you do you want it to create 10 tables that have the same data structure, but only thing that is different is the table name. If you explain a bit more about what your plans are for this then maybe I can instruct you on a better way to do your sql table structure for what you want to do.

    $table would have to be passed to the create table function either in the () or by use of a global define. Personally, I do not understand why you are doing this, but if you want to do it this way then here is the code to do it your way. At least with what code you gave me to work with. You need to pass the $limit to the function to. Also, change the while into a for statement. A bit easier to work with.

    <?php
      $counter = 0;
      $limit = $_POST['rooms'];
      $name = $_POST['name'];
      
      function connect()
      {
          mysql_connect('localhost', 'pma', '');
          mysql_select_db('hotel') or die('Database "hotel" could not be found');
      }
      
      function createtable()
      {
          $limit = 5;
          connect();
          while ($counter <= $limit)
          {
              mysql_query("CREATE TABLE ".$counter." (room INT NOT NULL AUTO_INCREMENT, PRIMARY KEY(room), filler VARCHAR(30), firstname VARCHAR(30), surname VARCHAR(30), roomkey INT)");
              $counter++;
          }
      }
      
      if (isset($_POST['install']))
      {
          if ($name == "")
          {
              echo "You must enter a name. Go <a href='index.php'>back</a>";
          }
          else
          {
              createtable();
          }
      }
      else
      {
          echo "Please use index.php to navigate to this file";
      }
    ?>
    PHP:
     
    exodus, Nov 4, 2007 IP
  8. blueparukia

    blueparukia Well-Known Member

    Messages:
    1,548
    Likes Received:
    66
    Best Answers:
    7
    Trophy Points:
    110
    #8
    
    <?php
    if(that works){
    $blueparukia ="Very Happy";
    $exodusrep = $exodusrep + 1;
    }
    echo "Thank you";
    ?>
    PHP:
     
    blueparukia, Nov 4, 2007 IP
  9. Krownet

    Krownet Peon

    Messages:
    42
    Likes Received:
    2
    Best Answers:
    0
    Trophy Points:
    0
    #9
    Not sure what the point of this is, and I'm pretty sure that any DBAs which are reading this are freaking out about now, but this is what you would want to do:

    Some notes about the code first. It will create a table called "guest" + the counter. It will only work the first time. So if user A enters in 5 it will create 5 tables. User B enters in 6, it will create only table 6.

    What you could do in that case is create the table name based off of $name (replace "guest" with $name, and then it would be $name + $counter).

    
    <?php
    $counter = 0;
    $limit = $_POST['rooms'];
    $name = $_POST['name'];
    $table = "CREATE TABLE table_name (
     room INT NOT NULL AUTO_INCREMENT,
     PRIMARY KEY(room),
     filler VARCHAR(30),
     firstname VARCHAR(30), 
     surname VARCHAR(30),
     roomkey INT)";
    
    
    function connect(){
    mysql_connect('localhost', 'pma', '');
    mysql_select_db('hotel') or die('Database "hotel" could not be found'); 
    }
    
    function createtable(){
    connect();
    while ($counter <= $limit) {
    mysql_query(str_replace("table_name","guest" . $counter, $table);
    $counter = $counter + 1;
        }
    }
                
    if (isset($_POST['install'])){
    if ($name == ""){
    echo "You must enter a name. Go <a href='index.php'>back</a>";
    }
    else {
     createtable(); 
    }
    }else{
    echo "Please use index.php to navigate to this file";
    }
    
    PHP:
    Some words of advise. There isn't really an application that I can see which would have the creating of tables based on user input. An application which creates tables dynamically, is one that needs to have more thought in the Database Design. It just doesn't follow good design. If it is an install application, it would be better to have the SQL in an SQL file, or hard-coded into the script.

    Better ways for table creation, would be to create a table, add a "User" column. Create a second table which has rows of users. All data in this table ("guestdata") would be associated with a rows in the users table. This is considerably more normalized and will stop the DBAs from having a heart attack. Also, if you start taking website guests input on creation of tables, you open yourself up to lots of other issues, like SQL injection.

    Then again, you may have some specific reason for doing this, in which case its not my place to comment. At any rate, this code would work for you.

    Cheers,
     
    Krownet, Nov 4, 2007 IP
  10. blueparukia

    blueparukia Well-Known Member

    Messages:
    1,548
    Likes Received:
    66
    Best Answers:
    7
    Trophy Points:
    110
    #10
    Yeah, I do have a specific reason, though there are probably better ways of doing it. It is my first piece of work which heavily involves daatabases, and is the only thing I don't really get. I was talking to an experienced PHP/Java programmer who does a lot of DB work, especially with PgSQL. He loathed the code I was using, but yeah, he knows I'm doing it for and agrees its the best way to do it - though that is not the exact SQL I will be using,


    Thanks all,
    BP
     
    blueparukia, Nov 4, 2007 IP
  11. Synch

    Synch Peon

    Messages:
    76
    Likes Received:
    7
    Best Answers:
    0
    Trophy Points:
    0
    #11
    In my own opinion, it looks like you could create another column in your table that you are planning on creating with a foreign key to a hotels table. With that in place you could create a new hotel in the hotel tables and insert the rows into one table with the hotel key. If each table is going to be huge (millions of rows each) then you are doing it the right way because it would be a cheap way of partitioning your data.

    you would have:

    CREATE TABLE guestdata
    ( room INT NOT NULL AUTO_INCREMENT, PRIMARY KEY(room), filler VARCHAR(30), firstname VARCHAR(30), surname VARCHAR(30), roomkey INT, hotelkey, int);

    CREATE TABLE hotels
    ( hotelkey INT NOT NULL AUTO_INCREMENT, PRIMARY KEY(hotelkey), hotelname VARCHAR(30), hotelindex int);

    Now when you have a new hotel, it creates a record, and if they need more than 1 instance, incremement hotelindex.
     
    Synch, Nov 4, 2007 IP
  12. blueparukia

    blueparukia Well-Known Member

    Messages:
    1,548
    Likes Received:
    66
    Best Answers:
    7
    Trophy Points:
    110
    #12
    What I actually want to do is for the user to input how many rooms are in their hotel, and create a tablefor each room. As I said, that is not the exact SQL I will be using, but that said, exodus's script isn't working, it connects, there are no errors, but no tables are created :(

    Cheers,
    BP
     
    blueparukia, Nov 5, 2007 IP