The website database access will be performed by a shared script, hub_connect.php, in the /var/www/private_html directory. This script has login details, so is placed in a new directory outside of the publicly accessible directory tree.
sudo mkdir /var/www/private_html
give pi ownership over public and private directories…
sudo chown -R pi:pi /var/www/
create file…
cd /var/www/private_html nano hub_connect.php
<?php
//require the PEAR::MDB2 classes.
require_once 'MDB2.php';
//Makes resultsets into column-name-addressable dictionaries
define("DICTCURSOR", MDB2_FETCHMODE_ASSOC);
//Define some constants
$db_engine = "pgsql";
$db_user = "postgres";
$db_pass = "raspberry";
$db_host = "localhost:5432";
$db_name = "hub";
//Assemble datasource name
$datasource = $db_engine.'://'.$db_user.':'.$db_pass.'@'.$db_host.'/'.$db_name;
//Define connection options
$options = array(
'debug' => 2,
'result_buffering' => true,
'portability' => MDB2_PORTABILITY_NONE
);
$db_object = MDB2::connect($datasource, $options);
if (PEAR::isError($db_object)) {
die($db_object->getMessage());
}
?>
Now we can test the database access from a PHP script, but first we need to enable Display Errors .
sudo nano /etc/php5/apache2/php.ini
Find the line display_errors = Off and change to display_errors = On. Then restart the apache web server…
sudo service apache2 restart
Place the following script in the /var/www/html directory.
<?php
//
// hellodb.php script to show PostgreSQL and PHP working together
//
// Required scripts
require_once '../private_html/hub_connect.php';
$qry = "SELECT * FROM \"Zone\"";
$result = $db_object->query($qry);
if (MDB2::isError($result)) {
die($result->getMessage());
}//end db error
echo "Show me the zones:<br />";
while ($row = $result->fetchRow(DICTCURSOR)) {
print $row["ZoneID"].' '.$row["ZoneName"].'<br />';
}//end while row
?>
Open it with your browser:
You should see the following output:
Any PHP errors will be logged in the following location:
/var/log/apache2/error.log
This completes the setup of the core hub components. In the next post we will consider the plan for the remaining parts of the project.