Creating a user specified number of tables.

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

 

Give us more code to work with, and we will be able to help you much better.

 

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.

 

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

 

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.

 

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

 

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:

 

<?php
if(that works){
$blueparukia ="Very Happy";
$exodusrep = $exodusrep + 1;
}
echo "Thank you";
?>

PHP:

 

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,

 

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

 

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.

 

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