PC Services

"From Payroll to Body Scanners"

Established 1994

Some PHP Tips and 'Tricks'

Thinking beyond each page and DESIGNING your web site

Tel: 0118 946 3634
Email

Making maintenance and database operation easier with MySQL (or other services).

The main problems with database maintenance, affect - script design, page layout, page updates, and database updates - all start with good database design!

Having seen a few databases, one project made me 'cringe', I inherited the website database for an update and first task of course review the code. I was curious why this database had several web pages as PHP scripts, for doing different types of searches on reading through the code I was confronted with a MySQL search query as -

$query = "SELECT * FROM $tableName WHERE item_type = 'job_20' AND display = 'Yes' ORDER BY title ASC";

Some will think what is wrong with that? Well let us also throw into the mix that various search PHP script files, had the ONLY difference between the files was the term item_type = 'job_20' was the ONLY change, and the field data was hand coded in EACH file. This gives rise to various maintainability problems such as -

• Hand coded field data in each file means any typographic error in any file gives rise to invalid data or invalid reports, e.g. if a flag is set to "yes" or "YES" instead of "Yes", many records are not accessible or in some cases editable or deletable.
• Any style or text changes across the pages has to be edited in more files, raising the probability of typing errors.
• Extra search types requires extra files/web pages.
• Bad structure gave rise to complicated back end scripts, with multiple PHP scripts with minor differences.

Some things to do for easier maintenance

First review you data and data structure.

• For Yes/No flags use BOOLEAN data type, and have a consistent method for encoding the Boolean to Yes or No.
• For product/sevice categories use coded or ENUMerated data types.
• Keep tables/arrays for encoding or enumerated to text strings for web pages for all data fields
• Ensure your keys and auto-increment values are used correctly.
• Normalise all data fields
• Restrict the number of users allowed to update the data
• Write down how items are classified, for consistent data entry and formatting.
• Backend should have a database backup page that allows backups to be done regularly, preferably after any updates, and keep MULTIPLE backups.

In your PHP scripts, some things people fail to do, but should do -

• You use include files, so have a common include file for the database connection details.
• Depending on number of ENUMerated or coded entries, create either an include file with these as arrays to Text strings for display on web pages, or use other tables in your database to store these.
• Create common functions to fill drop down lists, with highlighting current value and reverse lookup to store enumerated value.
• Use common functions to strip or convert to/from HTML coding to avoid database or security issues
• Create search scripts that can enter search details, display results in brief or detailed form as well as redisplay search form with errors (including no records found).
• Validate any data to the script from POST, GET and even the database, BEFORE using the data

Surprisingly, it can be easy to use GET and POST methods to access one search module, for form entry, and results processing. This means the script must be laid out -

1. Include site-wide CODE files.
2. Include database files.
3. Check for POST and/or GET data
4. Validate data and fill data array(s) with defaults or passed in data
5. If search database, perform the search and fill data array(s)
6. Build error message(s) for display later

At this stage the script should have one of the following -

• A blank request to start a search query
• A valid search as a shortform or detailed form list of matching records
• Know what type of display was requested
• Any errors - No records, invalid search, missing data, etc..

The rest of the script now has to do the following -

1. Include XHTML/HTML site-wide files.
2. Display the relevant format of page depending on type of page required, using prefilled data.
3. Display error messages in appropriate positions and styles.

This is not difficult to layout, reduces the number of script files, is easier to maintain as only one file is updated for page layout even if you use templates, less chance of errors.

Obviously you have a back end to update data that should use scripts in the same manner, even for listing, editing/new, or deleting records, which vastly reduces the amount of work you have to do.