SQL script: Create a table from a form

At design time, you may need to redefine the table, modify columns, and correlate the form to enter data and create rows for that table.

Why not create a table directly from the form? This would divide the problem by 2.

This scenario is appropriate in the following cases:

• The table contains many columns.
• Most have the same data type.
• Its structure can change frequently during the developmental phase .

Principle

The script will have the following components:

• HTML page with form.
• Configuration file with database access codes (not provided, it depends on your application).
• PHP functions for accessing the database.
• PHP function for creating a table.

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

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

The form input element name attributes will contain names that will also be column names.

At design time, the form's "action" attribute invokes a creation script. Subsequently, the script name is replaced by the script that processes the user input.

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

will become:

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

Example

A simple form for entering a nickname and email address.

Name:

Email:

HTML-code

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

SQL table to create

The table is called userlist or prefixe_userlist .

name	enamel

JavaScript code

Determines the width of columns using the maximum number of characters in text input 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 assigned to maxlength (maxLength in JavaScript) is passed to the value attribute to pass along with the form data.

For simplicity, it is assumed that there is only one form on the page.

PHP code

The script must view the DOM to retrieve all form objects, build a list from which it will build the SQL command to create the table.

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

$hnd = mysqli_connect(DB_HOST, DB_USER, DB_PASSWORD, DB_NAME)
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, ", mysql_error(), "<br>";
}
?>

This is the base code that needs to be adapted if necessary. The type of columns can be changed, qualities can be added.

Download full code

• Form2sql.