Case-sensitive compares in MySQL

Sometimes you want to do a case-sensitive compare even though the collation on the column is case-insensitive.

The solution is to use a binary collation, like this:

SELECT country FROM purchases
WHERE country COLLATE utf8_bin = 'us';

However, be aware that this also affects the handling of accents, so that "e" and "ë" will not compare as equal.

PEAR on Windows reports "Invalid configuration directive"

Problem: After running go-pear.bat to configure PEAR on Windows you may encounter the PHP error dialog "Invalid configuration directive."
Cause: This problem happens when running modern versions of PEAR on PHP 5.2 or earlier because the quoting handling of PHP 5.2 is broken.

Solution:   To correct the issue, modify the file C:\Program Files (x86)\PHP\pear.bat. Scroll down to the :RUN section and modify the line with the PHP directive "-d include_path" as follows:

... -d "include_path=\".;%PHP_PEAR_INSTALL_DIR%\""
Save the file and re-run the "pear" command.

Find Your Application Root in PHP - Part 2

This is Part 2 of 2. Part 1 describes less sophisticated solutions and this part describes the preferred technique.

This article describes the different ways a PHP application can define its application root directory. This is important because the application root directory is usually used as the jumping off point to find all other directories, such as include files and add-ons. The goal is to put something that looks like this at the front of each of our PHP files:

define('APP_ROOT', ???);

The question is what replaces the question marks.

There are two general methods of finding the application root directory. You can either calculate it or you can define it. If you look in the PHP manual page for include(), you will see numerous ways people have tried to solve this problem, all of which are based on these two methods. Part 1 of this article talks about ways to calculate the application root, but these techniques are not as robust as simply defining the application root.

The requirements for my solution were:

• Cross-platform (LAMP, WAMP and Windows/IIS)
• Maintainable
• Scalable for growing applications
• Deployable with source control
• Works with a subdirectory
• Fast

This article is primarily targeted at PHP 5, up to and including PHP 5.3. This article emphasizes breadth over depth – there are endless nuances to variations of PHP version/web server/operating system/server permissions that this article does not try to address.

The safest, easiest to maintain, and most portable choice, is to use a predefined constant that points at the application’s root directory. This technique can also be generalized into multiple constants, such as one for the application root, one for include files, one for add-ons, etc. This allows you to easily reorganize directories in the future.

There is no One True Way of cleanly defining a constant that works across all environments. However, I’ve found this to be an advantage because I can check all of the different configuration files into source control and each environment will pick up a value corresponding to the configuration file that it uses.

You can define a constant using:

• A hardcoded PHP define() value
• An ini value, read using get_ini.
• An environment variable, read using getenv.

There are numerous places to make these declarations:

• Configuration file
• auto_prepend_file
• Front Controller Pattern
• Php command line
• Php.ini Custom Variable
• Httpd.conf
• .htaccess
• Windows IIS FastCGI Environment
• Per Directory Values
• autoload

Let’s go through each one.

Configuration File

The simplest way to declare the application root is to use the PHP define command, like this:

define('APP_ROOT', '/var/www/htdocs/');

It’s fast to implement and easy to understand. The problem is where to put it. You don't want to copy it to every file in your project because that would be a maintenance nightmare. The logical next step is to put the hardcoded value in a configuration file and include that from all other files. For example, you might have a configuration.php file that contains this:

define('APP_ROOT', '/var/www/htdocs/');

The problem is that you don’t know where configuration.php lives, which means you need to know the APP_ROOT to find it, which gets you back where you started.

auto_prepend_file

This is a variation of the previous section that depends on a the "auto_prepend_file" declaration in PHP 5.3. This declaration goes in Php.ini and allows you to automatically prepend a particular file to all top-level scripts. php files using auto_prepend_file. This solves the problem of where the configuration file lives because each particular script will have that file prepended automatically without having any knowledge of the source of the prepended script.

