creating a dynamic php insert into mysql function?

Question

i have a post with a LOT of variables, and I was wondering if there was some way of inserting the information into mysql dynamically, rather than typing it all out manually, as the post variables change depending on what the user selects.

jasonbar · Accepted Answer · 2010-02-24 02:15:21Z

4

This is what we use to do a similar thing (inserting into a table we have no control over, and which has no API access)

Using the DESCRIBE query ensures only columns that exist are inserted.

$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) . ')';

edit: DB() is our wrapper around MDB2.

And of course this allows them to fill in the entire row. If you have restricted columns (auto-increment ids and such), you'll have to filter those out of the form data...

answered Feb 24, 2010 at 2:15

jasonbar

13.5k4 gold badges41 silver badges47 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

jasonbar Over a year ago

And of course the obligatory "it's best to not automatically generate sql."

Collectives™ on Stack Overflow

creating a dynamic php insert into mysql function?

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related