PHP: Lists and Forms

Johann Mitlöhner, 2020

Installation and Use

The programming language PHP (Personal Homepage; PHP Hypertext Preprocessor) is widely used in websites that employ server-side programming (cf. w3techs).

Server-side means that the code is executed on the server; the client (the web browser) only sees the result i.e. the HTML output of the PHP code.

On Linux systems the PHP module for the Apache web server and additional software for PostgresQL database access can be installed (if necessary) with the package manager, e.g. in Debian/Ubuntu/Mint packages libapache2-mod-php and php-pgsql. For the R examples you also need the R software.

Typical web server configuration:

the suffix .php for files containing PHP code is expected
PHP files are put in the user's public_html directory on the web server

In your public_html directory create a file such as hello.php and enter the following code:

Hello from PHP:
<?php echo 'Hello World!'; ?>

In your browser enter the following into the location bar to access that page (note that some web servers use different URL patterns for user web pages):

http://servername/~userid/hello.php

In our current configuration:

https://studentweb.cluster.ai.wu.ac.at/~h12345678/hello.php

Usually the web server is configured to read a file index.html or index.php as default for a directory; in that case you can leave out the file name from the URL.

PHP sections are marked like this: <?php some-code-goes-here ?>

You can have several PHP sections in a file
You can use only HTML in a PHP file, but not PHP in an HTML file.
It is usually possible to configure the web server to handle PHP in files ending in .html; however, this is considered bad practise by many, and this author concurs with that view: file extensions are here for a reason, they let you know what's inside.

A useful summary of PHP related details is available via

<?php phpinfo(); ?>

SQL Select

The following script produces a list of products from a PostgresQL database table prod; we put this into a file such as listprod.php in the public_html directory:

<?php include 'header.php'; ?>
<h2>Products</h2>

<table>
<tr><th>ID<th>Name<th>Price<th>
<?php
$query = 'select id, name, price from prod';
$result = pg_query($query) or die('Query failed: ' . pg_last_error());
while ($x = pg_fetch_object($result)) {
  echo "<tr><td> $x->id <td> $x->name 
    <td align=right> $x->price <td>\n";
}
pg_free_result($result);
pg_close($dbconn);
?>
</table>

The file header.php contains code that is used in this page and other pages in the application.
We put this commonly used code in a separate file and include it.
Note that the include statement is the first line in the PHP file. This will be important later for session management.

The content of our header.php is:

<?php
$dbconn = 
  pg_connect("host=myhost dbname=mydb user=myuser password=mypw")
  or die('Could not connect: ' . pg_last_error());
?>
<meta charset=utf-8>
<link rel=stylesheet href=style.css>

In our current configuration the host is postgresql.student-db.svc.cluster.local
The values for dbname and user are your userid, such as h12345678
The password is revealed in the video (as an extremly rudimentary attempt at security)

The connection to the database is made; obviously you have to supply your own credentials for dbname, user and password. This file will be executed on the server; the browser will not see the PHP code with the password, only the output (the meta tag and the stylesheet link).

Valid HTML

You may have noticed that we are not writing proper HTML, since

not all tags are closed, and
option values are not in double quotes
Nevertheless, browsers support it. Obviously some tags must be closed, such as h1-h9, table, ul, b, a.
Feel free to write proper HTML instead i.e. close all tags, put all tag options in quotes.

Charset and Encoding

The meta/charset line is used for UTF-8 encoded text containing non-ASCII characters, e.g. German umlauts.

Nowadays all application components (Postgres database, Apache web server, PHP module, and browser) should interpret UTF-8 correctly. However, the character set in the browser is often set to other defaults, such as ISO 8859-1; in that case non-ASCII characters such as German umlauts are displayed incorrectly; such problems are avoided by

stating the charset explicitely, or
where possible, simply not using non-ascii characters, e.g. in web pages writing ö instead of ö

Style

The style sheet style.css contains common layout options for all web pages of this application to provide a consistent look. Here is a very simple example:

body { margin: 5%; }

The option margin for the element body is set to 5%. Other options for HTML elements are set in a similar fashion, e.g. background colors and font sizes.

Of course you can put other PHP and HTML statements in the header.php file as well, e.g. a menu list or icons and banners. If you want similar elements at the bottom of the page you can include a trailer.php file. By combining included files and CSS you can easily create a uniform and professional look for your application.

SQL Insert

Entering data is a two-step process:

present form with input fields
process data received from form

The first step is static HTML only, but we put this into a file such as enterprod.php so we have the option of adding PHP code later:

<h2>Enter new product:</h2> <form action=insertprod.php method=POST> <table> <tr> <td> ID: <td> <input type=text name=id> <tr> <td> Name: <td> <input type=text name=name> <tr> <td> Price: <td> <input type=text name=price> <tr> <td> Stock: <td> <input type=text name=stock> <tr> <td> <td> <input type=submit value=OK> </form>

The insert script only makes sense when called as action from an HTML form. The script insertprod.php takes the form input and constructs an SQL Insert statement:

<?php include 'header.php'; pg_query("insert into prod (id, name, price, stock) values ('$_POST[id]', '$_POST[name]', $_POST[price], $_POST[stock])"); echo "Ready."; ?>

PHP Strings can extend over several lines. Complicated SQL statements should be written in a manner that is easy to read and understand. A clear layout greatly simplifies maintainance. It may take a little longer to write, but the investment pays off hugely in terms of fewer bugs.

Single quotes are used inside the SQL statement to delimit text values. Price and stock are numerical values, so they do not need quotes.

Processing form input is a major security problem. In real-world applications it is necessary to use some protection, such as htmlspecialchars($_POST['name']) or similar. Otherwise it is easy for attackers to inject malicious code.

Exercises

Create a home page for your lists and reports where you link to everything you create.
Create lists for the other tables in the shopping cart example, and also for join and group by statements, e.g.
- List of customers
- List of orders
- Number of orders per customer
- Sales per customer
- Sales per product
Create data entry forms for the shopping cart tables, and check if newly entered data show up in the reports.
Do the same for all your other tables.

Lots of work -- but practise makes perfect!