However, since the declaration must be placed in the php.ini file, this strategy is not feasible in many shared hosting environments.

Front Controller Pattern

Another variation on the "Configuration File" strategy is to use the Front Controller Pattern. This strategy uses .htaccess and mod_rewrite or IIS Rewrite to force all requests to all pages go to be processed by the same script, such as index.php file in the application root directory.

Since all of your page requests are routed through this file, your declaration can go at the front of this file. The Zend Framework uses this strategy with the MVC Front Controller Pattern.

Php Command Line

When you are using PHP-CLI, you can define an ini value from the php command line, like this:

php -d "myblog_app_root=/var/www/htdocs/myapp/" -d "myblog_inc_dir =/var/www/htdocs/include/"

You would then include these statements at the front of every top-level php file:

define("APP_ROOT", get_ini("myblog_app_root"));

define("INC_DIR", get_ini("myblog_inc_dir"));

Now you can include a file like this:

include INC_DIR . 'coolclass.php';

Php.ini Custom Variable

[Note: A huge amount of information about php.ini can be found in Custom PHP.ini tips and tricks.]

The php.ini file is where php defines its configuration parameters. The problem is that it’s only read when PHP starts. For PHP-CLI or PHP with CGI, that’s not a problem. For mod_php, that means that you have to restart the web server to reread your php.ini, which is often not possible in a shared hosting environment.

The other downside to placing the constants in php.ini is that the definitions cannot be isolated. In other words, your application-specific settings are intermingled with system global declarations, so it’s very difficult to keep a single file in source control that can be used across servers.

Setting the application root in your php.ini is done like this:

myblog_app_root = "/var/www/htdocs/myapp/"

myblog_inc_dir = "/var/www/htdocs/include/"

You would then include these statements at the front of every php file:

define("APP_ROOT", get_ini("myblog_app_root"));

define("INC_DIR", get_ini("myblog_inc_dir"));

Now you can include a file like this:

include INC_DIR . 'coolclass.php';

Httpd.conf

Setting a variable in httpd.conf has all of the downsides of Php.ini, plus the additional downside that it doesn’t work with PHP-CLI. Therefore, I wouldn’t use this strategy.

.htaccess

If you are running Apache, .htaccess is the best place to define the constant. For example, this is how the Zend Framework operates. You can make changes to .htaccess in isolation, so it is easy to put in source control, and it doesn’t require a server restart. You can put it in your application root and it will apply to all subdirectories.

The caveats are that:

• Apache must have mod_env loaded.
• You can’t use .htaccess for PHP-CLI or for PHP under CGI.
• Some shared hosting environments disallow the use of SetEnv.
• Doesn't work for Windows IIS.

Setting a custom variable for the application root in your .htaccess is done like this:

SetEnv myblog_app_root /var/www/htdocs/myapp/

SetEnv myblog_inc_dir /var/www/htdocs/include/

You would then include these statements at the front of every php file:

define("APP_ROOT", getenv("myblog_app_root"));

define("INC_DIR", getenv ("myblog_inc_dir"));

Now you can include a file like this:

include INC_DIR . 'coolclass.php';

A shell script

In some shared hosting environments, you may not be allowed to set environment variables from .htaccess or from php.ini. However, shared hosting environments that use FastCGI may allow you to call a shell script, which sets the desired environment variable(s) and then calls php. For details, see the last response in this article on StackOverflow.

Windows IIS FastCGI Environment

If you are using IIS, then you don't have a .htacess file, but there is rough equivalent. IIS uses the file web.config in much the same way as Apache uses .htaccess. Although Microsoft’s documentation says that the web.config file is only for ASP.net, this isn’t true. The web.config controls many aspects of IIS. ASP.net is only part of the web.config functionality.

If PHP is being run using FastCGI, then you can add an environment variable in the IIS Manager as follows: (screen shots can be found at learn.iis.net)

