How to insure column match for MySQL insert, when number and names of input fields will vary?

Question

Hello!

Working on a pre-existing program that parses an html form that has a dynamically created number of fields, and in the interest of forward-compatibility, may not even know number of mysql columns...

I imagine that this requires creating two arrays, and comparing/re-ordering of some sort, but can't quite wrap my head around it...

Would this be something like:

A) Database Array - 1) get # of MySQL columns 2) loop through this number and get MySQL column names

B) Form Array - 1) get # of form fields 2) get form field names/values

C) Match Array - match name.form_field[f] to name.mysql_column[c]

D) Execute Insert - insert value of name.form_field[f] into name.mysql_column[c] (INSERT INTO name.mysql_column[c], name.mysql_column[c+2], name.mysql_column[c+5], name.mysql_column[c+n] VALUES value(name.form_field[f]), value(name.form_field[f+9]), value(name.form_field[f+3]), value(name.form_field[f+x]))

I'm guessing the answer is something like the above, but can't quite picture the nested loops required to achieve the result...

Any possible solutions spring to mind out there?

All responses will be greatly appreciated!

Thanks,

Sean McKernan

McK66 Productions

Answer 1

I'm not sure if this fits your situation exactly, but we use something similar to the following for handling queries with data we aren't sure we have absolute control over. The form input names match the column names so if your's don't, you'll need to come up with the correct mapping.

This retrieves the column data from the table, and then builds the query from the form data using only the elements that match columns that exist in the table.

DB() is our wrapper around MDB2.

$db = new DB();
$sql = 'DESCRIBE `table`';
$result = $db->query($sql);
$row = array();
$query = array();

while ($row = $result->fetchRow())
{
    if (array_key_exists($row['field'], $form_data))
        if (is_null($form_data[$row['field']]))
            $query[$row['field']] = 'NULL';
        else
            $query[$row['field']] = $db->quote($form_data[$row['field']]);
}

$keys = array_keys($query);

foreach ($keys as &$key)
    $key = $db->quoteIdentifier($key);

unset($key);

$vals = array_values($query);

$sql = 'INSERT INTO `table` '
     . '(' . implode(', ', $keys) . ') '
     . 'VALUES(' . implode(', ', $vals) . ')';

Answer 2

This requires a longer answer, but I'll try a halfway decent introduction.

I suggest approaching the problem from the other direction. Ideally, the code would have it's own list of fields to insert and then go looking at the submitted data to construct the relevant rows. That also lets it have default values for 'missing' columns, so it can always generate row data with the correct number of fields.

There is a distressingly high amount of PHP which uses the submitted field names to build the SQL rather than starting with a known list of field names. In my experience, programmers do this because they are in control of the generated form, but forget that they aren't really in control of the submitted form. All it takes is a little editing in FireBug and you can knock the page for six, potentially corrupting your database.