TM08 Integrating Database With A Website
TM08 Integrating Database With A Website
Version-I
Page 1 of 59 Ministry of Labor and Skills Integrate Database with a November, 2023
Author/Copyright website
Table of Contents
Acknowledgment ....................................................................................................................... 3
Acronym..................................................................................................................................... 4
Unit Two: Retrieve data from database and display on web pages ...................................... 40
Reference ................................................................................................................................. 58
Version-I
Page 2 of 59 Ministry of Labor and Skills Integrate Database with a November, 2023
Author/Copyright website
Acknowledgment
Ministry of Labor and Skills wish to extend thanks and appreciation to the many
representatives of TVT instructors and respective industry experts who donated their time
and expertise to the development of this Teaching, Training and Learning Materials
(TTLM).
Version-I
Page 3 of 59 Ministry of Labor and Skills Integrate Database with a November, 2023
Author/Copyright website
Acronym
CGI ------------------------------------------------ Common Gateway Interface
CPU ----------------------------------------------- Central Processing Unit
CSS ------------------------------------------------ Cascade Style Sheet
DBMS --------------------------------------------- Database Management System
DML----------------------------------------------- Data Manipulation Language
DQL ----------------------------------------------- Data Query Language
HTML --------------------------------------------- Hypertext Markup Language
HTTP ---------------------------------------------- Hypertext Transfer Protocol
HTTP ---------------------------------------------- Hypertext Transfer Protocol
IIS ------------------------------------------------ Internet Information Service
PHPH---------------------------------------------- Ypertext Preprocessor
RAM ---------------------------------------------- Random Access Memory
SEO------------------------------------------------ Search Engine Optimization
SQL------------------------------------------------ Structured Query Language
UAC ----------------------------------------------- User Access Control
URL ----------------------------------------------- Uniform Resource Locator
WWW --------------------------------------------- World Wide Web
Version-I
Page 4 of 59 Ministry of Labor and Skills Integrate Database with a November, 2023
Author/Copyright website
Introduction to the Module
This Module. We will see how databases can be connected to the Web, and the most
effective way of using the new technology to develop database applications. We will also
study the most commonly used approaches for creating Web databases, and discuss related
issues such as dynamic updating of Web pages in line with the changes in databases,
performance.
The reason is that the Object-oriented model is considered the most suitable for the storage,
organization and retrieval of large sets of Web documents.
Version-I
Page 5 of 59 Ministry of Labor and Skills Integrate Database with a November, 2023
Author/Copyright website
Unit One: website to database connection
This unit is developed to provide you the necessary information regarding the following content
coverage and topics
This unit will also assist you to attain the learning outcomes stated in the cover page.
Specifically, upon completion of this learning guide, you will be able to:
Version-I
Page 6 of 59 Ministry of Labor and Skills Integrate Database with a November, 2023
Author/Copyright website
1.1. Web servers, databases and server-side scripting languages
The WWW comprises software (e.g. Web servers and browsers) and data (e.g. Web sites). It
simply represents a (huge) set of information resources and services that live on the Internet.
Each Web site consists of a set of Web pages, which typically contain multimedia data (e.g.
text, images, sound and video). In addition, a Web page can include hyperlinks to other Web
pages which allow users (also called net surfers) to navigate through the Web of information
pages.
There are two types of Web pages: static and dynamic.
A. Static: An HTML document stored in a file is a typical example of a static Web page. Its
contents do not change unless the file itself is changed.
B. Dynamic: For a dynamic Web page, its contents are generated each time it is accessed.
As a result, a dynamic Web page can respond to user input from the browser by, for
example, returning data requested by the completion of a form or returning the result of a
database query. A dynamic page can also be customized by and for each user. Once a user
has specified some preferences when accessing a particular site or page, the information
can be recorded and appropriate responses can be generated according to those
preferences.
From the above, it can be seen that dynamic Web pages are much more powerful and
versatile than static Web pages, and will be a focus for developing Web database
applications.
When the documents to be published are dynamic, such as those resulting from queries to
databases, the appropriate hypertext needs to be generated by the servers. To achieve this, we
must write scripts that perform conversions from different data formats into HTML „on-the-
fly‟. These scripts also need to recognize and understand the queries performed by clients
through HTML forms and the results generated by the DBMS.
In short, a Web database application normally interacts with an existing database, using the
Web as a means of connection and having a Web browser or client program on the front end.
Typically such applications use HTML forms for collecting user input (from the client); CGI
(Common Gateway Interface, to be discussed later) to check and transfer the data from the
server; and a script or program which is or calls a database client to submit or retrieve data
from the database. The diagram below gives a graphical illustration of such a scenario. More
will be discussed in later parts of this chapter.
Version-I
Page 7 of 59 Ministry of Labor and Skills Integrate Database with a November, 2023
Author/Copyright website
Fig 1.1 Common Gateway Interface
Web database applications may be created using various approaches. However, there are a
number of components that will form essential building blocks for such applications. In other
words, a Web database application should comprise the following four layers (i.e.
components):
Browser layer
Application logic layer
Database connection layer
Database layer
Browser layer:
The browser is the client of a Web database application, and it has two major functions. First,
it handles the layout and display of HTML documents. Second, it executes the client-side
extension functionality such as Java, JavaScript, and ActiveX (a method to extend a
browser‟s capabilities).
Version-I
Page 8 of 59 Ministry of Labor and Skills Integrate Database with a November, 2023
Author/Copyright website
The three most popular browsers at the present are Mozilla Firefox (Firefox for short),
Google Chrome and Microsoft Edge.
All three browsers are graphical browsers. During the early days of the Web, a text-based
browser, called Lynx, was popular. As loading graphics over the Internet can be a slow and
time-consuming process, database performance may be affected. If an application requires a
speedy client and does not need to display graphics, then the use of Lynx may be considered.
All browsers implement the HTML standard. The discussion of HTML is beyond this
chapter, but you need to know that it is a language used to format data/documents to be
displayed on the Web.
Browsers are also responsible for providing forms for the collection of user input, packaging
the input, and sending it to the appropriate server for processing. For example, input can
include registration for site access, guest books and requests for information. HTML, Java,
JavaScript or ActiveX (for IE) may be used to implement forms.
Application logic layer
The application logic layer is the part of a Web database application with which a developer
will spend the most time. It is responsible for:
Collecting data for a query (e.g. a SQL statement).
Preparing and sending the query to the database via the database connection layer.
Retrieving the results from the connection layer.
Formatting the data for display.
Most of the application‟s business rules and functionality will reside in this layer. Whereas
the browser client displays data as well as forms for user input, the application logic
component compiles the data to be displayed and processes user input as required. In other
words, the application logic generates HTML that the browser renders. Also it receives
processes and stores user input that the browser sends.
Depending on the implementation methods used for the database application, the application
logic layer may have different security responsibilities. If the application uses HTML for the
front end, the browser and server can handle data encryption (i.e. a security measure to
ensure that data will not be able to be intercepted by unauthorized parties). If the application
is a Java applet and uses Java for the front end, then it itself must be responsible for adopting
transmission encryption.
Database connection layer
Version-I
Page 9 of 59 Ministry of Labor and Skills Integrate Database with a November, 2023
Author/Copyright website
This is the component which actually links a database to the Web server. Because manual
Web database programming can be a daunting task, many current Web database building
tools offer database connectivity solutions, and they are used to simplify the connection
process.
The database connection layer provides a link between the application logic layer and the
DBMS. Connection solutions come in many forms, such as DBMS net protocols, API
(Application Programming Interface [see note below]) or class libraries, and programs that
are themselves database clients. Some of these solutions resulted in tools being specifically
designed for developing Web database applications. In Oracle, for example, there are native
API libraries for connection and a number of tools, such as Web Publishing Assistant, for
developing Oracle applications on the Web.
The connection layer within a Web database application must accomplish a number of goals.
It has to provide access to the underlying database, and also needs to be easy to use, efficient,
flexible, robust, reliable and secure. Different tools and methods fulfill these goals to
different extents.
Note
An API consists of a set of interrelated subroutines that provide the functionality required to
develop programs for a target operating environment. For example, Microsoft provides
different APIs targeted at the construction of 16- and 32-bit
Windows applications. An API would provide functions for all aspects of system activity,
such as memory, file and process management. Specialized APIs are also supplied by
software vendors to support the use of their products, such as database and network
management systems.
Database layer
This is the place where the underlying database resides within the Web database application.
As we have already learned, the database is responsible for storing, retrieving and updating
data based on user requirements, and the DBMS can provide efficiency and security
measures.
In many cases, when developing a Web database application, the underlying database has
already been in existence. A major task, therefore, is to link the database to the Web (the
connection layer) and to develop the application logic layer.
Version-I
Page 10 of 59 Ministry of Labor and Skills Integrate Database with a November, 2023
Author/Copyright website
Database gateways
A Web database gateway is a bridge between the Web and a DBMS, and its objective is to
provide a Web-based application the ability to manipulate data stored in the database. Web
database gateways link stateful systems (i.e. databases) with a stateless, connectionless
protocol (i.e. HTTP). HTTP is a stateless protocol in the sense that each connection is closed
once the server provides a response. Thus, a Web server will not normally keep any record
about previous requests. This results in an important difference between a Web-based client-
server application and a traditional client-server application:
There are a number of different ways to create Web database gateways. Generally, they can
be grouped into two categories: client-side solutions and server side solutions,
Client-side solutions
The client-side solutions include two types of approaches for connections: browser
extensions and external applications.
Browser extensions are add-ons to the core Web browser that enhance and augment the
browser‟s original functionality. Specific methods include plug-ins for Firefox, Chrome and
IE, and ActiveX controls for IE. Also, all the three types of browsers (Firefox, Chrome and
IE) support Java and JavaScript languages (i.e. Java applets and JavaScript can be used to
extend browsers‟ capabilities).
External applications are helper applications or viewers. They are typically existing database
clients that reside on the client machine and are launched by the Web browser in a particular
Web application. Using external applications is a quick and easy way to bring legacy
database applications online, but the resulting system is neither open nor portable. Legacy
database clients do not take advantages of the platform independence and language
independence available through many Web solutions. Legacy clients are resistant to change,
Version-I
Page 11 of 59 Ministry of Labor and Skills Integrate Database with a November, 2023
Author/Copyright website
meaning that any modification to the client program must be propagated via costly manual
installations throughout the user base.
Server-side solutions
Server-side solutions are more widely adopted than the client-side solutions. A main reason
for this is that the Web database architecture requires the client to be as thin as possible. The
Web server should not only host all the documents, but should also be responsible for dealing
with all the requests from the client.
Displaying image.
Executing plug-ins.
Version-I
Page 12 of 59 Ministry of Labor and Skills Integrate Database with a November, 2023
Author/Copyright website
1.2. User Identification and site data technical requirements.
The Data are collected based on the requirements of the website. Every website will have
different requirements and the data are collected based on the same. Different ways to
retrieve data for a website are via algorithms, data structure and complexity analysis.
Gathering data depends on the website, for small websites it takes lesser time; it also depends
upon the client requirements. Data should be collected based on some certain criteria
Business Objective
Website Design
Website Features
Website Layout
SEO / Search Engine Optimization Strategy
Requirements are collected based on the objective what is this project about and how to meet
the goal. The basic features and functionality helps in collecting the data required for the
project. For example, this may have e-commerce capabilities, support multiple languages, or
have a business directory listing. The next factor that helps in the data collection is
deliverables this includes how wireframes and mock-ups/structural model will be delivered,
timeframe/project plan (including project reporting aspects), how communications will be
handled, and quality assurance processes and standards. Smaller projects may not need a
wireframe or project plan (a projected deployment date will do), and quality assurance may
be as simple as discussing browser and device type support.
1.3. Web Server Installation.
Web Server Definition
A web server is a computer system capable of delivering web content to end
users over the internet via a web browser.
How web servers work
The end user processes a request via a web browser installed on a web server. The
communication between a web server or browser and the end user takes place using
Hypertext Transfer Protocol (HTTP). The primary role of a web server is to store, process,
and deliver requested information or webpages to end users. It uses:
Physical Storage: All website data is stored on a physical web server to ensure its
safety. When an end user enters the URL of your website or searches it using a
Version-I
Page 13 of 59 Ministry of Labor and Skills Integrate Database with a November, 2023
Author/Copyright website
keyword on a browser, a request is generated and sent to the web server to process the
data.
Web browser: The role of web browsers such as Firefox, Chrome, or Internet
Explorer is to find the web server on which your website data is located. Once the
browser finds your server, it reads the request and processes the information.
A web server‟s primary role is to serve web pages for a website. A web page can be rendered
from a single HTML file, or a complex assortment of resources fitted together. If you want to
host your web application on the internet, in many cases you will need a web server.
One of the most common use cases for web servers is serving files necessary for rendering a
website in a browser. When you visit http://www.google.com, you begin with entering a
URL that starts a request over the internet. This request passes through multiple layers, one or
more of which will be a web server. This web server generates a response to your request,
which in this case is the Google website, specifically the homepage. Ideally, this happens
quickly and with 24/7 availability.
Web servers act as an intermediary between the backend and the frontend, serving up
resources. While the term “web server” can refer to either the software itself or the hardware
it exists on. A web server handles requests on the internet through HTTP and HTTPS
protocol, and is also called an HTTP server. A web server is distinct from other types of
servers in that it specializes in handling these HTTP and HTTPS requests, differentiating
itself from application servers and servers for other protocols
Version-I
Page 14 of 59 Ministry of Labor and Skills Integrate Database with a November, 2023
Author/Copyright website
Enables browser caching for your static content.
In practical terms, here are some personal projects that would involve a web server:
You want to make an app that connects to the internet. This list is by no means
comprehensive, and a web server is not strictly limited in the data types it can serve to
an end user. For example, a web server that serves web API requests often responds
with data in a format such as JSON.
Web servers cater to an audience with expectations of speed, availability, reliability, and
more. They have a shared purpose of serving content on the internet, and in order to be
considered a viable web server solution, the following aspects must be considered:
Uptime: This refers to the time a web server is online and operational. Websites need
to be online at all times to serve users, so a high uptime is the goal. This also
translates to stability and predictability. When a user enters a URL or clicks a link to
your website, the expected page should load every time, and at any given time. The
only exceptions should be planned downtimes for updates or maintenance. A web
server that is buggy or crashes at random times adversely affects your users‟
experience.
Speed: Your web pages should load as fast as possible. Users want their request
fulfilled immediately, otherwise you risk losing them. On a slow loading web page,
even if the user sits through the first load, every subsequent long load will
exponentially decrease their willingness to stay or visit again.
Concurrency: This refers to the handling of multiple requests coming in at the same
time. Having too many people trying to visit your website at once seems like a good
thing, but this becomes a real problem when load times slow down to a crawl and
your whole server crashes. Your physical or virtual server only has so many resources
such as RAM and CPU compute power, and web servers must use these resources
efficiently.
Version-I
Page 15 of 59 Ministry of Labor and Skills Integrate Database with a November, 2023
Author/Copyright website
Scalability: Scalability refers to either making your existing servers more powerful
through vertical scaling, or adding more servers to your setup through horizontal
scaling. As you grow your audience, you may reach a point where you need more
than one or two small web servers.
Ease of set up: Getting a project up and running quickly is key to the iteration of your
project. A straightforward and repeatable install process is important for the first web
server you set up, and the multiple web servers afterwards when you scale up.
Documentation: Web servers are complex. The most common setups will get you on
your feet quickly, but your needs will grow over time. Oftentimes you will need
features that are not as commonly used. When that time comes, good documentation
is essential to creating custom solutions for your needs.
Developer support: If the core developers are not committed to their own project,
you shouldn‟t commit your project to theirs. This includes both plans for long term
support for their software, along with immediate short term support they provide in
the form of bug fixes and patches.
Community support: A core development team will handle most of the heavy lifting,
but a thriving community contributes to filling in the gaps. With open source projects,
this can mean contributions to the actual code base, but a strong community will also
answer your questions and help with your specific issues.
While web servers can offer different solutions, the solutions they offer stem from attempts to
address these same problems. These problems themselves evolve over time along with the
needs and expectations of the end user, making this a living and ever evolving list.
Web servers are primarily used to process and manage HTTP/HTTPS requests
and responses from the client system.
A web server can also perform several other functions, such as:
Store and protect website data: A web server can store and protect critical website
data from unauthorized users.
Version-I
Page 16 of 59 Ministry of Labor and Skills Integrate Database with a November, 2023
Author/Copyright website
Control bandwidth to regulate network traffic: A web server can help eliminate
the downtime caused by high web traffic. Web hosts can set bandwidth to manage the
rate of data transmission over the internet and minimize the excess network traffic.
Server-side web scripting: The server-side web scripting feature enables users to
create dynamic web pages using scripting languages such as Ruby, Python, and PHP.
Virtual hosting: Web servers can also be used as virtual servers to run multiple
applications, websites, data, and other services.
Apache web server software: Apache web server or Apache HTTP server is an
open-source server that processes user requests and delivers web assets and content
via HTTP. This web server uses the MySQL database to store critical information in
an easily readable format. With the help of the PHP programming language, Apache
can create and serve dynamic web content.
IIS web server software: Microsoft Internet Information Service (IIS) web server is
also known as a Windows web server. It‟s one of the most commonly used web
servers used on a Windows operating system. It is a versatile and stable web server
widely used to host ASP.NET web applications, static websites, and web applications
built on PHP. Although it has a built-in authentication option such as Windows,
ASP.NET, and Basic, it‟s easier for Windows users to sign in to various web
applications using their domain account. Other built-in security features include TLS
certificate management, request logging, FTP-specific security options, and more.
Linux web server software: Linux server is built on an open-source Linux operating
system that enables you to deliver content, applications, and services to end users.
Linux servers are flexible, consistent, and high-performing servers with snapshot
capabilities, optimized security, and scalable cloud technologies. These servers help
address the increasing requirements of web services, applications, database
management, and more.
NGINX web server software: NGINX is a popular open-source web server that runs
and utilizes resources efficiently. It can handle huge volumes of traffic. It offers
reverse proxy, HTTP caching services, email proxy, and load balancing. NGINX is a
Version-I
Page 17 of 59 Ministry of Labor and Skills Integrate Database with a November, 2023
Author/Copyright website
scalable, lightweight, and powerful web server capable of handling concurrent
connections and is ideal for delivering static content.
Web server vs. application server differences
Web server: The web server accepts and processes requests from end users for static
website content. It handles requests and responses via HTTP only. Web servers are
generally helpful in serving static content or static HTML webpages. It consumes
fewer resources such as CPU or memory compared to the application server and
provides a runtime environment for web applications.
Application server: The application server can deliver web content and dynamic
content required for displaying decision support, transaction results, or real-time
analytics. However, its primary role is to enable interaction between the end user and
server-side application code. These servers enhance interactive content or website
components depending on the request. Application servers use web containers. These
servers use more resources compared to web servers and provide the runtime
environment for enterprise applications.
Optimizing a web server requires regular monitoring of web and application servers. Outlined
below are the few benefits of monitoring and optimizing your server:
Helps solve critical problems quickly: It‟s crucial to monitor web and application
servers to ensure availability and performance. Monitoring web servers provides vital
insights into application pools (worker processes, cache, and requests), connections
(current and total connections), websites (network, files), cache (memory usage, file
cache).
Optimize infrastructure resources: It helps to understand key performance metrics,
website load, so you can effectively use infrastructure resources such as CPU
utilization, network traffic, disk capacity, and more. It also provides crucial insights,
such as client connections, web server traffic and status, server load.
Version-I
Page 18 of 59 Ministry of Labor and Skills Integrate Database with a November, 2023
Author/Copyright website
Prepare your database user account details.
Database systems use accounts, with specific levels of access to each user.
Account details should include a username and password. Locate these details,
if necessary copying them into a file.
Connect to database.
One or more server side scripts to connect to your database. The process for
making a connection is similar for other database systems and programming
languages.
Query data
In most cases scripts use SQL (Structured Query Language) to retrieve
specific sets of data from databases. These SQL queries can execute from
inside a server side script.
Output data: Once data is retrieved from the database it can be represented in the a
structured form in HTML page
Test script
Once you have your database connection script complete, or partially complete, upload it to
your server to test it. Once you have your database connection script complete, or partially
complete, upload it to your server to test it. If you encounter errors, check your database
account details as well as the structure of your tables.
Template-driven packages
A template driven database access package, is another way to link a Web front
end to a database back end.
The approach
Version-I
Page 19 of 59 Ministry of Labor and Skills Integrate Database with a November, 2023
Author/Copyright website
Template-driven database connectivity packages are offered by database vendors and third-
party developers to simplify Web database application programming. Such a package usually
consists of the following components:
The most important benefit from using a template-driven package is speed of development.
Assuming an available package has been installed and configured properly, it takes as little
time as a few hours to create a Web site that displays information directly from the database.
The structures of templates are normally predetermined by vendors or third party developers.
As a result, they only offer a limited range of flexibility and customis ability. Package
vendors provide what they feel is important functionality, but, as with most off-the-shelf
tools, such software packages may not let you create applications requiring complex
operations.
The actual performance of an application should be tested and evaluated before the
usefulness of such a package is ruled out. The overhead of parsing templates may be
Version-I
Page 20 of 59 Ministry of Labor and Skills Integrate Database with a November, 2023
Author/Copyright website
negligible if using high-performance machines. Other factors, such as development time or
development expertise, may be more important than a higher operational speed.
The architectures of visual building tools vary. In general, they include a user friendly GUI
(Graphical User Interface), allowing developers to build a Web database application with a
series of mouse clicks and some textual input. These tools also offer application management
so that a developer no longer needs to juggle multiple HTML documents and other API
programs manually. At the end of a building session, the tool package can generate
applications using various techniques. Some applications are coded using ODBC; some use
native database APIs for the databases they support; and others may use database net
protocols.
Some of these tools create their own API, which can be used by other developers. Some
generate code that works but can still be modified and customized by developers using
various traditional IDEs, compilers and debuggers. A building tool may generate a CGI
program or a Web server API program (such as NSAPI and ISAPI). Some sophisticated tools
even offer all the options. The developer can choose what he/she wants.
Unlike native database APIs or template-driven database connectivity packages, visual Web
database development tools tend to be as open as possible. Many offer development support
for the popular databases.
Visual development tools can be of great assistance to developers who are familiar and
comfortable with visual application development techniques. They offer rapid application
development and prototyping, and an organized way to manage the application components.
Visual tools also shield the developer from low-level details of Web database application
Version-I
Page 21 of 59 Ministry of Labor and Skills Integrate Database with a November, 2023
Author/Copyright website
development. As a result, a developer can create a useful Web application without the need
to know what is happening in the code levels.
Shortcomings of visual tools
Depending on the sophistication of the package used, the resulting programs may be slower
to execute than similar programs coded by an experienced programmer. Visual application
building tools, particularly Object-oriented ones, tend to generate fat programs with a lot of
unnecessary sub-classing.
Another potential drawback is cost. A good visual tool may be too expensive for a small one-
off development budget.
HTML are markup languages, basically they are set of tags like <html>, <body>, which is
used to present a website using css, and javascript as a whole. All these, happen in the clients
system will be browsing the website. Now, Connecting to a database, happens on whole
another level. It happens on server, which is where the website is hosted. So, in order to
connect to the database and perform various data related actions, you have to use server-side
scripts, like php, jsp, asp.net etc.
MySQL is a popular open source database management system commonly used in web
applications due to its speed, flexibility and reliability. MySQL employs SQL, for accessing
and processing data contained in databases. Simple web applications can be designed using a
two-tier architecture, in which a client communicates directly with a server. A web
application communicates directly with a MySQL database using the Database Connectivity
API. Essentially, it is the MySQL Connector and web application Driver that enables
communication between the website code understood by the application server, and any
content in SQL.
First of all, you must install any web server or Web Server Applications like XAMPP or
WAMP(Windows) or MAMP (Mac OS) kind of software on your computer to get a local
webserver i.e. Apache, PHP language, and MySQL database.
Steps to connect the web application to the database contains the following steps
Step 1: Design your Database and HTML form requirements for your web page
Step 2: Create a database and a table in MySQL
Step 3: Create HTML form for connecting to database
Step 4: Create a PHP page to save data from HTML form to your MySQL database
Step 5: All done!
Version-I
Page 22 of 59 Ministry of Labor and Skills Integrate Database with a November, 2023
Author/Copyright website
Self-check 1
Part-I: Fill the blank space
Version-I
Page 23 of 59 Ministry of Labor and Skills Integrate Database with a November, 2023
Author/Copyright website
Operation sheet 1.1. Integrating database with a website
Operation Title: connecting the web application to the database
Purpose: Generate and execute a new database file to test and understand basic database
syntax.
Computer
Windows 10 and above.
Version-I
Page 24 of 59 Ministry of Labor and Skills Integrate Database with a November, 2023
Author/Copyright website
XAMPP & PHP most recent file versions.
Note: If you‟re using Linux or OSX, the installation steps are pretty much the same. Just
make sure to download the appropriate version of XAMPP for your OS.
Step 2: Install XAMPP
Once downloaded, run the XAMPP installer file.
You might get a UAC warning before installation. Click OK and continue.
Select the components you want to install. If you‟re planning to install a WordPress site with
XAMPP, you only need Apache, MySQL, PHP, and phpMyAdmin. I‟ll check all the
components as I‟d like to experiment with them later.
Version-I
Page 25 of 59 Ministry of Labor and Skills Integrate Database with a November, 2023
Author/Copyright website
Choose the installation directory for XAMPP (default recommended).
Uncheck Learn more about Bitnami option. Bitnami provides all-in-one tools to install
popular open source apps on top of XAMPP. This includes add-on modules for installing
Word Press too. However, we‟ll be installing it manually here.
Version-I
Page 26 of 59 Ministry of Labor and Skills Integrate Database with a November, 2023
Author/Copyright website
You may be presented with a Windows Security Alert at the end of the installation. You need
to whitelist Apache HTTP Server from your Windows Defender Firewall by clicking the
Allow access button. Make sure to check the “Private networks, such as my home or work
network” option. This is very important. XAMPP won‟t work if you don‟t check this and
click Allow access.
The XAMPP Control Panel sports a simple user interface that lists all the modules of your
local server. It allows you to Start/Stop individual modules, access their Admin area, Config
files, and Logs with just a single click. Its bottom section also displays all your actions and
errors (if any).
Version-I
Page 27 of 59 Ministry of Labor and Skills Integrate Database with a November, 2023
Author/Copyright website
Step 4: Start Apache and MySQL Modules
Click the Start button beside Apache module. If everything‟s set correctly, your Apache
server should start successfully under ports 83 and 443.
You can access your Apache server‟s dashboard by clicking the Admin button beside it.
Alternatively, you can also reach it via http://localhost/dashboard/ URL in your browser.
Version-I
Page 28 of 59 Ministry of Labor and Skills Integrate Database with a November, 2023
Author/Copyright website
Then Start the MySQL module. If you‟re presented with a Windows Security Alert to
whitelist mysqld.exe, click Allow access. Like before, make sure that you‟ve ticked the
“Private networks…” option.
Version-I
Page 29 of 59 Ministry of Labor and Skills Integrate Database with a November, 2023
Author/Copyright website
Don‟t forget to tick the “Private networks” option. You can access your phpMyAdmin
dashboard by clicking the Admin button beside MySQL module. Or you can simply go to
http://localhost/phpmyadmin/ in your browser. Here, you can manage the MariaDB (or
MySQL) databases of your web projects.
Version-I
Page 30 of 59 Ministry of Labor and Skills Integrate Database with a November, 2023
Author/Copyright website
The phpMyAdmin Dashboard
This concludes the setup of XAMPP as your local development environment. It‟s now ready
to host any PHP-based software (e.g. Word Press).
The best way to check whether your local server has been installed and configured correctly
is to create a PHP test page, place it in XAMPP‟s local host folder, and then try accessing it
via your browser.
Let‟s do that now. Create a new folder called test in your C:\xampp\htdocs\ directory. This
directory can also be accessed easily by clicking the Explorer button in XAMPP Control
Panel and then going to htdocs folder.
Version-I
Page 31 of 59 Ministry of Labor and Skills Integrate Database with a November, 2023
Author/Copyright website
Create a file called test.php with the code below and place it in C:\xampp\htdocs\test folder.
<html>
<head>
<title>PHP-Test</title>
</head>
<body>
</body>
</html>
Version-I
Page 32 of 59 Ministry of Labor and Skills Integrate Database with a November, 2023
Author/Copyright website
If IIS is not installed by default, you can download and install the latest version of IIS
Express for your Windows 10 or Windows 11 OS from the Microsoft website. IIS versions
later than 7.0 include the new IIS Manager User interface.
Version-I
Page 33 of 59 Ministry of Labor and Skills Integrate Database with a November, 2023
Author/Copyright website
4. Wait for the installation to complete and select Close.
5. If you plan on using integrated Windows authentication in website hosted in IIS, tick the
Windows Authentication option under Internet Information Services > World Wide Web
Services > Security as well and select OK.
1. You can now install and test your website on IIS. After installation, you will see your
web console listed in IIS Manager (inetmgr), along with a default website generated
when you enabled IIS.
Step 1: Design your Database and HTML form requirements for your web page
Filter your HTML form requirements for your contact us web page
Step 1: Filter your HTML form requirements for your contact us web page
Version-I
Page 34 of 59 Ministry of Labor and Skills Integrate Database with a November, 2023
Author/Copyright website
Suppose you selected the form field Name (text input), Email (email input), Phone (number
input), and message (multi-line text). The form submit button also necessary for submitting
the form. You will get the complete form in HTML coding in step 3.
USE `db_Name`;
ALTER TABLE
Example
USE `db_contact`;
2b. you can use your web server applications to create the Database
Version-I
Page 35 of 59 Ministry of Labor and Skills Integrate Database with a November, 2023
Author/Copyright website
Open a web browser (chrome, Firefox, edge, etc.,) and type this http://localhost/phpmyadmin/
or http://127.0.0.1/phpmyadmin/ for open GUI for managing DB on your computer. See the
xampp screen below how it is coming.
E.g. click on the databases new link and create your db by the name “db_contact”.
Now you have to create an HTML form. For this, you need to create a working folder first
and then create a web page with the name “contact.html”. If you install xampp your working
folder is in folder this “E:\xampp\htdocs”. You can create a new folder “contact” on your
local host working folder. Create a “contact.html” file and paste the following code.
<html>
<head>
</head>
<body>
<fieldset>
Version-I
Page 36 of 59 Ministry of Labor and Skills Integrate Database with a November, 2023
Author/Copyright website
<legend>Contact Form</legend>
<p>
</p>
<p>
<label for="email">Email</label>
</p>
<p>
<label for="phone">Phone</label>
</p>
<p>
<label for="message">Message</label>
</p>
<p> </p>
<p>
</p>
</form>
</fieldset>
Version-I
Page 37 of 59 Ministry of Labor and Skills Integrate Database with a November, 2023
Author/Copyright website
</body>
</html>
Now your form is ready. You may test it in your localhost link
http://localhost/contact/contact.html
For storing data in MySQL as records, you have to first connect with the DB. Connecting the
code is very simple. The mysql_connect in PHP is deprecated for the latest version therefore
I used it here mysqli_connect.
You need to place value for your localhost username and password. Normally localhost
MySQL database username is root and password blank or root. For example, the code is as
below
The “db_contact” is our database name that we created before. Then you will save the file
with “contact.php”
Quality Criteria: The web page should display the output of your database.
Version-I
Page 38 of 59 Ministry of Labor and Skills Integrate Database with a November, 2023
Author/Copyright website
Lap Tests
Task 1: Create database table
Version-I
Page 39 of 59 Ministry of Labor and Skills Integrate Database with a November, 2023
Author/Copyright website
Unit Two: Retrieve data from database and display on web pages
This unit is developed to provide you the necessary information regarding the following content
coverage and topics
This unit will also assist you to attain the learning outcomes stated in the cover page.
Specifically, upon completion of this learning guide, you will be able to:
Version-I
Page 40 of 59 Ministry of Labor and Skills Integrate Database with a November, 2023
Author/Copyright website
2.1. Retrieve data using structured query language (SQL)
Structured Query Language (SQL) is a database language designed for managing data held in
a relational database management system. SQL was initially developed by IBM in the early
1970s (Date 1986). The initial version, called SEQUEL (Structured English Query
Language), was designed to manipulate and retrieve data stored in IBM‟s quasi-relational
database management system, System R. Then in the late 1970s, Relational Software Inc.,
which is now Oracle Corporation, introduced the first commercially available
implementation of SQL, Oracle V2 for VAX computers.
Version-I
Page 41 of 59 Ministry of Labor and Skills Integrate Database with a November, 2023
Author/Copyright website
Step 1: Create a PHP page to save data from HTML form to your MySQL database
The contact HTML form action is on “contact.php” page. On this page, you can continue to
write code on the contact.php which was created for connecting the database with website,
now we insert records into the database.
Inserting data into your database from a form
HTML form: First we create an HTML form that need to take user input from keyboard.
HTML form is a document which stores information of a user on a web server using
interactive controls. An HTML form contains different kind of information such as username,
password, contact number, email id etc.
The elements that are used in an HTML form are check box, input box, and radio buttons,
submit buttons etc. With the help of these elements, the information of a user is submitted on
the web server. The form tag is used to create an HTML form.
Syntax:
To pass the values to next page, we use the page name with the following syntax. We can use
either GET or POST method to sent data to server.
Version-I
Page 42 of 59 Ministry of Labor and Skills Integrate Database with a November, 2023
Author/Copyright website
2.2. Display data with web page
All done! Browse your webpage.
Now the coding part is done. If you would like to check then you can fill the form
http://localhost/contact/contact.html and see the result in the database. You may check via
phpmyadmin your inserted record and also you can format styles or reorganize your
webpages if the outlooks are not full enough.
Other template based webpages can show a list of open datasets and their various feature
types. These components are displayed as a hierarchy of levels that can be turned on or off,
from entire datasets down to individual feature types. Data List and Repeater controls are
used to alter the layout of data source records in the data List, common master/details
scenarios, ways to edit and delete data, how to page through records, and so on.
The steps involved maintaining the data list and repeater controls are
Add them to the web page
Display information with web page
Improve the appearance to the data list
Exploring the data list and other templates
Rendering specific markup with repeater control
Improving appearance of repeater
Version-I
Page 43 of 59 Ministry of Labor and Skills Integrate Database with a November, 2023
Author/Copyright website
Self-check 2
Part-I: Fill the blank space
1. _______________________________ a database language designed for managing
data held in a relational database management system.
2. _____________________________retrieve the data based on specific criteria
3. The elements that are used in an HTML form are _________________,
____________, _____________etc.
4. DDL stands for ___________________
5. DQL Stands for__________________
1. List at least four different browsers that you might use to test a web application
2. Explain the different types of web programming concepts;
Hypertext Transfer Protocol (HTTP)
Authentication and Web Security
3. Describe what is a database structure?
4. What does SQL stand for?
Version-I
Page 44 of 59 Ministry of Labor and Skills Integrate Database with a November, 2023
Author/Copyright website
Operation sheet 2.1: PHP Database connection
Operation Title: connect Database with PHP
Purpose: establish a connection and interact with the database.
Requirements: XAMPP web server procedure:
Computer
Internet
database
Steps in Detail:
Now open your PHP file and write your PHP code to create database and a table in your
database.
<?php
$servername = "localhost";
$username = "root";
Version-I
Page 45 of 59 Ministry of Labor and Skills Integrate Database with a November, 2023
Author/Copyright website
// Password is empty
$password = "";
// Creating a connection
$username, $password);
// Check connection
if ($conn->connect_error) {
. $conn->connect_error);
} else {
// Closing connection
$conn->close ();
?>
Version-I
Page 46 of 59 Ministry of Labor and Skills Integrate Database with a November, 2023
Author/Copyright website
If you want to see your database, just type localhost/phpmyadmin in the web browser and the
database can be found.
After connection database you need to take post variable from the form. See the below
code
$txtName = $_POST['txtName'];
$txtEmail = $_POST['txtEmail'];
$txtPhone = $_POST['txtPhone'];
$txtMessage = $_POST['txtMessage'];
When you will get the post variable then you need to write the following SQL command.
For fire query over the database, you need to write the following line
Here is PHP code for inserting data into your database from a form.
<? Php
Version-I
Page 47 of 59 Ministry of Labor and Skills Integrate Database with a November, 2023
Author/Copyright website
// get the post records
$txtName = $_POST['txtName'];
$txtEmail = $_POST['txtEmail'];
$txtPhone = $_POST['txtPhone'];
$txtMessage = $_POST['txtMessage'];
// insert in database
if($rs)
?>
Quality Criteria: The web page should display database data and information about
variables of different data types.
Version-I
Page 48 of 59 Ministry of Labor and Skills Integrate Database with a November, 2023
Author/Copyright website
Lap Tests
Task one: Create a database for the cosmetics website.
Task two: Use data list and repeater to maintain the data in the cosmetics webpage database
Task three: Format the data the cosmetics webpage database to display the data in a good
appearance
Version-I
Page 49 of 59 Ministry of Labor and Skills Integrate Database with a November, 2023
Author/Copyright website
Unit Three: Database user input manipulation
This unit is developed to provide you the necessary information regarding the following content
coverage and topics
This unit will also assist you to attain the learning outcomes stated in the cover page.
Specifically, upon completion of this learning guide, you will be able to:
Version-I
Page 50 of 59 Ministry of Labor and Skills Integrate Database with a November, 2023
Author/Copyright website
3.1 Update existing data stored in the database
The SQL data manipulation language (DML) is used to query and modify database data.
How to use the SELECT, INSERT, UPDATE, and DELETE SQL DML command
statements, defined below.
The UPDATE statement lets the database system know that you wish to update the records
for the table specified in the table_name parameter
The columns that you want to modify are listed after the SET statement and are equated to
their new updated values. Commas separate these columns
The condition in the WHERE clause dictates which rows from the mentioned columns will
be updated.
Version-I
Page 51 of 59 Ministry of Labor and Skills Integrate Database with a November, 2023
Author/Copyright website
requiring information. In computer programming, create, read, update, and delete are the four
basic functions of persistent storage
Most application programs need to be connected to the database in order to do some basic
operations like saving and retrieving the user details / data.
<? php
$conn = mysql_connect('localhost','root','');
$db = mysql_select_db ('database_name', $conn);
?>
The code above will establish the connection to MySQL Sever. The UPDATE statement is
used to update existing records in a table, syntax for the same is
The Syntax for SQL UPDATE Command
UPDATE table_name
SET column1=value, column2=value2...
WHERE some_column=some_value
In the syntax shown below we see that the UPDATE procedure requires the SET and
WHERE definitions to pinpoint the changes.
An example program to implement the update query is as below:
<?php
$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "myDB";
// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);
// Check connection
if ($conn->connect_error) {
die("Connection failed: " . $conn->connect_error);
}
$sql = "UPDATE contact SET phone='091234' WHERE name=‟ABC‟;
if ($conn->query($sql) === TRUE) { echo "Record updated successfully";
} else {
Version-I
Page 52 of 59 Ministry of Labor and Skills Integrate Database with a November, 2023
Author/Copyright website
echo "Error updating record: " . $conn->error;
}
$conn->close();
?>
3.2 Insertion of data in the database.
After a database and a table have been created, we can start adding data in them. The
INSERT INTO statement is used to add new records to a MySQL table.
The syntax to insert data in the database
Insert Into table_name (column1, column2, column3,...) VALUES (value1, value2, value3,...)
// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);
// Check connection if
($conn->connect_error) {
die("Connection failed: " . $conn->connect_error);
}
$sql = "INSERT INTO MyGuests (firstname, lastname, email)
VALUES ('John', 'Doe', '[email protected]')";
Version-I
Page 53 of 59 Ministry of Labor and Skills Integrate Database with a November, 2023
Author/Copyright website
}
$conn->close();
?>
Multiple SQL statements must be executed with the mysqli_multi_query() function.
Example for the same is below:
<? php
$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "myDB";
// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);
// Check connection if
($conn->connect_error) {
die("Connection failed: " . $conn->connect_error);}
$sql = "INSERT INTO MyGuests (firstname, lastname, email)
VALUES ('John', 'Doe', '[email protected]');";
$sql .= "INSERT INTO MyGuests (firstname, lastname, email)
VALUES ('Mary', 'Moe', '[email protected]');";
$sql .= "INSERT INTO MyGuests (firstname, lastname,
email) VALUES ('Julie', 'Dooley', '[email protected]')"; if
($conn->multi_query($sql) === TRUE) {
echo "New records created successfully";
} else { echo "Error: " . $sql . "<br>" . $conn->error;
}
$conn->close ();
?>
3.3 Deletion of data in the database
The DELETE statement is used to delete records from a table.
Syntax is Delete the data
DELETE FROM table_name WHERE some_column = some_value
Example:
Version-I
Page 54 of 59 Ministry of Labor and Skills Integrate Database with a November, 2023
Author/Copyright website
<? php
$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "myDB";
// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);
// Check connection if
($conn->connect_error) {
die("Connection failed: " . $conn->connect_error);
}
// sql to delete a record
$sql = "DELETE FROM MyGuests WHERE id=3";
Version-I
Page 55 of 59 Ministry of Labor and Skills Integrate Database with a November, 2023
Author/Copyright website
}
The table name is 'Users' and the column to be searched is 'username'. The input from form
was read into $username and I verified that it was read in properly using echo. $con contains
the connection to the server.
Version-I
Page 56 of 59 Ministry of Labor and Skills Integrate Database with a November, 2023
Author/Copyright website
Self-check 3
Part-I: Answer the following questions accordingly
Learning PHP, MySQL & JavaScript: With jury, CSS & HTML5 Robin Nixon, 2015, 5th
Edition
PHP and MySQL Web Development: Second Edition By Luke Welling, Laura Thomson
URL
https://www.geeksforgeeks.org/what-is-database/
https://www.javatpoint.com/what-is-
databasehttps://www.techtarget.com/searchdatamanagement/definition/database
https://www.britannica.com/technology/database
https://opentextbc.ca/dbdesign01/chapter/chapter-sql-dml/
https://medium.com/@actualiyke/manipulation-of-user-input-851dabdac33e
http://localhost/contact/contact.html
Developer’s Profile
Mobile
No Name Qualification Field of Study Organization/ Institution E-mail
number
1 Frew Atkilt M-Tech Network & Bishoftu Polytechnic College 0911787374 [email protected]
Information Security
4 Solomon Melese M-Tech Computer Engineering M/G /M /Polytechnic College 0918578631 [email protected]
5 Tewodros Girma MSc Information system Sheno Polytechnic College 0912068479 girmatewodiros @gmail.com