1. Open Internet Information Services (IIS) Manager.
2. Select the host node name.
3. Open FastCGI Settings on the right.
4. Select the desired application (there’s normally only one).
5. Click the … to the right of Environment Variables.
6. Add any desired environment variables.
7. Click OK.
8. Click OK.

The steps above define an environment variable global to all PHP applications, which has the same pitfalls as defining a variable in httpd.conf. However, the options under IIS are somewhat limited.

According to that same article at learn.iis.net, you can also define environment variables in your application’s web.config file as shown in this example, which sets PHPRC. However, I have not tried this myself.

<fastCgi>

<application fullPath="C:\PHP\php-cgi.exe" arguments="-d open_basedir=C:\Websites\Website1">

<environmentVariables>

<environmentVariable name="PHPRC" value="C:\WebSites\website1" />

</environmentVariables>

</application>

</fastCgi>

Per Directory Values

The Windows version of PHP allows you to put values into the registry that modify Php.ini directives on a per-directory basis, very similar to how .htacess works. These values are found in HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\PHP\Per Directory Values. This would be the perfect solution for setting an environment variable, but it doesn't work. That registry key can only be used to set very particular PHP directives.

Autoload

By way of closing, I'll mention that you can use PHP's autoload functionality so that your PHP scripts don't even need to define an APP_ROOT. You use autoload function to automatically load classes as they are needed instead of you trying to remember to include all of the appropriate files. This is particularly helpful for library modules, where you don't want to hardcode a definition, but you still need to depend on other library modules.

There are numerous community examples on the PHP Autoloading Classes page.
If you are using Autoload, then you should define a constant for the full path to the file that implements the functions and include that file at the front of each of your php files.

Also See

How can I get the “application root” of my URL from PHP?
Site Structure: Where to Locate Includes
PHP: Magic contacts
PHP: Runtime Configuration
Description of core php.ini directives
Using FastCGI to Host PHP Applications on IIS 7
Convert .htaccess to web.config on iis

Find Your Application Root in PHP - Part 1

This is Part 1 of 2. Part 2 describes the preferred technique and this part describes less sophisticated solutions.

This two part article catalogs the different ways a PHP application can find its application root directory in the filesystem. This is important because the application root directory is usually used as the reference point to find all other directories, such as include files and add-ons. The goal is to put something that looks like this at the front of each of our PHP files:

define('APP_ROOT', ???);

The question is what replaces the question marks.

There are two general methods of finding the application root directory. You can either calculate it or you can define it. If you look in the PHP manual page for include(), you will see numerous ways people have tried to solve this problem, all of which are based on these two methods.

The requirements for my solution were:

• Cross-environment (LAMP, WAMP, Windows/IIS, and PHP-CLI)
• Maintainable
• Scalable for growing applications
• Deployable with source control
• Works with a subdirectory
• Fast

This article is primarily targeted at PHP 5, up to and including PHP 5.3. This article emphasizes breadth over depth – there are endless nuances to variations of PHP version/web server/operating system/server permissions that this article does not try to address.

Calculating the application root is an alluring solution because it promises zero maintenance. You can change anything, move anything, update anything – and it will still work. However, there’s no way to reliably calculate your application root without making make some compromises.

There are four common methods for calculating the application root in PHP:

• Relative paths
• The file’s directory
• The DOCUMENT_ROOT directory
• A reference file

Relative Paths

The most popular way to calculate the application root directory is using relative paths. With this strategy, each PHP file has a definition at the top of the relative path to the application root. For example:

define('APP_ROOT', './');

This works quite well for a simple application that’s completely contained in a single directory. It’s simple, there’s no maintenance, and it’s cross-platform. For example:

include APP_ROOT . 'coolclass.php';

Notice that I used './' as the APP_ROOT instead of ''. If I used '', then PHP would have searched the include_path before searching the current directory. That syntax is used for including system files, like this:

include 'pear/pearclass.php';

When your application grows to multiple directories, this Relative Paths technique starts showing its flaws. Consider what happens if your parent source file was admin/index.php. Now you need to manually define a different APP_ROOT:

