Introduction

Defining all accesses to a MYSQL database on a dynamic page can be painful when you have a lot of information fields to manage. This small class is the beginning for a database layer abstraction (DBAL). Using it, you will never have to make a query again, deal with its syntax, know where to put quotes and not, know what the primary keys of the tables are, etc.... In fact, this information already exists in a database called information_schema. If performance of a site is not the main issue and you want it to make your site maintainable before all, then using this information to simplify the life of the programmer can be the key. But know that these kind of things, and by extension every DBAL, Object Relational Mapper (ORM), like Doctrine for instance, or frameworks like Cake or Zend have huge costs in terms of performance, but will ease your job. It's only a question of choice...

Principles

The first idea was about answering one question: "why to I have to deal with a bunch of fields I don't even need when writing a query, if those fields are perfectly identified into the database?". As a programmer, why should I have  to worry about knowing the complete structure of my table to insert a row? Instead of writing:

INSERT INTO MyTable VALUES ('Myvalue', '', '', 0, '', 0, '', '');

Why can't I do something like:
$MyFlied1 = 'MyValue';
$result = doInsert('MyTable');

The procedure should be able to retrieve alone the structure of the table of the specified database and deal alone with the fields and their types.

The second idea was about building a bridge between tables and forms. With identical name, the field of a form should be easily linkable to its homonym on the table and vice-versa. After retrieving the content of a table, it should be easy to fill a form with corresponding fields calling one method, instead of moving each value to each form field. On the contrary, when the form is complete and the data are to be inserted in the table, a method could be called to pass each of the form field value to the table fields.

Coding the Classes

Connecting to a specific database

Getting fields and types from the schema database

Getting primary keys from the schema database

Selection of rows

Key select, returns only one row

Field select with limitation

Insert Method

Update Method

Update the field corresponding to the primary key value but only with know properties.

Delete Method

Erase the field corresponding to the primary key value.

Bulk moving fields from database to Form and vice versa.

Those 2 functions move the value of the fields object to the post values of the form, when names are identical, and vice versa.

The complete Class

Improvements

Of course as you can see, to have a real DBAL application, a lot of work needs to be done. These classes however set  some principles up. We could complete them with:
- Full Select construction functions with COUNT, MAX, MIN functions,
- Join capabilities on several tables,
- Order, distinct, group by, like clauses,
- Inclusion of operators like >, >=, <, <=, NOT,
- Inclusion of Boolean operators AND, OR.

Sample

The sample below was created with the following table:

Inserting one row to the table:

Updating one row to the table:

Retrieving one field from the key:

Check this link out.

History

2008-09-29: First version

2010-08-23: some code reviewing and adaptation