{"id":141,"date":"2017-04-03T21:07:40","date_gmt":"2017-04-03T21:07:40","guid":{"rendered":"https:\/\/test.warrensoft.co.uk\/wp-test\/home-hub\/?p=141"},"modified":"2018-12-08T07:32:18","modified_gmt":"2018-12-08T07:32:18","slug":"testing-website-database-access","status":"publish","type":"post","link":"https:\/\/test.warrensoft.co.uk\/wp-test\/2017\/04\/03\/testing-website-database-access\/","title":{"rendered":"Testing – Website Database Access"},"content":{"rendered":"

The website database access will be performed by a shared script, hub_connect.php,\u00a0in 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.<\/p>\n

sudo mkdir \/var\/www\/private_html<\/pre>\n
give pi ownership over public and private directories…<\/p>\n
sudo chown -R pi:pi \/var\/www\/<\/pre>\n
create file…<\/p>\n
cd \/var\/www\/private_html\r\nnano hub_connect.php\r\n\r\n<\/pre>\n
 <\/p>\n
<?php\r\n\r\n\/\/require the PEAR::MDB2 classes.\r\n\r\nrequire_once 'MDB2.php';\r\n\r\n\/\/Makes resultsets into column-name-addressable dictionaries\r\ndefine(\"DICTCURSOR\", MDB2_FETCHMODE_ASSOC);\r\n\r\n\/\/Define some constants\r\n$db_engine = \"pgsql\";\r\n$db_user = \"postgres\";\r\n$db_pass = \"raspberry\";\r\n$db_host = \"localhost:5432\";\r\n$db_name = \"hub\";\r\n\r\n\/\/Assemble datasource name\r\n$datasource = $db_engine.':\/\/'.$db_user.':'.$db_pass.'@'.$db_host.'\/'.$db_name;\r\n\/\/Define connection options\r\n$options = array(\r\n 'debug' => 2,\r\n 'result_buffering' => true,\r\n 'portability' => MDB2_PORTABILITY_NONE\r\n);\r\n\r\n$db_object = MDB2::connect($datasource, $options);\r\n\r\nif (PEAR::isError($db_object)) {\r\n die($db_object->getMessage());\r\n}\r\n\r\n?><\/pre>\n
Now we can test the database access from a PHP script, but first we need to enable Display Errors .<\/p>\n
sudo nano \/etc\/php5\/apache2\/php.ini<\/pre>\n
Find the line display_errors = Off and change to display_errors = On. Then restart the apache web server…<\/p>\n
sudo service apache2 restart<\/pre>\n
Place the following script in the \/var\/www\/html directory.<\/p>\n
<?php\r\n\r\n \/\/\r\n \/\/ hellodb.php script to show PostgreSQL and PHP working together\r\n \/\/\r\n\r\n \/\/ Required scripts\r\n require_once '..\/private_html\/hub_connect.php';\r\n\r\n $qry = \"SELECT * FROM \\\"Zone\\\"\";\r\n\r\n $result = $db_object->query($qry);\r\n\r\n if (MDB2::isError($result)) {\r\n   die($result->getMessage());\r\n }\/\/end db error\r\n\r\n echo \"Show me the zones:<br \/>\";\r\n while ($row = $result->fetchRow(DICTCURSOR)) {\r\n   print $row[\"ZoneID\"].' '.$row[\"ZoneName\"].'<br \/>';\r\n }\/\/end while row\r\n\r\n?><\/pre>\n
Open it with your browser:<\/p>\n
http:\/\/home-hub\/hellodb.php<\/a><\/p>\n
You should see the following output:<\/p>\n
\"\"<\/p><\/blockquote>\n
Any PHP errors will be logged in the following location:<\/p>\n
\/var\/log\/apache2\/error.log<\/pre>\n
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.<\/p>\n","protected":false},"excerpt":{"rendered":"
The website database access will be performed by a shared script, hub_connect.php,\u00a0in 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 … <\/p>\n
Continue reading “Testing – Website Database Access”<\/span><\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[15],"tags":[],"class_list":["post-141","post","type-post","status-publish","format-standard","hentry","category-hub-software-website"],"_links":{"self":[{"href":"https:\/\/test.warrensoft.co.uk\/wp-test\/wp-json\/wp\/v2\/posts\/141","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/test.warrensoft.co.uk\/wp-test\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/test.warrensoft.co.uk\/wp-test\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/test.warrensoft.co.uk\/wp-test\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/test.warrensoft.co.uk\/wp-test\/wp-json\/wp\/v2\/comments?post=141"}],"version-history":[{"count":17,"href":"https:\/\/test.warrensoft.co.uk\/wp-test\/wp-json\/wp\/v2\/posts\/141\/revisions"}],"predecessor-version":[{"id":450,"href":"https:\/\/test.warrensoft.co.uk\/wp-test\/wp-json\/wp\/v2\/posts\/141\/revisions\/450"}],"wp:attachment":[{"href":"https:\/\/test.warrensoft.co.uk\/wp-test\/wp-json\/wp\/v2\/media?parent=141"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/test.warrensoft.co.uk\/wp-test\/wp-json\/wp\/v2\/categories?post=141"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/test.warrensoft.co.uk\/wp-test\/wp-json\/wp\/v2\/tags?post=141"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}