Generating an SQL table directly from a form

During the development phase it may be necessary to redefine a table, change the columns and, correspondingly, change the form used to enter data and create rows for the table.

Why not generate the table directly from the form? This would divide the task by 2.

This script should be useful in the following cases:

• Your table contains many columns.
• Most have the same type of data.
• Its structure may change frequently during the development phase.

Principle

The script has the following components:

• An HTML page containing a form.
• A configuration file with the access codes to the base (not supplied, it depends on your application).
• PHP functions to access the database.
• A PHP function to generate a table.
  

The name of the table will be passed through a hidden input object. The script will add a prefix that might be defined in the configuration file.

<input type="hidden" name="tablename" value="userlist">

The name attributes of all input elements will contain names that are also the names of the columns.

During the development phase the attribute "action" of the form calls the script for creation. Then we replace the name of the script by the script processing data entered by users.

<form name="users" action="form2php.php">

will become:

<form name="users" action="myscript.php"> 

Example

A simple form to enter a username and an email address.

Name:

Email:

The HTML code

<form name="users" method="post" action="form2php.php">
   <input type="hidden" name="tablename" value="userlist">
    <p>Name <input type="text" name="name" maxlength=32></p>
    <p>Email <input type="text" name="email" maxlength=64></p>
   <input type="submit" value="Submit">
</form>

The SQL table to generate

The table name is "userlist" or "prefix_userlist".

name	email

The JavaScript code

It defines the width of columns by taking the maximum number of characters of text entry fields:

function setsizes()
{
  var it = document.getElementsByTagName("input");
  for(var i = 0; i < it.length; i++)
  {
    var element = it.item(i);
    var len = element.maxLength;
    if(len > 0)
    {
      element.value = new String(len);
    }	
  }  	
}  

The maximum length, maxlength (maxLength in JavaScript) is transmitted to the attribute value to be passed with the form data.

For simplicity we assume that there is only one form in the page.

The PHP code

The script must traverse the DOM to retrieve all objects form, make a list from which he constructs a SQL table creation query.

<?php
include("config.php");

$hnd = mysqli_connect(DB_HOST, DB_USER, DB_PASSWORD)
or die("MySQL database '".DB_NAME."' not accessible.<br>");
mysqli_select_db($hnd, DB_NAME)
or die("Enable to select ".DB_NAME." database<br>");

$tablename = $table_prefix.$_POST['tablename'];
$query = "CREATE TABLE $tablename(";
$flag = false;

foreach($_POST as $name => $value)
{
  if($name == "Submit") continue;
  if($name == "tablename") continue;  
  if($flag) $query .= ",";  
  $query .= "$name varchar($value) ";
  if($name == "ID") $name .= " PRIMARY NOT NULL";
  else
  if($name == 'name') $name .= " NOT NULL";  
  $flag = true;
}
$query.=")";

mysqli_query($hnd, "DROP TABLE $tablename");

$x = mysqli_query($hnd, $query);
if($x == false)
{
  echo "Error, ", mysqli_error(), "<br>";
}
?>

It is a basic code that must be adapted as appropriate. The type of columns can vary, qualities may be added.

Download the code

• Form2sql.