Member Avatar for CarterLangley

Hi guys,

I have a mysql database with many tables. Instead of creating a page each to enter info into the relevant tables, is it possible to have a form created dynamically depending on what table I want to insert information into? Database has 20 tables in it at present, but this will most likely increase in time. So one page to insert info into a form that is dynamically created depending on what table is chosen would be one hell of a timesaver!

  • 5 Contributors
  • 6 Replies
  • 197 Views
  • 15 Hours Discussion Span
  • Latest Post Latest Post by CarterLangley

Dani AI

Generated

A practical, maintainable way to generate a single data-entry page for many tables is to introspect the schema at runtime, map column metadata to appropriate HTML controls, and always use parameterized queries for inserts. This follows the spirit of ’s idea (define form fields from metadata) but avoids manually keeping arrays for every table; it also addresses ’s warning about integrity by keeping rules in the app logic. Tools like ’s PHPMaker or ’s phpMyAdmin are quick alternatives, but the steps below show how to build a simple, safe custom solution.

Use information_schema to get column metadata:

SELECT COLUMN_NAME, DATA_TYPE, COLUMN_TYPE, IS_NULLABLE, COLUMN_DEFAULT, EXTRA
FROM information_schema.columns
WHERE table_schema = DATABASE() AND table_name = :table
ORDER BY ORDINAL_POSITION

With that metadata, build the form and the INSERT using PDO prepared statements. Example pattern (conceptual):

$cols = $pdo->prepare($metaSql);
$cols->execute([':table'=>$table]);
$columns = $cols->fetchAll(PDO::FETCH_ASSOC);

// build form by skipping EXTRA like 'auto_increment', mapping DATA_TYPE -> input type,
// and parsing COLUMN_TYPE for ENUM options
// on submit:
$fields = array_keys($values); // only non-auto fields
$placeholders = array_map(fn($f) => ':'.$f, $fields);
$sql = "INSERT INTO `$table` (`".implode('`,`',$fields)."`) VALUES (".implode(',',$placeholders).")";
$sth = $pdo->prepare($sql);
$sth->execute($values); // $values is assoc array param => value

Practical tips and gotchas:

  • Parse COLUMN_TYPE to get ENUM/SET options and render selects/radios. Use information_schema.key_column_usage to detect foreign keys and populate selects from referenced tables.
  • Map TINYINT(1) to checkbox, TEXT to textarea, DATE/DATETIME to date/time inputs, and skip columns with auto_increment or server-side defaults unless editable.
  • Validate server-side (types, required/null), use CSRF tokens, and always use prepared statements rather than escaping. Sanitize file uploads and store paths, not raw files in SQL.
  • Consider a small metadata table for human labels, help text, and field order if the auto-generated form needs nicer UX.

This approach keeps schema rules source-of-truth in the database, minimizes repetitive code, and is easy to extend as tables grow.

Recommended Answers

All 6 Replies

Member Avatar for urtrivedi

if you can buy, try phpmaker , I think it gives what u want.

Member Avatar for jkon

To be honest a “hell of a timesaver” would be also if you let your visitors alter the system tables by their selves. But you don’t want that, why ? . I suppose because you want to have rules of what user can edit what table, what row and in what data formats in each field.
Yes it is possible; there are frameworks out there that have created only for this purpose. I strongly believe that data structure and integrity must stay always at back of the application. You really don’t have any reason to enlighten your users of the backend.

Member Avatar for CarterLangley

This is to make my own life easier. No one but myself uses the database. And it runs locally on my Ubuntu box. I can only learn if I can get help with coding something instead of buying a solution. So who feels like a challenge?

Member Avatar for broj1

Describe your tables in a way that you can create forms and generate queries. A simple way of doing it would be to have table fields stored in an associative array where keys would be field names and values would be field types. That way you can tell whether to use nput field or textarea in the form.

$table = array(
    'tablename' => 'table1',
    'filed11' => 'varchar',
    'field12' => 'smallint',
    'field13' => 'text',
)


$table2 = array(
    'filed21' => 'integer',
    'field22' => 'smalint',
)

// form for the selected table (i.e.table1)
echo "<form>";
foreach($table1 as $key => $value) {
    if($key == 'tablename') {
        echo "<input type=\"hidden\" value=\"$value\">";
    } else {
        switch($value) {
            case 'varchar':
            case 'smallint':
                echo "<input type=\"text\" name=\"$key\">"; break;
            case 'text':
                echo "<textarea name=\"$key\"></textarea>"; break;
        }
    }
}
echo '<input type="submit" name="submit" value="Insert data">';
echo "</form>";

// this is how you process the form
if(isset($_POST['submit'])) {
    unset($_POST['submit']);
    $tablename = mysqli_real_escape_string($_POST['tablename']);
    unset($_POST['tablename']);

    foreach($_POST as $fieldname => $fieldvalue) {
        $fieldNamesArray[] = mysqli_real_escape_string($fieldname);
        $fieldValuesArray[] = mysqli_real_escape_string($fieldvalue);
    }

    $query = "INSERT INTO $tablename ("`. implode('` ,`', $fieldNamesArray) "`) VALUES (" . implode("','", $fieldValuesArray) . "')";

    // then execute the query
    ...
}

This is just a concept. It can be much improved but you get the idea.

Edited by broj1
Member Avatar for Member #120589

If you want a form for updating data in your tables why not use phpmyadmin or another GUI?

Member Avatar for CarterLangley

Busy using phpmyadmin. Takes longer than doing it through the website though. That code above I can use and probably combine with an idea I have. Will see how I get on over the coming weeks.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.