define('APP_ROOT', '../');

The first problem with this solution is that it is relative to the current working directory, which makes it somewhat fragile.

The second problem is that, if you are defining the APP_ROOT relative to the current directory, you constantly have to be aware of what directory you are in and make sure to use the proper path. If a file moves to a new directory, then all of the include paths need to change. While this is certainly doable, it’s maintenance overhead and an accident waiting to happen in a production system.

You’ll see this relative path strategy implemented in different ways. On Island Linux, you’ll see a solution similar to what I’ve shown above. On one StackOverflow question, most of the solutions are based on relative paths, although you have to read the Clarification to determine that. If you look at the PHP manual page for include(), you’ll see a comment by tim at atwoodglass dot com showing a boilerplate file header where you have to define “header_to_root_distance”.

The File’s Directory

A variation on the Relative Paths strategy is a path relative to the current file instead of the current directory. For example:

define('APP_ROOT', realpath(dirname(__FILE__) . '/../'));

This strategy is also helpful for library files that are intended for reuse. You can’t use the current directory because you have no idea who included you and you can’t use APP_ROOT because you don’t know anything about the application. The solution is to include the file relative to the directory of the current file. For example:

include dirname(__FILE__) . 'helperclass.php';

In PHP 5.3 and later, this also works:

include __DIR__ . 'helperclass.php';

Some examples for this problem use $_SERVER['PHP_SELF'], but this refers to the filename of the currently executing script, relative to the document root. This solves a different problem.

The DOCUMENT_ROOT Directory

The DOCUMENT_ROOT directory is wildly popular for developers who have learned not to use relative paths and are looking for the next best thing. For example:

define('APP_ROOT', realpath($_SERVER['DOCUMENT_ROOT']));

There are several people on the PHP manual page for include() who recommend variations on this theme.

There are three key problems with this approach:

• Some web servers don’t support DOCUMENT_ROOT (such as some versions of IIS under Windows.)
• It fails completely if you are using PHP-CLI for unit testing. Since there is no web server, DOCUMENT_ROOT is undefined.
• It only works if your application is in the root directory of the web server. If your application moves to a subdirectory, it breaks.

Therefore, I would say that you could use the DOCUMENT_ROOT as a default or fallback, but it’s not a good choice as a reliable solution.

A Reference File

Searching for the application root is often where developers end up after discovering that calculated solutions have significant limitations. This strategy is most commonly implemented by placing a file with a name like “.approot” in the application root directory.

Advantages to searching for the application root are that it will allow your application to work in a subdirectory and will usually allow your application to work cross platform.

There are two downsides. First, the code to search for that file must be copied to the top of every PHP file that needs to refer to its root. This can add up to quite a lot of code duplication.

Second, this strategy makes it difficult to use the file in a custom environment. For example a unit test framework may want to use a custom APP_ROOT for testing purposes.

Part II

In Part 2 of this article, we'll look at how to define the application root from the environment.

Fast CGI Crash in debugger in PhpED

I use NuSphere's PhpED for my PHP debugging, and I'm constantly having problems with the fact that it has its own PHP installation that conflicts with my PHP installation for IIS. The problem is that both installations, by default, look in C:\Windows for the Php.ini file.

I recently installed PhpED on my new Windows 7 system and started having trouble again with crashes whenever I ran the debugger. The error was:

CGI / FastCGI has encountered a problem and needs to close.

Really, really annoying. Up until now I've worked around this problem with a batch file that sets PHPRC, but there is a simpler way if you are using PHP CLI instead of the built-in Apache server:

1. Go to Tools / Settings
2. Under Run & Debug, click on PHP CLI
3. Under "Additional environment variables", set PHPRC to:
   
   C:\Program Files (x86)\NuSphere\PhpED\php5

If you are using Apache, there's a separate value you can set in httpd.conf, but it will be overwritten with each upgrade of PhpED.