So i was wondering is there any possibility to have one form input and add.php that can track the numbers of fields in any tables we created without manually changed the field in the add.php
I love this line of thinking. :-) There's **probably** an implementation for it in the Zend framework, but I do this in Perl and PHP for any system that will need to expand and evolve without having to recode it. But in my approach it requires a bit of preparation.
Your database has a joined table for data type that corresponds to any table. You can approach this a number of ways, but a decent one is something like this:
customers
id|active|fname|lname|email|comments (etc.)
data_types
|id|table|fieldname|field_alias|form_type|sequence
One might say you can just extract the mySQL data type (varchar, boolean, text, etc.) and use that as a trigger for what form field to generate, but there are cases where that's not enough info. A boolean field can be for a set of radio buttons OR a checkbox, for example.
The second thought is an extra table is a bit of overhead, why not just include the data type field in the customers table? The data table is only queried when you need a form to modify records. The actual data itself may be used publicly and more frequently, it's to your advantage to keep it lean and mean.
So to generate your form you have
- Via a session variable or some other mechanism, determine which table you're inserting or updating.
- A function to get field names of the table. For utmost security, it's a **really good idea** to not use the actual field names as input form names, these should be aliased. Solution: another table, or store the alias in the data_types table (which explains "field_alias".)
- A function to get the data types for each field and return an array of strings for each form field. Using the sequence field in data_types, store the form items in the array in the order you want them. Example, the field "active" is a boolean or tinyint(1), it's supposed to be a radio array and at the top of the form, so your function generates a string containing the radio buttons and it is in position 0 of the array.
- Foreach through the array, output the form.
Take it to the next level: you use this same kind of anonymous approach in doing your updates and inserts, no more hard coded select statements. Though the example below is not working code, and demonstrates no variable cleansing (hint: clean $_POST on input so you don't need to do variable naming) it will give you an idea:
$flds=$vals=$query=null;
// Associative array of aliases => actual fields.
$fields = get_field_names($tablename);
//
foreach ($fields as $alias=>$tablefield) {
if (isset($_POST[$alias])) {
$flds .= "$tablefield,";
$vals .= "'" . $_POST[$alias] . "',";
}
}
//
if ($vals and $flds) {
$flds = preg_replace('/,$/','',$flds); // or other chop
$vals = preg_replace('/,$/','',$vals);
$query = "insert into $tablename ($flds) values($vals)";
mysql_query($query);
}
There are many challenges to this approach, but they're all workable; for example, what if the radio is not boolean, it has more than two possible multiple values, and which one is checked by default? It may seem like more trouble than it's worth, but down the road when you add a new field or a new table, it becomes a very sound investment.
