PHP Automagic

May 12, 2007 1:18 PM

How do I put html form field values into a MySQL database and create the field if it doesn't exist, without declaring or manually updating every single variable?

Say I have a large form with over 100 fields. If I name the form fields and the SQL fields exactly the same, can I write a script that does this automatically, without needing to know the field names in advance? Every tutorial I've looked at would require me to painstakingly declare (globals are off) and then insert the values one at a time. I know this can't possibly be the way it's done in production work.

Rank beginner to PHP here, I usually have some program or other generate a script then tweak it with the manual close at hand.

posted by IronLizard to Computers & Internet (14 answers total)

Err, this would be a Bad Idea.

Users can submit anything they like - they can modify the form before it is submitted. So a user could submit a form with 100,000 form fields, and then your potential script would dutifully modify your MySQL table to add 100,000 columns... not a good plan.

In addition, you'd be taking user input into your SQL statement, which is also a Bad Idea, for an entirely different reason. Wait till I submit a form with a field named ";DELETE * FROM tablename" , and your script cheerfully wipes out a database table.

So, basically, I'd say you have a sort of conceptual failure here, either in your data model (how your database is laid out) or in how you're planning to handle your form input. Perhaps you can describe more exactly just what you want to do, and someone can then describe the optimum way to do it.
posted by jellicle at 1:42 PM on May 12, 2007

I'm unclear on what you're even trying to do. Are you trying to autocreate MySQL columns based on an extant HTML form (as jellicle suggests) or are you trying to automatically write the form itself based on an extant mySQL structure?
posted by rafter at 2:04 PM on May 12, 2007

That is the way it's done in production work, yes. Globals are off precisely to prevent you from doing something like this.

However, in the interest of answering your question, you would use import_request_variables to get the fields into an array such as $_REQUEST, then iterate over that array, using the keys as the field names and the values as the field values.

"And now that you know how it's done...don't do it." -- Troy McClure
posted by L. Fitzgerald Sjoberg at 2:33 PM on May 12, 2007

You don't even have to worry about importing variables, just do something like foreach ($_GET as $k => $v) to iterate over each field. Replace with $_POST as appropriate if that's the form method.

As far as creating the fields in the database automatically, that's a really terrible idea to let user controled unchecked data go directly into SQL like that. You *really* shouldn't do this. Not to mention that your DB schema must be horrendously un-normalized under this method.
posted by Rhomboid at 2:38 PM on May 12, 2007

You could automatically build the SQL query using a simple foreach loop and some escaping. See this example code:

<?php

$fieldList = array('field1', 'field2', 'field3' ...etc...)

// begin sql
$sql = "INSERT INTO myTable (";

// add each field name
foreach ($fieldList as $fieldName) {
if (true == isset($_POST[$fieldName])) {
$sql .= "`" . $fieldName . "`, ";
}
}
$sql = substr($sql, -2); # remove last quote

$sql .= ") VALUES (";

// add values
foreach($fieldsList as $fieldName) {
if (true == isset($_POST[$fieldName])) {
$sql .= "'" . sqlEscape($_POST[$fieldName]) . "', ";
}
}
$sql = substr($sql, -2); # remove last quote

// end sql
$sql .= ')';

// execute query
sqlExecute($sql);

?>

NOTE: this code is complete untested, and it's quite likely that it contains syntax errors, bugs, and other problems. But it shows the concept, and it's probably the best way to do it.
posted by jayden at 2:45 PM on May 12, 2007

Yeah, I don't think he wants the mysql table dynamically changed. That'd be a touch deranged, heh. I think he just wants a way to iterate over all the fields.

What I occasionally do is have an array of the input names... vaguely like so, but neater:

$fields = array('FirstName', 'LastName', 'Location');

$id = (int) $_POST['id'];
foreach($fields as $field) {
   $data = $_POST[$field];
   escape_and_filter($data);
   your_sql_function("UPDATE table SET $field = '$data' WHERE ID = '$id');
}

Really, don't iterate indiscrimantely through all the $_POST vars unless you really must. It's bad practice, for security reasons.
posted by Firas at 2:52 PM on May 12, 2007

Response by poster: Well, the autocreation of fields would only apply to the first use, after which I could comment it out. Or even put it in a separate script, though sticking it in the insert loop would be easier. The reason I'm interested in doing this is simply because I have a big html form (actually 3, which is why abstracting it would be wonderful) already in place and thought it might save my slow-typing self a few hours. The current form method doesn't use a database, it uses internal e-mail.
All data will be regexed for anything resembling SQL, punctuations or code of any sort, really. I'd assumed that was a given, even for someone of my limited experience. Also, referer checks for the each of the scripts couldn't hurt.
Though, honestly, this is all just to prevent vandalism as the table doesn't contain any user data or any kind of access privilege scheme, it's just some paper form made electronic and the key field is an auto increment that shouldn't be editable anyway (just in case someone snuck in an UPDATE statement, somehow).
If this ever gets implemented on an actual server there would be two users in the DB. One for the forms (insert privilege only) and another for one admin to read/delete, whose functions/scripts are in a pw protected server directory (these are secure, right? Please tell me these are secure.).

Sorry if I wasn't clear on that and I'm probably over complicating this by trying to make it easier. I'll just extract all the field names from the form and do it the regular way. Thanks!

[NOT DERANGED]
Well, not constantly, anyway
posted by IronLizard at 5:53 PM on May 12, 2007

Best answer: In that case, I don't think you even need to check whether the column already exists... mysql won't let you add a column with a duplicate name. So something like:

foreach($_POST as $field_name) {
$data_type = 'varchar(30)'; // Replace with whatever type you want
$query = "ALTER TABLE yourtablename ADD $field_name $data_type";
mysql_query($query);
}
posted by Firas at 6:11 PM on May 12, 2007

Response by poster: Ahh, sweet! Even I can understand that, thank you!
posted by IronLizard at 7:02 PM on May 12, 2007

Response by poster: OK, I must be missing something here. When I got around to trying it it made fields named after the content of the form fields, not after the names of the fields themselves. ???
posted by IronLizard at 9:48 PM on May 12, 2007

Response by poster: I think I have a fundamentally flawed concept of how POST works.
posted by IronLizard at 9:52 PM on May 12, 2007

Best answer: $_POST and $_GET are associative arrays (key/value pairs), the keys are the field names, the values are their values. If you just do foreach ($_POST as $foo) you are iterating over the values.
posted by Rhomboid at 10:06 PM on May 12, 2007

Best answer: No, my fault. Try foreach($_POST as $field_name => $value)
posted by Firas at 10:07 PM on May 12, 2007

Response by poster: Ok, it worked, thanks!
posted by IronLizard at 11:23 PM on May 12, 2007