Dynamic insert statements in MySQL

[raghujosh]

Are dynamic insert statements possible in MySQL. I am creating an application that generates a database driven web application with minimal human intervention. Briefly, a user is allowed to build an HTML form(by adding HTML form elements) on a web portal and then he/she submits the form. Once the form is submitted, the metadata of the form is planned to be used to

1. dynamically create a table in the database.
2. dynamically write insert statements to insert values from the form.

I wanted to know if
1. we can dynamically create a table and also generate the corresponding insert statements for the form fields.

[smantscheff]

If you are using PHP, you can use exactly the same DDL statements as from the command line. So, yes, you can dynamically create and alter tables.

[raghujosh]

If you are using PHP, you can use exactly the same DDL statements as from the command line. So, yes, you can dynamically create and alter tables.

well, in my case the program has to create the CREATE and INSERT statements based on the column names of the table. Can I generate the CREATE and INSERT statements on the fly? There will be no reference available from any DDL statements. I cannot copy the DDL statements, but I have to create them on the fly. I really need to know this as the entire project stands on this. What about dynamic SQL?

[smantscheff]

I don't konw if it can be done from MySQL itself, that is, from procedures or triggers. But it definitely can be done with any language (like PHP) which lets you synthesize mysql commands. What is your programming platform?

[raghujosh]

I don't konw if it can be done from MySQL itself, that is, from procedures or triggers. But it definitely can be done with any language (like PHP) which lets you synthesize mysql commands. What is your programming platform?

My programming language is PHP. If possible, can you please share some tips(steps) and sample code to say generate a CREATE statement and/or and INSERT statement on the fly?

[smantscheff]

If you do not know how to create a MySQL insert statement from PHP I suggest that you google for "mysql php primer" and take a course first.
Any command which you enter on the mysql command line you can also put into a PHP variable and feed to mysql - with exactly the same effects. You could for example create a table from a field definition array (not tested):

$fields = array( 
  array( 'id', 'integer', false, 'auto_increment primary key')
, array( 'username', 'varchar(255)', false, NULL)
);
$create = '';
foreach( $fields as $field) {
  $create .= ",$field[0] $field[1]";
  if (!$field[2]) $create .= ' not null ';
  if ($field[3]) $create .= $field[3];
}
$create = 'CREATE TABLE users (' . substr($create, 1) . ')';
mysql_query( $create );

[raghujosh]

If you do not know how to create a MySQL insert statement from PHP I suggest that you google for "mysql php primer" and take a course first.
Any command which you enter on the mysql command line you can also put into a PHP variable and feed to mysql - with exactly the same effects. You could for example create a table from a field definition array (not tested):

$fields = array( 
  array( 'id', 'integer', false, 'auto_increment primary key')
, array( 'username', 'varchar(255)', false, NULL)
);
$create = '';
foreach( $fields as $field) {
  $create .= ",$field[0] $field[1]";
  if (!$field[2]) $create .= ' not null ';
  if ($field[3]) $create .= $field[3];
}
$create = 'CREATE TABLE users (' . substr($create, 1) . ')';
mysql_query( $create );

Thank you very much. I did not know about this before. I did google "mysql php primer", but I did not come across any links that mentioned executing sql statements as PHP. All the links referred to the traditional database connections and traditional SQL statement execution. Can u pls post some links where I can go find more on this? Also, is this the PEAR database module in PHP?

[smantscheff]

If with "traditional SQL statement execution" you mean Data Manipulation Language only, be pleased to find that MySQL supports all Data Definition Language statements through its standard interface, too, and they can arbitrarily be mixed. Which means: anything which you can achieve using the MySQL command line interface you can also achieve using PHP. Therefore I strongly recommend the use of the command line (as opposed to using GUIs which do their job fine but won't let you learn how to do it on a lower level).
My code does not refer to any PEAR modules; it's plain PHP.