Advanced Commercial Web Site Design: ServerSide Scripting

1
Advanced Commercial Web Site Design Server-Side
Scripting

• Session 9
• Database fundamentals

2
Outline

• Database
• Definition
• Reasons to use a database
• Types of database systems
• Supported database systems by PHP
• SQL basic statements
• SELECT
• INSERT
• UPDATE

3
Definition1

• A database is a collection of information
  organized in such a way that a computer program
  can quickly select desired pieces of data.
• A field is a single piece of information a
  record is one complete set of fields and a table
  is a collection of records.
• Storing content in fields rather than static
  pages makes that content appropriate for dynamic
  delivery.

• http//www.fatwire.com/services/D_cmglossary.html

4
Database elements
5
Reasons to use a database

• Searching
• Speed and performance on searching using simple
  statements.The equivalent search on hundreds (or
  even thousands) of files becomes an unrealistic
  solution.
• Redundancy
• Storing specific information at the database
  allows the programmer to have templates of
  web-pages, with their content obtained from the
  database. Therefore, instead of hundreds of
  static HTML pages, just a few PHP templates and
  content at the database will suffice.
• Security
• Storing information in files assumes that the
  file is readable/writeable by Anybody. Using a
  database adds another layer of security, having
  to first authenticate in order to get access to
  the content. Also, different permissions can be
  granted to different users.

6
Reasons to use a database (cont.)

• Multi-tier architecture
• Although in simple 2-tier architectures obtaining
  the content is trivial, in 3-tier or multi-tier
  architectures the database support is compulsory
  and a required component.Multi-tier
  architecture Assume a large e-commerce web site,
  that many software components need to communicate
  in order to accomplish the task, such as
• the shopping-cart with the system taking the
  orders
• the credit-card debiting system with bank APIs
• log analysis tools
• the customer and product databases
• Structure
• In a database the information is structured, even
  before it is entered to the system. The tables,
  records, fields need to be carefully considered
  before any piece of information is available for
  entry.

7
N-Tiers Architecture

• Advantage
• it separates out the processing that occurs to
  better balance the load on the different servers
  
• it is more scalable.
• Disadvantages
• It puts a greater load on the network.
• It is much more difficult to program and test
  software than in two-tier architecture because
  more devices have to communicate to complete a
  users transaction.

8
Types of databases

• Relational databases
• Probably the most commonly used type. The
  information is spread across multiple tables,
  linked by relational keys (IDs) (see page 3). PHP
  programmers are mainly using this kind of
  databases.
• Hashing databases
• Mostly used within other applications, such as
  email servers. They are not very smart, as the
  programmer should deal with most of the
  functionality of the database. Extremely fast for
  storing and searching specific information.
• Object-oriented and object-relational databases
• The future! Unfortunately PHP (ltver4) is not an
  object-oriented language and cannot take
  advantage of the functionality they offer.
  Primarily used by natively object-oriented
  languages.

9
Talking to the database

• Generic standard APIs
• Open Database Connectivity (ODBC) - Microsoft
• Java Database Connectivity (JDBC) - Sun
• Abstract API to connect, query and store
  information to a database.
• Open standard.
• Code to access a database via ODBC/JDBC does not
  need to be rewritten for a different
  ODBC/JDBC-compliant database. The drivers
  abstract the inner commands, providing the
  programmer with general commands.
• Slower than native API commands.
• Native API
• PHP native API
• Direct access to the database system specific to
  the programming language.
• Faster.

10
Supported database systems
11
SQL basics

• Structured Query Language
• A strict and general method for manipulating
  data.
• The structure of an SQL database contains
• Multiple databases
• Each database contains multiple tables
• Each table contains multiple records (rows) and
  each new entry is an added row to the table.
• The columns are carefully specified to a number
  of constraints.

12
SQL commands

• Two types of commands exist in SQL
• Those commands that create/modify the database
  structure
• Those commands that alter the values of the
  stored information
• Three (3) statements are mainly used for the
  manipulation of the database structure
• CREATE
• DROP
• ALTER
• Four (4) statements are mainly used for the
  manipulation of the data
• SELECT
• INSERT
• UPDATE
• DELETE

13
CREATE statement

