• PostgreSQL
  • PostgreSQL Maestro
  • PostgreSQL PHP Generator
  • PostgreSQL Data Wizard
  • PostgreSQL Data Sync
  • PostgreSQL Database Converter
  • PostgreSQL Code Factory
• Download
• Purchase

Article: Getting started with PHP script generators

Last modified: Aug 25, 2008

Abstract

Introduction

The reasons that make the subject of using PHP generator so requisite are quite obvious. It's all of human natural desire to save the time, and this is no surprising. It will be much faster to generate the script and complete it with necessary additional scenarios (if only generator hasn't done it for you already), in short to adjust it in the way you need, rather than to write the whole script file from the very beginning. This is also very convenient if you need to publish some information from your database in the web in the shortest possible time, and all the more if you want to edit table data through the browser or give these rights to a remote user. In order to give a helpful solution to an administrator and to a user the SQL Maestro Group team has worked out a tool for generating PHP scripts from database objects.

How does it work?

The sequence of PHP Generator actions consists of a few simple steps which you have to follow for the purpose of preparing data for generation. To start the process, it is necessary to connect to your database (Picture 1). The connection settings depend on which server you are using. Though, the main principles remain the same.

Picture 1. Connection settings.

After the connection is established indicate the tables and/or views and their fields to be represented in the future PHP script. In the following steps you are to adjust the html appearance of the result script and set the security options. Actually this is it. Just push the "Generate" button and the script is ready, waiting to be uploaded on the web server (Picture 2).

Picture 2. Generating a PHP script.

Now let's dwell upon some details.

Appearance

When setting the preliminary adjustments you can indicate which table fields you want to see in the browser and which of them will be editable. If necessary, you can make one or more SELECT queries (Picture 3) in order to get an exact set of fields and rows you need.

Picture 3. Adding queries for script generation.

During the process of preparing data for generation it is possible to indicate how many rows to display at one page. The choice of navigation buttons location is available too. You can also select fonts and color palette for the future page according to your needs (an example is shown on Picture 4

Picture 4. Customizing the appearance of the result script.

Security

The access to the generated PHP script can be restricted by means of authentication. Due to wide choice of security options suggested by the generator the limitations may vary in security level support. You can choose which actions need secure: attempt to access the whole script, detailed viewing, adding, editing or deleting and the way the authentication will be performed. There are three variants of setting password and user name. The first one makes use of password and user name that you have entered to connect to the host. The second allows you to specify custom user name and password (Picture 5). And at last, when using the third one it is possible to select a specific table which stores user names and passwords and indicate the login and password fields. User name/password combination should match one of the value combinations of this table.

Picture 5. Settings for user authentication.

The security options can be really useful when several operators are going to work with the script. If any of the limitations were set, the script will request user name and password when someone will try to produce the restricted action (Picture 6).

Picture 6. The result php script requries authentication.

Linked tables

One of the most convenient features implemented for table manipulations is a so called lookup option. It can be applied in those cases when a table you need has a field with a foreign key constraint. Probably this field will contain numeric values which have their corresponding names in another table referenced with yours.

Lets look through the tables' SQL definitions (MySQL):

CREATE TABLE country (
  country_id    int NOT NULL,
  country_name  varchar(100),
  /* Keys */
  PRIMARY KEY (country_id)
) ENGINE = InnoDB;
 
CREATE TABLE customer (
  customer_id       int NOT NULL,
  customer_name     varchar(100),
  customer_country  int NOT NULL,
  /* Keys */
  PRIMARY KEY (customer_id),
  /* Foreign keys */
  CONSTRAINT fk_customer_country
    FOREIGN KEY (customer_country)
    REFERENCES country(country_id)
) ENGINE = InnoDB;

As appears from the above there is a referential integrity between "Customer" and "Country" tables.

Picture 7. Enable a lookup option.

When using a lookup option it is possible to substitute the values from the referenced table instead of numeric values (Picture 7 shows how you can set this option). During inserting or updating of a row the corresponding edit area will have a popup list containing only correlated values, so it will be impossible to enter incorrect data (Picture 8). Thus you protect yourself against useless errors.

This feature can be used even if there is no referential integrity between tables or even foreign key constraints are not supported by database engine at all (for example when you are working with SQLite database). The only difference is that you will have to specify the referenced table and field manually.

Picture 8. Adding a new record.

Though it is impossible to foresee all requirements, we tried to provide a maximum comfort  for a user to prevent him from PHP script writing routine. As it was mentioned above if something doesn't fit you, it is possible to complete the script later manually. Before we'll finish this article it is worth reminding that Data Wizard PHP Generator goes in one kit with data pump and import/export wizards which also may increase the productivity of your work.

Back to resource list