Testing – Website Database Access

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:

http://home-hub/hellodb.php

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.

Leave a Reply

Your email address will not be published. Required fields are marked *