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 |
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," ");
}
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.