Reusable Insert Script - Processes Data From Many Forms

[u023172]

Hopefully I can make myself clear here on what I'm trying to do

I'm looking for a way to have a single insert script thgat inserts records into a mySQL DB, with the ability to insert into different tables - one file inserts all, was what I was hoping :p

So, instead of having many different files that insert records into different ables with different attributes and values, I;m trying to find a way to have ONE insert file, that can handle different insert requests to different tables, just basically to save time and cut back on the number of files in a directory. For the site I;m doing, I have a lot of functionality, mainly with insert queries (e.g - add assignment, add student, add staff, etc etc)
It would just make sense to have one file that could insert into m,any different tables depending on the varibales sent from a previous form.

For exmaple, instead of INSERT INTO tblStudent, a script may read INSERT INTO $table; $table being a value sent from a previous form, like "student".

Hopefully this all makes sense; I just can't work out how to do this, nor do I even know if this is possible!

Haver tried posting this question on other forums, but either people get generally puzzled or I don't understand their solution! :p

Any suggestions would be much appreciated!

[tittat]

*****Thread Moved to Programming Help*****

[misson]

Your description makes sense. If you're concerned about being understood, you could take a page from test driven development or design by contract and give the declaration & behavior of a function that you want do define. I'll show how I'd go through this process and end with the resulting spec.

First a little terminology. The type of abstraction involved (creating a data control statement like a INSERT or SELECT) is part of what's called a "data access layer" (DAL), which isolates data storage from the rest of the script.

Interface design can be a little tricky, but this problem's not too bad. What you're doing is taking a statement in one language (SQL) and turning it into a statement in another language (PHP). Start by considering the statement in SQL, which has two formats that apply here:

INSERT INTO <table> (<field>[, ...]) 
    VALUES (<value>[, ...]);

INSERT INTO <table> SET <field>=<value>[, ...];

Every time you INSERT, the table name, fields and values will vary, so these need to be abstracted out, which means turn them into variables.

Now consider what you're translating to. You could approach this with OOP and make an Insert class, but you'll probably perform at most one insert per table for each page view and there are only 3 variables to encapsulate, so a simple function should suffice. Should you want to expand the project to include other statements (UPDATE would be a natural counterpart to INSERT), a single Statement class might be a cleaner design than multiple methods, but it should be easy to refactor a createInsertStatement function into a Statement::createInsert.

You could have the function create an insert for multiple rows, but (for now) have it work for a single row as many forms don't require creating more than one row.

Another very important feature of a DAL is sanitizing the input, making it safe for other systems (such as the database) that will handle the data. A malicious user can easily craft input to do mean things your database. You could sanitize the data when it's first accessed (i.e. when you first reference $_POST or $_REQUEST), but sanitizing depends on where the data is being sent to more than where it's coming from.

Now let's think a little more about the variables. The table (call it $table) should be defined in the script, not the form, so it doesn't need to be sanitized. The only information we need about the table is its name, so $table can be a string. The fields (stored in $fields) have names and types (which can be used for sanitizing), so an array of name=>type pairs makes sense for $fields. Lastly, form input is already name=>value pairs, so let's leave that as-is. If you don't care about field types, the name=>value pairs includes both the field names and values. This might tempt you to leave out $fields, but remember that the names from the form are user input and just as untrustworthy as the values, thus needing sanitization.

To be really secure and robust, the names of the form inputs should be different from the column names. This follows from the security concept of "information disclosure", which, among other things, means you should disclose only enough information to complete a task. Divorcing form input names from column names also decouples the data model of the form from the data model stored in the database, the advantage of which is a single form input could map to multiple columns, simplifying the user's interaction with the form. To keep it simple, let's leave this out for now and sanitize column names to keep things safe.

The above leads to the following spec:

/*
 *   'createInsert($table, $fields, $data)' creates an SQL INSERT statement given 
 * column specifiers & data.  $data will be sanitized by 'createInsert()'; this 
 * includes limiting the fields of $data to those defined in $fields.
 * 
 * @param  string   $table    The name of a table to insert into.  Not sanitized.
 * @param  array    $fields   Column names & types.  Array of name=>type pairs, 
 *                            where 'name' is the column name and 'type' is the
 *                            type of the field (how types are specified is to be determined later).
 * @param  array    $data     Data to insert; name=>value pairs where 'name' is field
 *                            name.  Will be sanitized.
 * 
 * @return string   An SQL INSERT statement
 * <code>
 * $fields=array('id' => 'int', 'value' => 'str');
 * $query = createInsert('test', $fields, $_REQUEST);
 * </code>
 */

function createInsert($tbl, $fields, $data) {
  // what goes here?
} 

Take a whack at that. Feel free to alter the requirements if you feel something is missing; often you'll find this is the case for the initial design. We'll post our implementations later and compare.

If you need a hint, take a look at the array functions, especially array_intersect_key, array_map and implode. Extra credit for each additional DB driver your implementation supports. This means you might want to use PDO, though it's not currently enabled on the free X10 hosts.