Display normalised MySQL tables on a web page

Designing an SQL Database System example from a Well House Consultants training course
More on Designing an SQL Database System [link]

This example is described in the following article(s):
• Database design - get it right from first principles - [link]

If you're searching for a page where you can try this code, select here

This example references the following resources:
http://www.wellho.net/resources/S154.html
http://www.wellho.net/resources/ex.php4?item=s154/tabled.php
http://www.wellho.net/resources/ex.php4?item=s154/just.php
http://www.wellho.net/resources/ex.php4?item=s154/docs.sql
http://www.wellho.net/resources/ex.php4?item=s154/docdata.sql
http://www.wellho.net/resources/S154.html
http://www.wellho.net/course/mqfull.html
http://www.wellho.net/course/phfull.html

Source code: tabled.php Module: S154


<?php

/* This is an example of a data presentation from a set of
normalised tables via a PHP web page.  Extra example written
during a Well House Consultants training course as demonstration
of principles.

* A number of CLIENTS
* Each client commissions a number of BROCHURES
* Each brochure is available in a number of TRANSLATIONS
* Each translation goes through a number of VERSIONS until it's released

All 4 tables are joined in a select which was tested out with
test data and the mysql client program prior to embedding it into
this test program.

A fifth table contains details of languages; that's included in
the join too.

Same SQL to create the database and tables, populate the tables
with data, and run the test select can be found under
          http://www.wellho.net/resources/S154.html

*/

#####################################################

/* Function to add a row to a table on the output.
Adds a start and end table tag if required (see flag params).

Also lengthens and shortens rows after position no. 1 to
ensure that all rows are the same length, right justified. */

function dumptab($lang,$start = 1, $end = 0) {
        global $result;
        static $intable = 0;
# start table if need be
        if ($intable == 0 and $start == 1) {
                $result .= "\n<table border=1 cellpadding=2>\n";
                $intable = 1;
                }
# Only add a row if we have some data
        if (count($lang) > 0) {
                $lenshow = 5;
                $dots = array(" >>>>> ");
# Shorten row if necessary
                if (count($lang) > $lenshow) {
                        array_splice($lang,1,count($lang)-$lenshow+1,$dots);
                        }
# Lengthen row if necessary
                if (count($lang) < $lenshow) {
                        $space = array();
                        for ($k=0; $k<$lenshow-count($lang); $k++) {
                                array_push($space,"&nbsp;");
                                }
                        array_splice($lang,1,0,$space);
                }
# Generate the row of a now fixed length
                $dets = "<tr><td width=100>".
                        implode("</td><td width=140>",$lang).
                        "</td></tr>\n";
                $result .= $dets;
# End table if necessary
                if ($end == 1) {
                        $result .= "</table>\n";
                        $intable = 0;
                        }
# Return an empty row ready for refilling
                $lang = array();
                }
        return $lang;
        }

#####################################################

# The main code - run the query!

mysql_connect("192.168.200.66","trainee","abc123");
mysql_select_db("brochures");

