Web Development with PHP and MySQL

Using PHP scripting and MySQL database enables programmers to create applications that'll run on just about any computer, regardless of operating system. PHP has thousands of programming functions to facilitate almost any task.

If the computer can run web server software, the PHP / MySQL™ application is portable across operating systems and environments ... PC, Mac, Linux, Unix, Windows, Lindows, Internet, Intranet, etc. This means I can develop a project on my Windows PC, and send it to my friend in Slovakia who can run it on his Linux box.

PHP / MySQL™ is most often used to create dynamic web sites. On this web site, one script drives about 60 pages of content. Additional scripts are used here to process form data ... but that too could be done through the main script using included files.

PHP / MySQL™ projects include forums or communities, organizers, project management tools, calendars, shopping carts, mailing lists, and all sorts of useful applications. Source code for many open source projects is free, while advanced projects often require a registration fee for commercial use.

Data can be exported from MySQL™ for use in spreadsheets or databases on your PC. Similarly, data residing in existing PC spreadsheets and databases can be imported to an online database. The portability of data opens up all sorts of uses, especially for workgroups and for those who need to access data from both home and work.

Tips for using PHP with MySQL

Most web hosts suck. Start your hosting experience with a proven and reliable web host that already hosts millions of domains, offers cPanel®, and regularly upgrades phpMyAdmin. If a web host offers an old version of PHP and an old version of phpMyAdmin they probably don't care if the system runs at peak performance, nor whether users have current software.

Never use a hyphen (-) in the name of a MySQL™ database, table, or column name, even if hyphens are technically allowed. Doing so will crash your project with MySQL errors when when a double quoted PHP string interprets the hyphen as a minus sign.

Generally speaking, name your variables and MySQL™ components using ONLY lower case alphanumeric characters and the underscore (_) character. Variable names and MySQL™ components should begin with a letter, not a number. Upper case table names and field names may not be usable on other systems, depending on their configuration.

$string = str_replace('_', ' ', $string);

Creating MySQL™ column names such as "first_name" instead of "FirstName" will enhance readability and facilitate replacing the underscore character with a space when displaying column names.

Do not use PHP / MySQL™ reserved words as the name of a PHP variable or a MySQL™ database, table, or column. The use of reserved words is likely to cause unexpected results, and cause you (or someone else) to waste a lot of time trying to track down the problem.

Learn to use the define() function in order to create variables that are automatically global in scope (available within user defined functions). Alternatively, consider creating configuration variables as an array which can subsequently be declared as global is scope within individual, user defined functions. Syntax for your configuration variables might use a syntax such as:

$opts = array();
$opts['host'] = 'localhost';
$opts['user'] = 'someone';

Do not use short PHP tags (<? echo 'hello world'; ?>) because if the scripts are subsequently transferred to a server where short tags are turned off in the php.ini file, the scripts will not run.

Before purchasing PHP projects, request code samples from the project. You may decide not to consummate the purchase if short tags are present, or if the coding style does not conform to good practices such as PEAR Coding Standard, or if the variables code are often double-quoted. Such findings may indicate the developer is not highly skilled. Buyer beware.

Saving PHP scripts or "include files" using the filename extension ".inc" will result in the server displaying that file as if it were a text file (if requested directly by the web browser). Consequences can be significant if you were to expose your database logon inside something like "db_logon.inc".

Consider placing all your "include files" above the root directory of the web site, or in a sub-directory that has been password protected. If you are not in control of the server, and if your "include files" are above the root folder, and if your web host changes the path to root during a system upgrade, the "include files" will not be at the expected location (your project will crash). Most web hosts provide a control panel with a Password Protection utility that makes it easy to apply password protection to a sub-directory.

Sessions are impacted by the PHP version as well as the register_globals setting in PHP.INI. If you find that a project using sessions behaves differently on different servers, investigate the impact of the register_globals setting. Security is enhanced when register_globals is OFF.

Whenever possible, use the UTF8 character set with MySQL in order to support the widest range of character sets from various languages. Usage of UTF8 should include the appropriate HTML META tag in your documents, plus extra queries following the MySQL connection.

