Requirements

Sudo
Nginx
PHP
PHP-FPM
MySQL

Installation

You should make sure all the requirement above are installed before proceeding. The next step is to install the ODBC drivers:

sudo pkg install php71-odbc
sudo pkg install databases/mysql-connector-odbc

The first line installs the ODBC extension for PHP and the second line installs the ODBC driver for MySQL.

Configuration

Because this tutorial involves several different technologies the configuration is not entirely straightforward. First, we will configure Nginx by adding the following (it it doesn’t exist yet):

sudo ee /usr/local/etc/nginx/nginx.conf

http {
    server {
        root   /home/$USERNAME/www;
        location ~ \.php$ {
            try_files $uri =404;
            fastcgi_split_path_info ^(.+\.php)(/.+)$;
            fastcgi_pass unix:/var/run/php-fpm.sock;
            fastcgi_index index.php;
            fastcgi_param SCRIPT_FILENAME $request_filename;
            include fastcgi_params;
        }
    }
}

Do not forget to replace $USERNAME with your username.

WARNING: note that ‘root /home/$USERNAME/www;‘ must also go under the server section (not just under location).

We have to restart Nginx for the configuration changes to take effect:

sudo service nginx restart

You can now serve PHP files from your web root (/home/$USERNAME/www) and subdirectories. Next, we have to instruct PHP-FPM to load the ODBC PHP extension by opening the following file:

sudo ee /usr/local/etc/php-fpm.conf

and add this line at the end of the file:

php_admin_value[extension]=odbc.so

Finally, we have to restart PHP-FPM to reload the settings:

sudo service php-fpm restart

We continue with configuring ODBC. We open the configuration file:

sudo ee /usr/local/etc/odbcinst.ini

and insert

[MySQL]
Driver=/usr/local/lib/libmyodbc5w.so
UsageCount=10

Then we install the driver:

sudo odbcinst -i -d -f /usr/local/etc/odbcinst.ini

As the last step, we have to add a ODBC data source:

sudo ee /usr/local/etc/odbc.ini

where we add the HSD database from the CMPS 160 Databases course.

[HSD]
Driver=MySQL
SERVER=localhost
PORT=3306
DATABASE=HSD    
OPTION=4194304

This concludes the tutorial. Note that you also have to create an HSD-User user for the HSD database with HSD-User+password as password if you want to run the PHP files that come with the book.

Videos

Adding a database user:

The video below shows how we can query the HSD database using PHP. Before you go through this you need to do three things first:

1. Go through the tutorial above. In particular, you have to create an ODBC data source for the HSD database and give permissions to connect.
2. Use FileFilla to copy the file HSD.tar.gz to your www directory.
3. Install unix2dos.

Setup Hosting Environment:

How to Sync your database to your website Video 2: