A vd el ni ng en fö r da ta ve te ns ka p vi d In st itu tio ne n fö r In fo rm at ik o ch M at em at ik
EXAMENSARBETE 2003:D13
Jörgen Skoog Daniel Karlsson
Web-based database solution
Drivhuset Sweden
EXAMENSARBETE
i
Högskolan Trollhättan ⋅ Uddevalla
Institutionen för Informatik och Matematik
Uppsats för filosofie kandidat i Datavetenskap
Web-based database solution
Drivhuset Sweden
Jörgen Skoog Daniel Karlsson
Examinator:
Steven Kirk Institutionen för Informatik Handledare:
Jonas Amoson Institutionen för Informatik
Trollhättan, 2003
2003:D13
DEGREE PROJECT
ii
Web-based Database Solution
Drivhuset Sweden Jörgen Skoog Daniel Karlsson
Summary
We have developed a web-based interface that will be used to putting statistics together by Drivhuset Sweden. Today, Drivhuset Sweden consists of nine Drivhus from Malmö in the south, to Gävleborg in the north. Their need for a common tool to put statistics together arises from the fact that each Drivhus has individual routines when it comes to running statistics on its activity.
We received a list of requirements from Drivhuset Sweden, and on the basis of that list we drew a design proposition on a piece of paper of what we had in mind. With that design idea as base, we started preparing the internal web pages under supervision of a working group at Drivhuset Sweden. That working group has been available throughout the project for feedback and Q&A. We have discussed ideas and proposals, given and received points of views and brought up a solution that suits their needs.
Drivhuset provided us with access to a web server account. The server supports MySQL and PHP, which became the frame of our work. Some of the functionality has been implemented using JavaScript.
The result was a web-based database solution where all Drivhus’ can gather their activities using the same method, and they can all take part of each other’s information and ideas.
Publisher: University of Trollhättan ⋅⋅⋅⋅ Uddevalla, Department of Informatics and Mathematics Box 957, S-461 29 Trollhättan, SWEDEN
Phone: + 46 520 47 50 00 Fax: + 46 520 47 50 99 Examiner: Steven Kirk
Advisor: Jonas Amoson
Subject: Computer science Language: English
Number: 2003:D13 Date: May 20, 2003
Keywords MySQL, PHP, JavaScript, HTML, MySQL-Front, MySQL Control Center
EXAMENSARBETE
iii
Webbaserad databaslösning Drivhuset Sverige
Jörgen Skoog Daniel Karlsson
Sammanfattning
Vi har utvecklat ett webbaserat gränssnitt för sammanställande av statistik för Drivhuset Sverige. Drivhuset Sverige består i dagsläget av nio stycken Drivhus från Malmö i söder till Gävleborg i norr. Deras behov av ett gemensamt verktyg för sammanställande av statistik grundar sig på att alla Drivhusen har individuella rutiner för hur de för statistik över sin verksamhet.
Vi fick en kravspecifikation av Drivhuset Sverige och efter den arbetade vi fram ett designförslag på papper över hur vi hade tänkt oss den. Från den designen utarbetade vi Drivhusets interna sidor under överseende av en arbetsgrupp från Drivhuset Sverige.
Arbetsgruppen har varit med oss under hela projektets gång som bollplank. Vi har bollat idéer och förslag, fått synpunkter och gett synpunkter och tagit fram en lösning som passar deras behov.
Drivhuset gav oss tillgång till ett webbserverkonto med MySQL och PHP stöd vilket blev stommen i vårt arbete. Viss funktionalitet gjordes med JavaScript.
Resultatet blev en webbaserad databaslösning där alla Drivhus sammanställer sin verksamhet på ett gemensamt sätt och alla kan ta del av varandras information och idéer.
Utgivare: Högskolan Trollhättan ⋅⋅⋅⋅ Uddevalla, Institutionen för Informatik och Matematik Box 957, 461 29 Trollhättan
Tel: 0520-47 50 00 Fax: 0520-47 50 99 Examinator: Steven Kirk
Handledare: Jonas Amoson
Huvudämne: Datavetenskap Språk: Engelska
Nivå: Fördjupningsnivå 1 Poäng: 10
Rapportnr: 2003:D13 Datum: 2003-05-20
Nyckelord: MySQL, PHP, JavaScript, HTML, MySQL-Front, MySQL Control Center
Web-based database solution
iv
Preface
This project has been extremely rewarding since we have been working with a real task that Drivhuset hopefully will find very useful.
We would like to thank Drivhuset for their support, constructive criticism and suggestions about functionality and additions. They also gave us relatively free hands during the creation when we came up with new proposals.
The distribution of work has been evenly distributed throughout the project.
Web-based database solution
v
Table of Contents
Summary... ii
Sammanfattning... iii
Preface ...iv
Table of Contents ...v
1 Introduction...1
1.1 Background...1
1.2 Purpose and objective...1
1.3 Delimitations...1
1.4 Drivhusets requirements...1
2 Prerequisite ...2
2.1 Server-Side Script ...2
2.2 Database Manager ...3
2.3 JavaScript ...4
3 Method...4
3.1 Methods...4
3.2 Approach...5
4 Analysis ...6
4.1 Table Design...6
4.1.1 Entity Relation Diagram ... 6
4.1.2 Optimization ... 7
4.2 Tables and Dependencies ...7
4.2.1 Table ’users’... 7
4.2.2 Table ‘ort’ ... 8
4.2.3 Table ‘person’ ... 8
4.2.4 Table ’utbildningsomr’... 9
4.2.5 Table ’ide’... 10
4.2.6 Table ’bransch’ ... 10
4.2.7 Table ’verksamhet’ ... 11
4.2.8 Table ’kategori’... 11
4.2.9 Table ’malgrupp’... 12
4.2.10Table ‘ideperson’ ... 12
4.2.11Table ‘personmote’ ... 13
5 Constructing the website...13
5.1 Tools ...13
5.1.1 Adobe Photoshop Elements ... 13
5.1.2 ConTEXT... 14
5.1.3 MySQL-Front and MySQL Control Center ... 14
5.1.4 LeechFTP... 14
5.2 Structure ...14
5.2.1 Site structure ... 14
5.2.2 Brief file guide ... 16
5.3 Database ...23
5.3.1 Table Implementation ... 23
5.4 Manuals ...25
6 Result ...25
7 Conclusion ...26
References ...27
Web-based database solution
vi Appendices
Appendix A - Drivhuset Requirements ...1
Appendix B - Manual ...5
Appendix C - Admin Manual ...34
Appendix D - Source Code...43
Web-based database solution
- 1 -
1 Introduction
Drivhuset is represented in an increasing number of cities around Sweden. The need for a common tool to gather and compile statistics became more obvious. Up to this date, each separate Drivhus has individually customized ways of measuring statistics. This complicates a mutual statistic platform considerably, so being able to measure data commonly is of great importance.
1.1 Background
Drivhuset contacted us in December 2002 to ask us if we could create a web interface to be used by all Drivhus’ in Sweden that are going to work towards their central server in Karlstad where all their projects and ideas will be stored.
1.2 Purpose and objective
Our purpose was to create a web-based database solution for Drivhuset Sweden where they can input data into the database using online forms. The objective is that all Drivhus shall be able to view all entries of the database, but only be able to alter entries connected to the city the particular Drivhus is situated in.
The users will also be able to perform searches in the database to, for example, view information about a certain project, and find out which projects became companies during a certain period of time, or maybe look up a specific person’s number of guidance meetings.
To make the interface we created easy to use, we will also make a manual with step-by- step instructions; one for all Drivhus’ in Sweden and one for the Administrator in Karlstad that contains more information about the database and such.
Drivhuset would also like a web interface connected to a database where they can input information about lectures and seminars they’ve held to profile themselves to be able to measure their activity.
1.3 Delimitations
Our project consists of creating the database and the web interface needed to manage it.
The input of data into the database is not within the limits of our project, but will be implemented by each Drivhus using the tool we created.
1.4 Drivhusets requirements
Drivhuset handed us a specification that contained information about functionality and
content they want to have implemented in the database solution.
Web-based database solution
- 2 -
We decided to leave some parts of their specification out, like for example specific data to be placed in certain list boxes and such. The specification can be found in Appendix A.
2 Prerequisite
Since our objective was to create a web interface intended to communicate with a database, we chose to work with PHP and MySQL. Not only does PHP have good connections with MySQL; its sessions also provide safety when it comes to user identification and authentication.
We needed to use some JavaScript for additional functionality such as on-the-fly input controls, since PHP is a server-side script language.
We mostly use Swedish file- and variable names and such, since both the users and the server administrator are Swedes.
2.1 Server-Side Script
Rasmus Lerdorf started to write some Perl scripts in 1994 that grew to a package called
”Personal Home Page” (PHP) [6], but now the abbreviation stands for Hypertext Processor. As the interest in PHP grew, the creation of a script engine with support for in-data from HTML forms took place. Along with the increasing interest, an organisation was founded and PHP3 was developed. The latest version is PHP4 and is based on Zends script engine.
PHP is a HTML-embedded server-side script language, distributed as open-source and free to use for everybody. The script runs on the server and not locally.
It supports the most common databases (among with numerous SQL-databases), many web servers and has many features such as XSLT (Extensible Style Sheet Language), graphic handling, and LDAP (Lightweight Directory Access Protocol) that can be activated when compiling. PHP uses PCRE (Perl Compatible Regular Expressions) to handle regular expressions. It mainly runs in three different ways, as CGI script, as a module to a web server or as a Command Line Script.
Variables can, like in all modern programming languages, be declared when needed.
What distinguishes a variable in PHP is the $-prefix (E.g. $username). The data type of the variable is of little interest.
PHP makes it easy to mix programming logic and HTML in the same file, but in many cases the code is placed in separate functions that are imported into the PHP script.
Other alternatives to PHP are ASP and JSP. ASP and JSP works in a similar fashion as
PHP.
Web-based database solution
- 3 -
ASP (Active Server Pages) is Microsoft’s way of using code in HTML pages. ASP is mostly written in VB-script, which is a version of Visual Basic. It is also closely related to other MS techniques.
JSP (Java Server Pages) is Sun’s answer to Microsoft’s ASP and these two are almost identical, but JSP uses Java as programming language.
In both ASP and JSP the code starts with ”<?” and ends with ”?>”. PHP starts with
“<?php” and ends with “?>”
What made us choose PHP instead of ASP/JSP?
• Is platform independent, but some functionality requires that the right libraries are available on the platform it is compiled on.
• Has online documentation, where everybody can contribute with helpful comments that help development.
• Is easy to use as the syntax is a mix of C and Perl.
• Is developed and designed for web solutions.
• Has better support for databases with open source like MySQL.
2.2 Database Manager
The database management system we worked with is MySQL (MyISAM).
According to Mysql.com:
MySQL is the most widely spread DBMS (DataBase Management System) tool with open source and was developed by the Swedish company MySQL AB. MySQL is distributed under GPL (GNU General Public License) which is a legal agreement that regulates the use of free software from GNU and the FSF (Free Software Foundation).
“MySQL is a SQL database server. SQL stands for “Structured Query Language” which is used to handle and get data from one or more databases. The database server stores data in the database and the clients use SQL to manipulate and fetch data from the tables in the database. A database server contains more than just databases, such as authentication tools to handle ownership and access rights to the different databases.”
[5]
MySQL is a relatively simple database server that focuses on speed and security. With a
script language such as PHP or Perl it is easy to build a database-based dynamic
webpage.
Web-based database solution
- 4 -
2.3 JavaScript
JavaScript or ECMA-script as it also is called, is a script language used to create dynamic HTML documents. JavaScript can be embedded in the HTML document and doesn’t need extra files to function. But external JavaScript files can be linked in if needed.
Java and JavaScript aren’t the same. Java is a programming language and JavaScript is a script language as the name implies. The biggest different is that in Java, real programs can be created.
JavaScript’s biggest advantage is the ease to develop, write and test the functionality of the script because all that is needed is a JavaScript-enabled browser, and almost all browsers are JavaScript compatible. Another advantage with JavaScript is the easy control of user initiated events such as forms and objects on the webpage or browser.
With relatively few rows of code you can retrieve the users screen resolution, browser version and operating system, and then adjust the page according to this information.
Some things that are possible with JavaScript could only be done with CGI (Common Gateway Interface) in the past.
JavaScript’s disadvantage is that it cannot save user information when the user changes webpage or shuts down the browser. The only way to save user information is to save cookies that are used at the next visit, assuming that the user has the same computer and browser. Not all browsers have JavaScript enabled.
PHP is server-side, which means the script executes on the server, and the client gets the servers interpretation back as a result of the script. This fact meant that we needed to use some JavaScript on our pages to perform “on-the-fly” operations, for example in laggtillperson.php [Appendix B page 50] where the JavaScript checks if the compulsory data has been entered.
3 Method
3.1 Methods
The main purpose with this project was to create a web-based database solution for Drivhuset Sweden that is supposed to become a mutual tool for all Drivhus’ in Sweden.
Since the Drivhus’ work by individual routines and name their activities differently it’s hard to compile data to use for comparing different Drivhus’ to get results.
Our work has been computer scientific with a touch of system science when it comes to
analysis of the database and building the tables.
Web-based database solution
- 5 -
3.2 Approach
On the basis of Drivhusets specified demands, a first rough sketch was formed with the basic design of the site that we had in mind. We brought this outline to the first real meeting we had with Drivhuset, and this is where the foundation to our continued work was made. Our design proposal was approved and thereby we drew up guidelines for how to shape the web site and how to implement the requested functionality. The designing of the database tables, including dependencies and entities, was made during the analysis phase where we also set up limitations and sizes of each table member.
The web hotel we were supposed to use had certain limitations when it comes to support for database management and scripting. This led us to investigating suitable tools and methods to use to be able to work effectively according to those limitations.
When all necessary information was obtained and the tools chosen, the actual making of the web site could commence. When the construction was done and all requested functions was implemented, we handed user accounts to Marie, Mariah and Mikael so they could start testing their new tool.
We tried to make a scalable solution since it is possible that the future will require
additions in the web application. We have tried to name variables and files consistently
and using names that explain what the variable/file is used for. This will hopefully make
any administration of the site easier.
Web-based database solution
- 6 -
4 Analysis
The first phase, according to David M. Kroenke, is to start making a rough sketch of the tables we thought would be needed and their dependencies. The design contained the tables’, fields and dependencies, using names that would be easy to understand. [1]
4.1 Table Design
4.1.1 Entity Relation Diagram
We have decided to make this entity relation diagram to be able to clearly see the relations between the tables in the database [1]. You can also see their dependencies.
Weak entities (entities that can not exist without their connection to a stronger entity) have round corners whilst strong entities have sharp corners.
The text in the diamonds describes the relationship. E.g. person vs. Idé has the relation
“N:N” (read: “many to many”). This means that one person can be related to many ideas, and one idea can also contain many persons.
person ide
1:N N:N
ort 1:N verksamhet 1:1 1:1
kategori
malgrupp
1:N
1:1 N:N
utbildningsomr personmote ideperson
1:1 bransch
Web-based database solution
- 7 -
The softly curved lines you see between idé and person show a join between those tables.
4.1.2 Optimization
We tried to optimize the design of the tables as much as possible. Each field in every table has a maximum number of allowed characters specified. This is one thing that is not very easy to optimize, since you can never know how long somebodys surname might be for example. The fields that we knew the exact length of, such as date and social security number are strictly limited. In other words, we did everything we could to make the database as compact as possible.
The tables kategori, malgrupp, bransch and utbildningsomr were placed in the database to make additions to values on the site easy. On the site there are numerous listboxes containing different values, for example categories. These values have all been specified by Drivhusets’ project group and it is possible that they forgot to mention some appellations. This means that a user might not find the desired value, and therefore wants to add a new, more suitable one.
Since all those values exist in these tables, the update is easy to make by just inserting that specific requested value in the right table, and the effect on the site will be immediate.
4.2 Tables and Dependencies
We designed the tables carefully and tried to make them as unlimited as possible.
4.2.1 Table ’users’
Attributes Domain Limitations
id int(11)unsigned PK, AUTO_INCREMENT
username varchar(30)
password varchar(50)
The table users contain three fields; id, username and password. Id is the primary key
(PK) and uses AUTO_INCREMENT which means it increases automatically for each
new user that gets added. The database automatically set the auto increasing primary
key length to 11 characters, 99.999.999.999 entries, which is more than enough and can
be considered as infinite.
Web-based database solution
- 8 -
We decided to let the usernames be the name of the city the specific Drivhus is situated in to easily separate the different users. 30 characters should be enough to cover the name of any city in Sweden. Any kind of password is allowed.
4.2.2 Table ‘ort’
Attributes Domain Limitations
id int(11)unsigned PK, AUTO_INCREMENT
ort varchar(30)
The table ort (locality) consists of two fields. Id is the primary key and ort contains cities. The contents of this table, like many others, will be showed to the user as values in a list box used for searches by locality. If a new Drivhus locality gets added to this table, it will appear in the list box on the site immediately.
4.2.3 Table ‘person’
Attributes Domain Limitations
persid int(11)unsigned PK, AUTO_INCREMENT
kon varchar(6)
eNamn varchar(50)
fNamn varchar(40)
personnr varchar(11)
kommentar varchar(200)
address varchar(70)
postnr varchar(6)
ort varchar(25)
telefon varchar(20)
mobil varchar(20)
epost varchar(100)
utbildningsomr varchar(30)
drivhusort varchar(25)
date timestamp
Web-based database solution
- 9 -
The person table has 15 fields with information regarding the persons that gets added to the database. Since we cannot know what information will be inserted, we have tried to make each field large enough for the data to fit, but still not extremely large. We’ve considered long firstnames, lastnames, addresses and e-mail addresses and such, and then we added a few more characters on top of that to be sure to get hold of any deviation. The table also contains the attribute date, a timestamp so that the users will get the most recent data presented on top, or where it might be easiest to find on our pages.
4.2.4 Table ’utbildningsomr’
Attributes Domain Limitations
id int(11)unsigned PK, AUTO_INCREMENT
utbildningsomr varchar(50)
The table utbildningsomr is another table which values will be presented in a list box.
Utbildningsomr contains education areas, and we chose to make it a separate table
instead of just adding education areas in the HTML code in case the Drivhuset users
will come up with more education areas they want to add. Now, instead of having to
find the right file, the right row and to add the right code in the right place, the
administrator only has to insert a line into the MySQL database and the new value will
be presented on the web page in an instant.
Web-based database solution
- 10 - 4.2.5 Table ’ide’
Attributes Domain Limitations
ideid int(11)unsigned PK, AUTO_INCREMENT
namn varchar(50)
typ varchar(8)
bransch varchar(30)
beskrivning text
reg char(3)
bolag varchar(16)
orgnr int(20)unsigned Allow NULL
datum int(8)unsigned Allow NULL
anstallda int(3)unsigned Allow NULL
omsattning varchar(20) Allow NULL
hemsida varchar(80) Allow NULL
This table contains 12 fields, and some of these are compulsory. The fields where NULL is allowed are fields that can be left empty by the user. If the idea isn’t a company it might be difficult to provide an organization number or number of employees and such, and therefore NULL is allowed. Once again we have limited the size of each domain but they should still be large enough.
4.2.6 Table ’bransch’
Attributes Domain Limitations
id int(11)unsigned PK, AUTO_INCREMENT
bransch varchar(50)
The table bransch (industry) consists of two fields, id and bransch. This is another table
that gets its values fetched by a list box. The reason we made it a separate table is
because additions are now easy to make. A short line needs to be given as a command to
the database manager, and the added industry will appear immediately in the list box on
the web page.
Web-based database solution
- 11 - 4.2.7 Table ’verksamhet’
Attributes Domain Limitations
verkid int(11)unsigned PK, AUTO_INCREMENT
namn varchar(50)
kategori varchar(50)
malgrupp varchar(50)
kommentar Text Allow NULL
narvarande int(11)unsigned
trycksaker int(11)unsigned
ovrigexpo varchar(100) Allow NULL
totalexpo int(11)unsigned Allow NULL
ledare varchar(80)
ort varchar(30)
The table verksamhet (activity) contains information about Drivhusets activities. Users will freely input relevant data about the activities they have carried out. Approximate size limitations have been made together with Drivhuset since we didn’t know how large the entries could be.
4.2.8 Table ’kategori’
Attributes Domain Limitations
id int(11)unsigned PK, AUTO_INCREMENT
kategori varchar(60)
The kategori (category) table contains the different categories Drivhusets activities
might be, for example “lecture” or maybe “workshop”. An addition of data in this table
will be able to view immediately on the web site.
Web-based database solution
- 12 - 4.2.9 Table ’malgrupp’
Attributes Domain Limitations
id int(11)unsigned PK, AUTO_INCREMENT
malgrupp varchar(50)
Malgrupp (target group) contains the different target groups Drivhuset might arrange for example a workshop for. On the site, these target groups are presented not in a list box, but as checkboxes. Alterations in this table will have immediate effect on the web site.
4.2.10 Table ‘ideperson’
Attributes Domain Limitations
id int(11)unsigned PK, AUTO_INCREMENT
Pid int(11)unsigned
iid int(11)unsigned
The table ideperson consists of three fields where id is the primary key. pid and iid are unsigned integers.
When a person has been added to the database, and gets connected to an idea/company, person id will end up in pid, and idea id will end up in iid. This is called a join of tables.
Since one person can have many ideas and many persons can be connected to the same idea, the join creates a many to many relation.
This example table contains two persons and two ideas, and as you can see, person1 is a member of two ideas, while person2 is a member of one idea which happens to be idea2. This makes presenting of data easier than if you were to store person1 twice in the person table and idea2 twice in the idea table. All that is needed is a subquery.
id1 person1 idea1
id2 person1 idea2
id3 person2 idea2
Web-based database solution
- 13 - 4.2.11 Table ‘personmote’
Attributes Domain Limitations
id Int(11)unsigned PK, AUTO_INCREMENT
pid Int(11)unsigned Default 0
motesdatum Int(8)unsigned Default 0
moteskommentar Varchar(200) Allow NULL
A person who contacts Drivhuset with an idea or a project gets his/her information added to the database. Since this certain idea may take quite some time to realize, continuous guidance meetings with Drivhuset might be necessary. This gives Drivhuset an insight in how the project comes along and evolves between meetings, and at the same time supports the person working with it.
Id is the auto increasing primary key in this table. In order to keep track of which persons meeting we are adding, pid contains the persons ID. The date of the meeting will be inserted into motesdatum, and if there is any comment about the meeting it will be inserted into moteskommentar.
5 Constructing the website
Since the construction of the website was made from scratch, we had to find suitable tools for the implementation.
5.1 Tools
To create the website we decided to use Adobe Photoshop Elements [7] for graphics, ConTEXT [8] for code management, MySQL-Front [9] and MySQL Control Center [10] for table creation and editing, and LeechFTP [11] for file transfers.
5.1.1 Adobe Photoshop Elements
Photoshop Elements is a graphics program that we used to create for example the images used as links in the menubar. We decided to use a similar menu design to the one www.drivhuset.se already had.
The reason for picking this particular software to create and edit our images is that it
suited our needs, and was available for us at school. Photoshop has got many useful
functions and an interface that is easy to learn and understand.
Web-based database solution
- 14 -
Another contributing reason is that we have been working with Photoshop before this project and got acquainted with the environment, which helped a lot since we already recognized most of its functions. This gave us more time to devote to the actual making of the website.
5.1.2 ConTEXT
Context is a text editor that we used to write all our code, HTML, PHP and JavaScript.
The main reason for us picking ConTEXT is that it has got the builtin function of coloring the code you write. This makes coding a lot easier, especially as we have files that contain mixed PHP, HTML and JavaScript. Even ConTEXT is a program we were familiar with.
5.1.3 MySQL-Front and MySQL Control Center
MySQL-Front and MySQL Control Center are the two programs we have used for all database management. We have used it both to create tables using SQL commands, and editing tables and table data with the programs’ graphical user interface.
MySQL-Front and MySQL Control Center are both freeware. The reasons we have been using two different programs for the same purpose is that we couldn’t decide which one was best. They are both pretty much the same, but MySQL-Front has got some features MySQL Control Center doesn’t have and vice versa.
5.1.4 LeechFTP
Since PHP is server-side, and the database we were going to use was on Drivhusets server, we worked towards the server.
To be able to upload and download files, you need to have a FTP program. We chose LeechFTP since it’s first of all freeware, plus it is a clean and simple tool without unnecessary functions. This is also a program we both had used before.
5.2 Structure
As we mentioned earlier, we decided to stick to a similar design that is used at www.drivhuset.se, so the users will feel ’at home’. Drivhuset wanted a simple and clean site that was easy to use, and that is what we had in mind while creating the pages.
5.2.1 Site structure
The names of the links on the site have got describing names, and when you click on one and that page appears, you can easily navigate and input the requested data.
Link number one is Lägg till (add), and there you can insert a person(s) to the database
and then connect them to the idea/company you add. The second link is Sök (search),
Web-based database solution
- 15 -
since Drivhuset requested that function. They also requested that editing of database entries should be possible, so link number three is Redigera (edit). Drivhuset also wanted a tool to input and view data about their activities, hence link four; Verksamhet (activity).
Since our site provides secure login using username and password, the users can change their passwords by clicking the fifth link, Byt lösen (change password). Link number six, Logout speaks for itself.
5.2.1.1 Add
Here we put all that has to do with adding persons and ideas/companies because these are connected to each other. One must start with adding a person before connecting it to an idea/company.
The page where persons can be added uses a design with text fields where fields marked with (*) are mandatory. Certain information about the person must exist; otherwise the tool might not work the way it is supposed to. If the user would miss a mandatory field when clicking the submit button, an error message is displayed to the user stating that information is missing. The user is told to complete the information requested.
The idea/company page, which is the next step, has a similar design. Some fields here are mandatory. The user adds information about the idea/company and the amount of information needed here varies. If the project being added is a company, fields containing company information will be available. If the project is just an idea, those fields will be unavailable.
Persons the user wants to connect to the idea/company are added using dropdown menus at the bottom of this page. The persons listed in the dropdown menus are sorted by date, which means the person that was added last will be easiest to access.
5.2.1.2 Search
Here you can find all information regarding searches in the database. The users can search for persons, ideas/companies, or Drivhusets activities. The search has no limitations since all the data in the database is supposed to be able for all Drivhus’ to view.
All search starts with displaying a search form. The user can here enter any information he or she would like to search for in the database. The more fields the user completes, the more accurate the search results will be. It is possible to only search by using parts of names or addresses etc.
At the bottom of the search results, the user will see two links. One is used for turning
the search results into a comma-separated file, and the other to create a semicolon
separated file. The reason to why we put two links here is that different versions of
Microsoft Excel use different types of separators. For example the Excel version on the
Web-based database solution
- 16 -
computers in the labs at school supports csv-files separated with semicolons while the version of Excel in Drivhuset Trollhättan supports colon separated files.
5.2.1.3 Edit
To edit a person or an idea/company, a search has to be made to locate the entry the user wants to edit. When the search has been made and the user has found the person or idea/company he or she wants to edit, the user needs to click the Ändra (edit) link. Now the edit form will be displayed and already completed with the information connected to the requested person or idea/company. When all changes have been made the user has to press the button Ändra (edit) to execute the database update.
5.2.1.4 Activity
This page tells the user about the available options, “add activity” or “search for activity”. The reason activity has its own page is because it is a detached part of Drivhusets internal pages that have nothing to do with persons and ideas/companies.
Here Drivhuset inputs information about lectures and workshops they’ve held among other activities.
5.2.1.5 Change password
This page (byt lösen) handles password changes only. The user enters the old password first, and then the new password. A confirmation has to be done.
5.2.1.6 Logout
This is where the session will be unset and destroyed. Once clicked, the user is logged out and will have to log back in again to access the internal website.
5.2.2 Brief file guide
5.2.2.1 drivhuset.css
Contains Style Sheet information to enhance some graphics on our pages.
5.2.2.2 login.htm
The page you use when you login. Calls the login script login.php.
5.2.2.3 login.php
This file contains an important feature; secure login. Here you can find functions to
check if the user inputs a correct username and password and if the login information is
valid. A session starts which makes sure that you can only access the pages within the
site if you are logged in.
Web-based database solution
- 17 - 5.2.2.4 index.php
The intro page confirms that the user logged in successfully and prints a welcome message. The user now gets a new menu that is only accessible if you are logged in.
This menu allows users to add, search for and edit persons and ideas, but also to add and check Drivhusets work activity, change password and logout.
5.2.2.5 laggtillperson.php
This page contains a form in which student data should be completed. The list box containing education areas gets its values from a table in the database so that if a user realizes that the existing education areas aren’t enough, it’s easy to add new ones without having to alter any PHP or HTML code. All you have to do is insert a new value into the database.
JavaScript controls if all mandatory fields have been completed.
5.2.2.6 laggtillperson_s.php
This is the PHP script that handles the data the user completed in the “add person” form.
It checks if the person in question already exists in the database, and if it doesn’t, the values get inserted into the person table. The data in the fields regarding meeting date and meeting comment will be inserted into the personmote table.
5.2.2.7 laggtill_confirm.php
This file presents the information about the person the user just added. In this way the user gets a good look at what he/she entered and here gets a chance to alter or delete the person in question by clicking the link that says Ändra (alter) or Radera (delete).
The user also gets the option to either go back and add more persons to the database, or go directly to the “add idea/company“ page.
5.2.2.8 ide.php
The page the user comes to after laggtill.php. Here the user is supposed to connect the student/students he or she just added to an idea/company. Once again the list box gets its values from a table in the database, but this time it contains the trades/industries that are available for the idea/company. A bit further down on the page there are six list boxes. These contain all students added by for example the HTU Drivhus. The ones that were added last will be displayed on top since they are more likely to be the ones that the user wants to add to the idea/company.
Unless the idea is a company, not all fields will be enabled. JavaScript takes care of this
feature.
Web-based database solution
- 18 - 5.2.2.9 ide_s.php
This is the PHP script that handles the data the user completed in the “add idea/company” form. It checks if the idea/company already exists in the database, and if it doesn’t, the values get inserted into the ide table. It doesn’t matter which listbox the user decides to add persons from.
5.2.2.10 ide_confirm.php
This file presents the information about the idea/company the user just added. In this way the user gets a good look at what he/she entered and here gets a chance to alter or delete the idea/company in question by clicking the link that says Ändra (alter) or Radera (delete).
5.2.2.11 ideinfo.php
Ideinfo.php is the script that presents information about a certain idea. This page is available by clicking an idea/project name among the idea/project search results.
Not only does it display all the information the database has about the idea, it also shows which persons that are involved in this specific idea/project.
5.2.2.12 epostlista.php
On the page where the ”search person” search results are displayed, there is a row of checkboxes to the right. If the user checks one or more checkboxes (or uses the “check all” function) and then presses the button that says “Skapa e-postlista” (create mailing list), this is the script that will be executed. It displays the e-mail addresses of the requested persons so the user can simply copy and paste the addresses in their e-mail software.
5.2.2.13 redigera.php
This page gives the user two options; Edit person, and Edit idea/company. These links leads to the search forms. After clicking the “edit” link in the search results, the user will be able to edit the requested data.
5.2.2.14 verksamhet.php
This is a page contains two links to add verksamhet or search for verksamhet.
5.2.2.15 laggverksamhet.php
Here the user gets an option to either add an activity or to search for activities.
Web-based database solution
- 19 - 5.2.2.16 laggverksamhet_s.php
This is the PHP script that handles the data the user completed in the “add activity”
form. We bumped into a problem here. Since it’s possible to check more than one of the checkboxes, we need to take care of that somehow, and we chose to put the values in a string and solved the problem like this:
require("dbconnect.inc.php");
$stmtx = "SELECT * FROM malgrupp";
$queryx = mysql_db_query($db, $stmtx);
while ($rowx = mysql_fetch_object($queryx)) {
$malid = $rowx->id;
$_id = "id_".$rowx->id;
$id = ${$_id};
if ($id == 1) {
$getStmt = "SELECT malgrupp FROM malgrupp WHERE id = $malid";
$getQuery = mysql_db_query($db, $getStmt);
while ($getRow = mysql_fetch_object($getQuery)) {
$malgr .= $getRow->malgrupp . ", ";
} } }
5.2.2.17 sok.php
This page hands the user three options, to either search for an idea/company, to search for a person, or to search for an activity.
5.2.2.18 sokverksamhet.php
This page generates the form used for searching for activities.
5.2.2.19 sokverksamhet_s.php
This file presents the search results. We decided to use a clean and simple layout using tables. The Ändra/Radera (edit/delete) links to the right are available if the specific activity belongs to the user logged in. As we mentioned earlier, Drivhuset HTU is only allowed to alter and delete entries belonging to Drivhuset HTU, but they can still view the data of all Drivhus’.
At the last search result row, there are two links that lets the user export the search results to Microsoft Excel.
5.2.2.20 redverk.php
This file prepares the data that will be loaded for editing and generates the form that will
be used.
Web-based database solution
- 20 - 5.2.2.21 redverk_s.php
redverk_s.php takes care of the actual activity database update. The data that has been altered overloads the data that previously had that place in the database.
5.2.2.22 bytlosen.php
This is where the user has the option to change the password used for logging in..
5.2.2.23 byt.php
..and this is the script that performs the actual password change. First, the user’s old password is checked, and then the script controls if the new password and the confirmation of the new password match.
5.2.2.24 logout.php
A script in this file unsets and destroys the active session, and tells the user about the successful logout.
5.2.2.25 common.php
This file contains some useful functions to relieve many of our pages from lots of code that now gets called from this file instead. print_header(), print_trailer(), print_errorMsg() and print_list() are some of these functions. Here you can also find functions that handle generation of the forms that are used within the site.
5.2.2.26 dbconnect.inc.php
This is a file that creates a persistent connection to the database when needed. It contains username and password to the database among other things.
5.2.2.27 csv.php
Drivhuset wanted to be able to import search results to Microsoft Excel. This is the file that makes this possible. “header("Content-type: text/csv");” tells the browser that this is a csv file and asks the user if he or she wants to download the file.
Then the search result gets echoed into the file that the user now can download. We noticed that different versions of Excel support different types of separators. That’s why there are two options for the user. To either create a comma-separated file, or a semicolon-separated file.
The same file takes care of all three possible search results, and both kinds of
separators.
Web-based database solution
- 21 - 5.2.2.28 motesinfo.php
When the user decides to edit a user in the database, that specific persons guidance meetings will be displayed on that page. The dates of the meetings are links, and on click, this file makes a new window pop up containing more information about that specific meeting.
5.2.2.29 noaccess.php
As we mentioned, Drivhusets interal web site is protected by a secure login. If someone tries to basically manually enter a URL to one of the internal pages, this file displays a message that says that you need to login to gain access to the page in question.
5.2.2.30 persinfo.php
Persinfo.php is the script that presents information about a certain person. This page is available by clicking either a person’s last name, or e-mail address among the person search results.
Not only does it display all the information the database has about the person, it also shows which ideas/companies the person is involved in.
5.2.2.31 raderaide.php
The script in this file deletes all information about the specific idea/company from the database. Since we have a table that is a join of persons and ideas, the rows in that table which have anything to do with the idea being deleted are also deleted by the script in this file.
5.2.2.32 raderaperson.php
This file makes sure to delete all information about the specific person from the database. Since we have a table that is a join of persons and ideas, the rows in that table which have anything to do with the person being deleted, are also deleted by the script in this file.
Each person in the database has also got one or many guidance meetings related to it, so this is another dependency that hereby gets deleted.
5.2.2.33 raderaverksamhet.php
The script in this file deletes all information about the specific activity from the
database.
Web-based database solution
- 22 - 5.2.2.34 redide.php
This file prepares the data that will be loaded for editing and generates the form that will be used.
5.2.2.35 redide_s.php
Redide_s.php takes care of the actual idea database update. The data that has been altered overloads the data that previously had that place in the database.
5.2.2.36 redpers.php
This file prepares the data that will be loaded for editing and generates the form that will be used.
5.2.2.37 redpers_s.php
Redpers_s.php takes care of the actual person database update. The data that has been altered overloads the data that previously had that place in the database. It also enters the new meeting information into the personmote table, and the next time the “edit person” page loads, this new meeting will appear in the list of meetings.
5.2.2.38 sokide.php
This page generates the form for searching the database for ideas/companies.
5.2.2.39 sokide_s.php
This file presents the search results. The Ändra/Radera (edit/delete) links to the right are available if the specific idea/company belongs to the user logged in. As we mentioned earlier, Drivhuset HTU is only allowed to alter and delete entries belonging to Drivhuset HTU, but they can still view the data of all Drivhus’.
At the last search result row, there are two links that lets the user export the search results to Microsoft Excel.
5.2.2.40 sokperson.php
This file generates the form used for searching for persons.
It also presents the search results. The Ändra/Radera (edit/delete) links to the right are available if the specific activity belongs to the user logged in. As we mentioned earlier, Drivhuset HTU is only allowed to alter and delete entries belonging to Drivhuset HTU, but they can still view the data of all Drivhus’.
To the right there is one checkbox belonging to each person. The user can check one or
many of those, and generate a mailing list by pressing the button at the bottom of the
search results.
Web-based database solution
- 23 -
At the last search result row, there are two links that lets the user export the search results to Microsoft Excel.
5.2.2.41 validering.php
This file contains a script used for validation of zip code and social security number.
This ensures that these entries will look the same in the database.
5.3 Database
The database our tables are stored in is called drivhuset.
5.3.1 Table Implementation
We created our tables using SQL commands, which we inserted into the database managers we used, MySQL-Front and MySQL Control Center. Here they are:
CREATE TABLE person (
persid int(11) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
kon varchar(6) NOT NULL,
fNamn varchar(40) NOT NULL,
eNamn varchar(50) NOT NULL,
personnr varchar(11) NOT NULL,
kommentar varchar(200) NULL,
adress varchar(70) NOT NULL,
postnr varchar(6) NOT NULL,
ort varchar(25) NOT NULL,
telefon varchar(20) NOT NULL,
mobil varchar(20) NOT NULL,
epost varchar(100) NOT NULL,
utbildningsomr varchar(30) NOT NULL, drivhusort varchar(25) NOT NULL,
date timestamp(14) NOT NULL
);
CREATE TABLE bransch (
id int(11) UNSIGNED AUTO_INCREMENT PRIMARY KEY, bransch varchar(50) NOT NULL
);
CREATE TABLE ide (
ideid int(11) UNSIGNED AUTO_INCREMENT PRIMARY KEY, namn varchar(50) NOT NULL,
typ varchar(8) NOT NULL,
bransch varchar(30) NOT NULL,
beskrivning text NULL,
reg varchar(3) NOT NULL,
bolag varchar(16) NULL,
Web-based database solution
- 24 -
orgnr int(20) UNSIGNED NULL, datum int(8) UNSIGNED NULL, anstallda int(3) UNSIGNED NULL, omsattning varchar(20) NULL, hemsida varchar(80) NULL,
ort varchar(30) NOT NULL,
date timestamp(14) NULL );
CREATE TABLE ideperson (
id int(11) UNSIGNED AUTO_INCREMENT PRIMARY KEY, pid int(11) UNSIGNED NOT NULL,
iid int(11) UNSIGNED NOT NULL );
CREATE TABLE kategori (
id int(11) UNSIGNED AUTO_INCREMENT PRIMARY KEY, kategori varchar(60) NOT NULL
);
CREATE TABLE malgrupp (
id int(11) UNSIGNED AUTO_INCREMENT PRIMARY KEY, malgrupp varchar(50) NOT NULL
);
CREATE TABLE ort (
id int(11) UNSIGNED AUTO_INCREMENT PRIMARY KEY, ort varchar(30) NOT NULL
);
CREATE TABLE users (
id int(11) UNSIGNED AUTO_INCREMENT PRIMARY KEY, username varchar(20) NOT NULL,
password varchar(50) NOT NULL );
CREATE TABLE utbildningsomr (
id int(11) UNSIGNED AUTO_INCREMENT PRIMARY KEY, utbildningsomr varchar(50) NOT NULL
);
CREATE TABLE verksamhet (
verkid int(11) UNSIGNED AUTO_INCREMENT PRIMARY KEY, namn varchar(50) NOT NULL,
kategori varchar(50) NOT NULL, malgrupp varchar(50) NOT NULL,
kommentar text NULL,
narvarande int(11) UNSIGNED NOT NULL, trycksaker int(11) UNSIGNED NOT NULL, ovrigexpo varchar(100) NULL, totalexpo int(11) UNSIGNED NOT NULL, ledare varchar(80) NOT NULL,
Web-based database solution
- 25 -
ort varchar(30) NOT NULL,
date timestamp(14) );
CREATE TABLE personmote (
id int(11) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
pid varchar(100) NOT NULL,
motesdatum varchar(100) NOT NULL, moteskommentar varchar(100) NOT NULL );
5.4 Manuals
According to the requirements Drivhuset gave us, we made two manuals. One for the users where we describe every little step you take when you add, alter and remove database entries. The other manual is intended for the administrator and contains the user manual plus a listing of all the files our tool uses. The listing of files contains brief descriptions of the files, since we reckon that a person owning a web hotel that supports PHP and MySQL, among other features, does not need as fundamental explanations on how to make changes.
6 Result
The finished database solution that we created according to Drivhusets’ requirements met their expectations and requests when it comes to both design and functionality.
The result of our work is a web interface that is easy to use, where Drivhuset can put information and statistics about their activities together. They can also enter data about the students that come to Drivhuset and their ideas and projects, to get a statistic output of this information too. Not only can they benefit from the ability to put the actual statistics together, they will also use the tool to easily view and manage their data.
Earlier, Drivhuset used Microsoft Excel to input information about all of their students and acitivites, but now all they have to do is to use our tool and export the statistics of their choice to Excel. They now no longer only have access to view and get statistics about the students and activities in their own Drivhus city, but they can retrieve statistics from any Drivhus in Sweden. This is now possible due to their new mutual way of handling data.
“We are convinced that the database will be invaluable for Drivhuset Sweden, thanks to the work the students did, we can now measure our activities in a mutual way.”
Mariah ben Salem Drivhuset Gothenburg
Through continous meetings along the project we got feedback and suggestions on
changes to make. We then designed the site with relatively free hands and the simple,
Web-based database solution
- 26 -
clean layout we used was much appreciated. Drivhuset encouraged us to come up with new additions and suggestions on improvements.
The fact that Trollhättan has got its own Drivhus made things easier since we relatively easy could talk to Marie Heddelin about problems and ideas.
”The work with Drivhusets new statistic database has progressed excellently and as planned. The students have sticked to the time plan and collaborated well with Drivhusets’
project leaders. In addition, the students have come up with proposals to solutions on their own.”
Mariah ben Salem Drivhuset Gothenburg
We are satisfied with the site and its functionality since we managed to achieve the requirements and requests Drivhuset had.
7 Conclusion
The goal of our project was to create a web interface that is going to be used by all Drivhus’ in Sweden since they need to be able to put statistics together in a common way, because as it is now, each Drivhus city uses different routines.
The tool we created is web-based and reachable from any computer with an Internet connection.
We have made the desired restrictions possible; for example Drivhuset in Gothenburg can only edit and delete information entered by them.
Since not all persons who meet with Drivhuset want their social security number to be entered into the database, an important feature had to be removed, definitive unique identification. The solution was to use e-mail addresses instead. This makes flaws possible, but Drivhuset didn’t reckon it would cause any problems since it’s only a small group of people adding persons to the database.
A recommendation to further work on the solution is to create a policy where users need to be added with their social security number as the unique identifier since Drivhuset today has got about 90.000 persons registered. This means about 10.000 a Drivhus city.
Using social security numbers instead of e-mail addresses would prevent doubles being
added to the database since there might be many Anders Svensson’s, but they all have
unique social security numbers.
Web-based database solution
- 27 -
References
1 David M. Kroenke (2000) Database Processing – Fundamentals Design &
Implementation, UK: Prentice-Hall
2 Ben Forta (2000), SQL på 10 minuter, Malmö: PC Boken
3 Chris Chollo(1999), Professional PHP programming, UK: Wrox press 4 Refsnes Data, JavaScript Tutorial, [Electronic], www.w3schools.com
Availability:<http://www.w3schools.com/js/default.asp>[20030520]
5 MySQL, About MySQL AB [Electronic], www.mysql.com
Availability:<http://www.mysql.com/company/index.html>[20030520]
6 PHP, PHP,[Electronic], www.php.net Availability:<http://www.php.net>[20030520]
7 Adobe Photoshop Elements, Adobe Photoshop Elements [Electronic], www.adobe.com/products/photoshopel/main.html
Availability:<http://www.adobe.com/products/photoshopel/main.html >[20030520]
8 ConTEXT, ConTEXT [Electronic], fixedsys.com/context/
Availability:<http://fixedsys.com/context/>[20030520]
9 MySQL-Front, MySQL-Front [Electronic], www.mysqlfront.de Availability:<http://www.mysqlfront.de >[20030520]
10 MySQL Control Center, MySQL Control Center [Electronic],
www.mysql.com/products/mysqlcc/
Availability:<http://www.mysql.com/products/mysqlcc/>[20030520]
11 LeechFTP, LeechFTP [Electronic], stud.fh-heilbronn.de/~jdebis/leechftp/
Availability:<http://stud.fh-heilbronn.de/~jdebis/leechftp/>[20030520]
Appendix A
Drivhuset Requirements
Background
Drivhuset Sweden has a need for a common tool to measure and put statistics together.
And it has great importance that we have a common way of measuring on all Drivhus.
To date all Drivhus have individual ways of putting together statistics which considerably complicates a common statistic platform.
Purpose
The purpose with this project is to create a common statistical foundation for all nine Drivhus’ in Sweden through this new tool.
Goal
To create a common statistical platform for Drivhuset Sweden To make a new tool to use for summarizing our common activity.
To create statistical tables for “student”, “idea/company” and “activity”.
Realization
This project is being realized by cooperation between the students Daniel Karlsson and Jörgen Skoog from HTU. Drivhuset takes the task to help the students out and to follow up on the work as it proceeds and inform about the content of the site. Drivhuset Sweden has put together a working group that consists of Mikael Kipowsky (Malmö), Marie Heddelin (HTU) and Mariah ben Salem (Göteborg) that will coordinate the work.
These persons will be available for questions during the project time. Drivhuset expects the project to be completed in the end of May 2003. The students will receive 10000:- when the project is finished. Drivhuset will compensate possible costs that will occur during this project.
Page contents
Drivhusets internal web site will be able to reach from a link on drivhuset.se.
Idea contents
Name of Idea/company Type: Product or Service Subject area
Organisation number (if it evolves into a company) Company form
Webpage
Person content Sex
Name Address
Social security number Phone
Cellular phone E-mail
Education area Drivhus city
Guidance meeting date
Activity
Name of activity Category Comments
Number of people attending the activity Number of distributed printed matter Person in charge of activity
Functions needed from the database Save
Search (whole or partial words numbers etc) Selection of various variables for export to Excel®
Export statistics to Microsoft® Excel®
Security Secure login
Only the administrator shall have access to all of the information belonging to all
Drivhus’. Each Drivhus can only alter their information, but view data entered by all
Drivhus’.
Handbook
Jörgen and Daniel will make a handbook for us users, that is easy to understand. They
should also make a manual for the administrator.
Appendix B
Manual
Manual till Drivhusets interna sidor
Gjord av Jörgen Skoog Daniel Karlsson
20030520
Manual till Drivhusets interna sidor
Innehållsförteckning
1 Start – Login ... viii 2 Huvudsidan ... viii 3 Lägg till... ix 3.1 Lägg till person...ix
3.1.1 Kontrollsida för lägg till person ... x
3.2 Idé/företag...xi
3.2.1 Kontrollsida för Idé/företag...xii
4 Sökning ... xiii 4.1 Sök idé/företag ...xiv
4.1.1 Idé/företag, sökresultat... xv
4.2 Sök person...xvi
4.2.1 Sök person, sökresultat...xvii
4.3 Skapa e-post-lista...xvii 4.4 Skapa en Excel-fil ...xvii
4.4.1 Spara filen ...xviii 4.4.2 Öppna/redigera...xviii
5 Redigera ... xix 5.1 Redigera person/lägg till mötesdatum... xx
5.1.1 Utför Redigera person/lägg till mötesdatum ... xxi
5.2 Redigera Idé/företag...xxii 6 Verksamhet ... xxiv 6.1 Lägg till verksamhet ... xxv 6.2 Sök verksamhet ... xxvi
6.2.1 Sökresultat Verksamhet ...xxvii 6.2.2 Redigera verksamhet...xxvii