<meta http-equiv="charset" content="UTF-8">
<meta http-equiv="content-type" content="text/html; charset=UTF-8">

mysql_query('SET NAMES UTF8');
mysql_query('SET COLLATION_CONNECTION=UTF8_GENERAL_CI');

If you're considering buying a book on PHP or MySQL online, you might be able to preview portions of the book in advance by using Google™ Books. Personally, I prefer PHP Cookbook which can sometimes be purchased from Amazon.com as a discounted Used Book.

Read up on MySQL functions, especially the string and date functions. If MySQL provides a function that alters data during retrieval, you'll save yourself a lot of brain damage to use the function in a query (instead of retrieving the data and then having to modify the data using PHP). Be aware there are a lot of useful MySQL functions that can reduce the amount of required PHP code.

If you are genius in other programming languages, then you might want to begin your PHP journey by studying PEAR.

Turning your Windows box into a WAMP PC

Don't install the latest versions of Apache and PHP on 64-bit Windows 7. They'll appear to work on 64-bit Windows 7 but won't play well together. Use Apache 2.0x not 2.2. Pick a PHP version which is about a year old. This can save you about 3 days of swearing. Presumably the same is true for 64-bit Vista. Obscure bugs can exist in PHP's .DLL files for the latest versions of PHP which are run largely under Linux. It takes a long time for Windows related bugs to crop up, be reported, and be fixed.

Installing PHP and MySQL on a Windows PC is less intimidating than it was a few years ago. You'll learn more about PHP and MySQL if you install the applications individually after visiting the Downloads page at both PHP.net scripting and MySQL.com. Due to differences between Linux and Windows file systems, you may sometimes need your scripts to respond differently based on where they're being run (PC vs. web server). Consider setting a unique variable in your PC's Apache httpd.conf file, possibly something like 'SetEnv WAMP 1' which can later be evaluated in a PHP script with:

if(isset($_SERVER['WAMP'])){
   // running on pc
}

With Apache running on a Windows PC, there may be times when it would be helpful for the script to automatically determine whether it is being run from the WAMP development environment or production LAMP environment. I encounter this often when a directory path on the web server is different from the directory structure on the PC. Consider editing the Apache httpd.conf file on the WAMP PC, adding a unique environmental variable that is unlikely to be exist on any other server. Subsequently PHP's $_SERVER variables can easily be checked.

In my PC's Apache httpd.conf file, I added: SetEnv WAMP 1 and subsequently in PHP scripts I can determine the runtime location with: if(isset($_SERVER['WAMP'])){ // }

If your web host allows you to alter the PHP configuration using the .htaccsss file, you can set your timezone in your .htaccess with something like SetEnv TZ America/Denver

If your web host allows you to alter the PHP configuration using the .htaccsss file, you can add custom variables to the $_SERVER array, for example SetEnv MY_DATABASE dbname would populate the PHP variable $_SERVER['MY_DATABASE']

MySQL doesn't really have an upgrade path on Windows PC's. Export your data using a MySQL utility prior to installing more recent MySQL versions.

Uniform Server ... WAMP hard drive or WAMP thumb drive! Amazing!

Uniform Server is free, portable, open source WAMP package for Microsoft Windows (Apache, MySQL, PHP, Perl, phpMyAdmin, etc.) which can be installed on a USB drive, a hard drive, smartmedia card, etc. Uniform Server has an installer and it works great! No muss, no fuss. I migrated a content management system to a portable USB Flash Drive in 10 minutes ... the only scripts that I needed to change were a few PHP scripts that expected a document root of C:/apache/htdocs instead of Uniform Server's document root of W:/www. If you already have Apache and MySQL running as a service under Windows, simply shut down those services before (for example) firing up Uniform Server on a USB stick.

MySQL™ / PHP Form Generator

PHP Form Generator will create one form for each table found in the currently configured MySQL™ database in just a few seconds. Uses phpMyEdit and requires MyISAM type tables. Most users find it pays for itself in a few minutes, currently priced at $39 US.

• PHP Form Generator for phpMyEdit
• php-form-generator.com home

Additional information can be found on the PHP / MySQL Resources page.