• Create a new database called dataBank
• CREATE DATABASE dataBank
• Create a table called marks with three fields
  the ID, the surname of the student and its mark.
  The surname should be Text and the mark should be
  a float number. The ID should be an integer
  number, it should be auto increasing and be the
  primary key of the table.
• CREATE TABLE marks (
• id INT NOT NULL AUTO_INCREMENT PRIMARY
  KEY,
• surname TEXT NOT NULL,
• mark FLOAT
• )

14
DROP statement

• Delete a database called dataBank
• DROP DATABASE databank
• // Be VERY careful with this command!!!
• Delete a table called marks
• DROP TABLE marks

15
ALTER statement

• Modify the structure of a table called marks
• ALTER TABLE marks RENAME AS grades
• // Rename the table from marks to
  grades
• ALTER TABLE marks ADD COLUMN markedAt date
• // Add another field markedAt of type
  date
• ALTER TABLE marks DROP COLUMN surname
• // Delete the column surname

16
SELECT statement

• Obtain information from the database.
• SELECT field1, , fieldN FROM tableName WHERE
  condition
• Example
• SELECT surname, mark FROM grades WHERE mark gt
  15.0
• // It will return the surnames and marks of all
  the students with a mark greater than 15.0
• In order to get all the fields of the record, use
  the star ( ) instead of specific fields
• SELECT FROM grades WHERE mark gt 15.0

17
INSERT statement

• Add information to the database.
• INSERT INTO tableName (column1, ..., columnN)
  VALUES (value1, , valueN)
• Example
• INSERT INTO grades (surname, mark) VALUES
  (Cane, 17.2)
• // Add another student to the table grades
  with name Cane and mark of 17.2

18
UPDATE statement

• Edit the information already stored at the
  database.
• UPDATE tableName SET field1 value1 fieldN
  valueN WHERE condition
• Example
• UPDATE grades SET surname Rich WHERE id
  238
• // Find the record with id238 and change the
  value of the surname field to Rich

19
DELETE statement

• Remove information (records) from the database.
• DELETE FROM tableName WHERE condition
• Example
• DELETE FROM grades WHERE surnameCane
• // It will remove all the records from the
  table grades where the surname is equal to
  Cane.

20
Care with DELETE, UPDATE

• Always use the statements DELETE and UPDATE with
  extra care!
• It is advisable to always use the WHERE option on
  these statements, in order to minimize the effect
  of the statement. If this option is not used, all
  the records of the table will be affected.

21
Introduction to MySQL

• Open Source SQL database management system.
• Probably the cheapest, fastest, simplest and
  reliable database in the market.
• MySQL Server was originally developed to handle
  large databases much faster than existing
  solutions and has been successfully used in
  highly demanding production environments for
  several years. Although under constant
  development, MySQL Server today offers a rich and
  useful set of functions. Its connectivity, speed,
  and security make MySQL Server highly suited for
  accessing databases on the Internet.2

2. http//dev.mysql.com/doc/mysql/en/what-is.html
22
Web access to the database

• PHP front-end to MySQL server
• phpMyAdmin (see Useful Links at modules web
  page)
• URL http//www.till.me.uk/phpMyAdmin-2.6.2/index.
  php
• Database to use
• evbrack1_webw
• We will be working inside this database,
  creating tables and adding data. Every student
  will have its own tables, that should start with
  his/her initials, to avoid database conflicts and
  locks.
• For example, tables that belong to Gang Lu
  will look like
• gl_Marks, gl_Destinations, gl_Photos, etc.

23
Exercise 14.1

• Familiarize yourselves with the environment of
  phpMyAdmin.
• Identify the sections of the page that refer to
  databases, tables, fields, etc.
• Browse through our database evbrack1_webw and
  identify the tables already stored.
• Navigate to the table gl_marks and check its
  structure and records.

24
Exercise 14.2

• Create a new table in the database with the name
  initials_marks
• It should have the following structure

25
Exercise 14.2 (cont.)

• Add some (10) records to the table
• Some with marks and dates, others with the name
  and surname of the student, and others with full
  details.
• Use both, the graphical entry and the INSERT
  statements.
• Note The field id does not need to be filled
  in as we have specified it as auto-increment.
  With every record we add, its value is increased
  automatically.
• Perform a number of SELECT statements, like
• All the students with marks gt 12
• All the students with no marks
• All the students that they were marked a certain
  date