Database Lab 10

19 November 2014

December 2020 This lab is not assigned this term, although there are some interesting notes included. I am sorry to have mislead anybody . I have not done du dilligence in rviewing my web pages that have accumulated over the years.

During the last lab of the term, I want you to create practical application(s) for your database. Since the emphasis is on the database connectivity, neither a GUI nor a web application is required, though either of these would be nice. The applications should be easy for someone like a store employee or on-line customer to use. The most common operations, such as adding a customer or renting a tool, should be implemented. The Part 1 instructions now include how to use postgresql in C as well as Java and python.
The important issue of transactions and commit status is addressed there.

In any case, the store employee shouldn't have to guess at what products there are, but rather pick from a list, for example. Make it as easy to use as possible.

Alternatively, set up some XML output for various student tables I have.

Web-based application, using HTML and PHP (or other language)

By using forms on a web page, and processing the form information by a "cgi" program, you can create custom made result pages, for example, they can be search results, using a database SELECT statement, or do an INSERT or UPDATE of the database from the form data (and return some reassuring page to the client.)

For this assignment, you may either implement a search or update of your database, or the timetable database. For the timetable, you could implement any 2 of: Enroll in a course, show courses enrolled in by a student, show the classlist for a course (with mailto: links perhaps?).

The most straightforward way to proceed is to have one page with html and a form, and have this form call a php page, with script. However, a php page can also have a form, to allow calling of another php result. For example, a search can have a "Search again" form.

Setup

Your web pages need to be on a host that runs a web server, in a subdirectory of your home directory named public_html, We will be using osiris.ubishops.ca and I will make you an account, the public_html directory will already exist with proper permissions.

Put there a file named index.html (or index.php).Your page can be accessed using any browser, anywhere in the world, as either

http://osiris.ubishops.ca/~youracct
http://osiris.ubishops.ca/~youracct/index.html

The html form

Your main page should be named index.html It can contain the form you will use to provide input to your php program.
A form has a name, an action (program to receive the data) and a method, normally "GET". The form then contains INPUT elements and buttons. It may also contain "select lists" and textareas (for multiline text) See HTML, my introduction for details. The form elements need names, and they come between the <form ...> and </form> tags.

The simplest input is for a line of text (type="text") is the default. Its value is whatever gets typed in the field. You might want to specify type="password" for secure entries.
Buttons are input elements of type="submit" or type="reset". A submit button causes the form's action to be called, that is, the form data is sent to the php program mentioned as ACTION. Here is a form example, taken from the file timetable_footer:

   <form action="timetable.php" method="GET">
      A few letters of family name needed for search<br>
	<input name=search>
        <input name=foo value="Some other info!">
        <input type=submit value="Search">
        <input type=reset>
   </form>

This looks like the following, and if you press the submit button (Which is labeled "Search" by the optional value), you will either search the timetable database, like I did in class, or, if you are browsing the file directly, you may see quite a mess. The second input named "foo" is ignored by timetable.php. You will notice that some encoding is done on the query string, which will look something like:

?search=Bu&foo=Some+other+info%21

Note that spaces become + and the ! became %21. This will be unencoded before your PHP or Python program sees the data. PERL may have to decode.

A PHP cgi program

Well, a php file is actually an HTML page, with PHP script embedded in it. What happens is that PHP gets passed the file, and also the form data, it reads the file, and when it comes to the special <?php tags, it executes the script, up until ?>
PHP is well documented at www.php.net in many languages.
The data from the query string will be found in $_GET['search'] and $_GET['foo'].
All the database access functions are named pg_.... and they are all documented in the section PostgreSQL Functions in all the versions that I checked, the French and one Chinese.

So, your php page will be a certain amount of html text, with some sections of script, connect to the database, do queries, and then present results or inform the client that an update was sucessful or not.

Security, "escaping"

Any form data that will appear in an SQL query inside single quotes needs to be preprocessed, for one thing the actual single quote in O'Reilly needs to be doubled, so it doesn't crash the SQL syntax. There are more sinister ways that a single quote can be used. As for numeric data, HTML passes over strings, with no guarentee that actual digits have been entered. Thank you Tom. Use double quotes for php strings. Like perl, variables will be expanded. Don't forget the single quotes that SQL needs for the data.

Just don't put user data directly into an SQL statement, use query_params instead.

<ul><?php
$search =$_GET['linux'];	/* a particular record is requested by key */
$rownum =$_GET['rownum'];	/* or a row by number */

settype($rownum,'integer');	// it ought to be an integer, this forces it to be so.