$rset = mysql_query("
select *,translation.tid as tiddler, version.vid as viddler
        from (((client left join brochure on client.cid = brochure.cid)
        left join translation on brochure.bid = translation.bid )
        left join version on translation.tid = version.tid)
        left join language on language.lid = translation.lid
        order by client.cid, brochure.bid, tiddler, viddler");

$result = "";
$old_cid = -1;
$old_bid = -1;
$old_tid = -1;
$lang = array();

# Loop through data, setting up a new table when the client or
# project changes, and a new row when the translation changes

while ($row = mysql_fetch_assoc($rset)) {
        if ($row[cid] != $old_cid) {
                $lang = dumptab($lang,0,1);
                $result .= "<br><b>Results for $row[customer]</b><br>";
                $old_cid = $row[cid];
                }
        if ($row[bid] != $old_bid) {
                $lang = dumptab($lang,0,1);
                $result .= "<br>Job: <i>$row[jobno]</i><br>";
                $old_bid = $row[bid];
                }
        if ($row[tiddler] != $old_tid) {
                $lang = dumptab($lang,1,0);
                $old_tid = $row[tiddler];
                $lang = array($row[name]);
                }
        $statustab = array("not yet known","to be reviewed","ACCEPTED");
        $status = $statustab[$row[signoff]];
        if ($row[proofno]) {
                array_push($lang,"No. $row[proofno]  - $status");
        } else {
                array_push($lang,"No translation available");
        }
}

# Ensure that the final row and table are added to the result set

dumptab($lang,0,1);

?>
<html>
<head>
<title>Translations in progress</title>
</head>
<body>
<h2>Job report ...</h2>
<?= $result ?>
<hr>
This output is from a sample program written by
<a href=http://www.grahamellis.co.uk>Graham Ellis</a> of
<a href=http://www.wellho.net>Well House Consultants</a> to
demonstrate how a number of tables can be used to hold data
in the form of a normalised database, and that data can be
presented in a restructured report on a web page using PHP.
<br><br>
Related resources:<br>
<a href=http://www.wellho.net/resources/ex.php4?item=s154/tabled.php>
Source code of this example</a><br>
<a href=http://www.wellho.net/resources/ex.php4?item=s154/just.php>
Source code of simpler (bare bones) example</a><br>
<a href=http://www.wellho.net/resources/ex.php4?item=s154/docs.sql>
SQL to set up tables</a><br>
<a href=http://www.wellho.net/resources/ex.php4?item=s154/docdata.sql>
SQL to set up sample data</a><br><br>
<a href=http://www.wellho.net/resources/S154.html>
"Designing a database" index</a>
<br><br>The page your on is the result of just a couple of
hours of work during a
<a href=http://www.wellho.net/course/mqfull.html>public MySQL
course</a> ... if you're new to online applications and have
a requirement to write a system such as this, you'll need to
learn both MySQL, and also
<a href=http://www.wellho.net/course/phfull.html>PHP</a>.
</body>
</html>

Learn about this subject

This module and example are covered on our public MySQL course. If you have a group of three or more trainees who need to learn the subject, we can also arrange a private or on site course for you.

Books covering this topic

Yes. We have over 700 books in our library. Books covering are listed here and when you've selected a relevant book we'll link you on to Amazon to order.

Other Examples

This example comes from our "Designing an SQL Database System" training module. You'll find a description of the topic and some other closely related examples on the "Designing an SQL Database System" module index page.

Full description of the source code

You can learn more about this example on the training courses listed on this page, on which you'll be given a full set of training notes.

Many other training modules are available for download (for limited use) from our download centre under an Open Training Notes License.

Other resources

• Our Solutions centre provides a number of longer technical articles.
• Our Opentalk forum archive provides a question and answer centre.
• The Horse's mouth provides a daily tip or thought.
• Further resources are available via the resources centre.
• All of these resources can be searched through through our search engine
• And there's a global index here.

Purpose of this website

This is a sample program, class demonstration or answer from a training course. It's main purpose is to provide an after-course service to customers who have attended our public private or on site courses, but the examples are made generally available under conditions described below.

Web site author

This web site is written and maintained by Well House Consultants.

Conditions of use

Past attendees on our training courses are welcome to use individual examples in the course of their programming, but must check the examples they use to ensure that they are suitable for their job. Remember that some of our examples show you how not to do things - check in your notes. Well House Consultants take no responsibility for the suitability of these example programs to customer's needs.

This program is copyright Well House Consultants Ltd. You are forbidden from using it for running your own training courses without our prior written permission. See our page on courseware provision for more details.

Any of our images within this code may NOT be reused on a public URL without our prior permission. For Bona Fide personal use, we will often grant you permission provided that you provide a link back. Commercial use on a website will incur a license fee for each image used - details on request.