In this task you will:
Create a new file in notepad++ called 'fetchData.php' and create the PHP script tags within which your PHP script will sit. If you like, you can place some basic HTML around the PHP script tags to wrap the PHP output, but this is not necessary.
Using the notes on PHP Database Connection, open a secure connection to the database
instance on the dialogplus.ac.uk server. As this PHP file will sit on the dialogplus server
itself, the host name will be 'localhost', the database name is 'geog5871' and you have been
provided with the database username and password (the same ones we used when connecting to the database with pgAdmin). ]
The entire string needs to be encased in double quotation
marks and each parameter needs to be encased in single quotation marks. Look back at the syntax in the notes
if you are unsure. It is a good idea to include an 'or die' statement at the end of the pg_connect string to
provide further information incase the connection fails, for example:
$pgOptions = "host = 'xxx' dbname = 'xxx' user = 'xxx' password = 'xxx'";
$dbconn = pg_connect($pgOptions) or die ('connection failure');
Create a new variable to store an SQL query and assign this to one of the SQL queries you defined in Task 2 to query the tweets table. For example:
$query = "SELECT * FROM tweets";
Use the pg_query() function to query the geog5871 database, again it may be a good idea to
include an 'or die' statement. pg_last_error() will provide further information if the query
should fail.
$result = pg_query($dbconn, $query) or die
('Query failed: '.pg_last_error());
Print each row from the returned results set using a while loop (as shown in the notes).
Finally close the database connection using the pg_close() function.
Upload the file to your dialogplus directory and check it works in the web-browser.
As mentioned earlier, PHP is notoriously hard to debug. If there is no output from the PHP script,
check the console log. If you see a general 500 error ("Internal Server Error"), there is probably a typo in your file
(check all line breaks are present, check your SQL query in the query builder within pgAdmin). For other HTTP errors, see
this list.
If you see one of the errors you defined in the 'or die' statements,
there is a problem with either your SQL query or your database connection parameters.
If you get really stuck, we have provided an example PHP file here, but you will need to enter your own credentials to make this script run.