/* connecting to a postgresql database  */
$link = pg_connect("host=cs-linux port=5432 dbname=timetable user=.... password=....") or die("Connect failed");
$query = "select * from students where linux = $1";
$result = pg_query_params($query, array($search)) or die("query failed");

while ($line = pg_fetch_array($result))     /* both indexed and associative */
{   $email = $line['email'];		    // can't go inside "  "	    
    print "<li>$line[1] $line[0] email: $email</li>\n";
}  
  pg_free_result($result);	/* Free resultset */    
  pg_close($link);		/* Closing connection */
?> </ul>

This code writes an ordered list of names and email addresses (list items, <li>) inside the unordered list tags: <ul>...</ul> Before and after would come whatever html-tagged text you like. And, you can have as many <?php .... ?> sections as you like, variables are global to the whole page. The actual timetable.php file constructs a table instead of the simpler list.

My examples

A simple update example, updateemail.html, calls updateemail.php
An elaborate search/update example (which is the action of the form above), done for the internet class, is:

timetable.php , it shows a query result formatted as an html "Table" and allows updating of any result by calling on
timeup.php on the row you select using "radio buttons" This in turn calls on
stuupdate.php to do the actual update or insert (using the POST method and a secure connection)

You can't see the code using your web browser, only the results of "running" them. To look at the code, open with an editor, all files are in /home/COURSES/CSC207

Debugging

Normally we don't want web clients to see any of our error messages. However, we need ot debug programs. First of all, for syntax errors, you will only get one error message. This is usually cryptic, the most valuable information is the line number. Look around, often it will be something on the line before, such as a missing semi-colon(;).
If there are no compiler errors, you will see the html source, as written by PHP. You may also see several run time errors, warnings, or notices. (an example to See some error messages )
Notices are not necessarily errors, since an undefined value may be interpreted, correctly, as FALSE. But a notice can draw your attention to a misspelled variable, such as $mane instead of $name.

Should you not see anything, include this line in your php code:

ini_set("display_errors","1");         // use during debugging for run time errors

After your file is working properly, you can get rid of those annoying notices by including this line in your php code:

error_reporting(E_ALL ^ E_NOTICE);	// do not show notices about undefined variables, etc.

How to run a PHP file

You must invoke php by accessing your file through the web server (named apache) of cs-linux. That is,

http://cs-linux.ubishops.ca/~you/index.html     not
file:///home/you/public_html/index.html		which will just show your php code in an ugly way

What to hand in: Just leave your files in public_html, where I or anyone else can run them.

Other language options

You may prefer to code in another language. Apache, the web server, detects the extension .php jand automatically runs that file throught the "PHP Hypertext Preprocesor". PHP has the unique feature of the code being embedded within an HTML page. Any other language is a pure executable, and must "print" all the fixed parts of the page.

If you prefer PERL or python, please note the following:

Your program in either language will need to print a complete html page, after printing a line with content-type and a blank line:

print "Content-type: text/html\n\n";	#PERL -or-
print "Content-type: text/html\n"	#Python

For a PERL program, put it in your directory public_html/perl/ Apache throws any file here to PERL to process. Links back and forth must take this positining into account.

In an html form tag, ACTION="perl/yourprog.pl"
While a link back from PERL would be <a href="../yourform.html>

For Python, mark the program executable [ chmod a+x yourprog.py ], put in public_html, and ask me for a "Link" from cgi-bin, then you call your program as

ACTION="/cgi-bin/you/yourprog.py" or
ACTION="http://osiris.ubishops.ca/cgi-bin/you/yourprog.py"
The link back would be: <a href="/~you/yourform.html>

In the second case, your form can be a local file, and the python will execute on osiris (but you won't be able to link back, of course.)

Any other language,

Such as C, C++, or even Java, can serve as a cgi (commin gateway interface) program, if the "web path" descends from /cgi-bin/. One such program on Osiris is Count.cgi, written in C. It provides a web counter, displayed as an image. For Java, one actually executes a shell script, which calls the Java class.

XML

XML is a file with markup "tags" that are nested. it is commonly used to transfer database information from one system to another. For instance, one system might produce: and another require:

<person>				<client who="Minnie Mouse" where="Disneyland" />
  <name>Minnie Mouse</name>
  <address>Disneyland</address>
</person>

For example, see: XSL stylesheet for XML and a DTD

Write a program or script to produce XML from a query, and XSLT to translate into some common form for information transfer, or to be used as a "stylesheet" for rendering as HTML. HTML is a special case of XML. (Almost. You can leave out closing tags or self-closing "/>". XHTML was supposed to correct this, but it didn't catch on.)

Lin Jensen