Docstoc

Chapter 10 _MySQL_

Document Sample
Chapter 10 _MySQL_ Powered By Docstoc
					Relational Database Systems

    Higher Information Systems

    Advanced Implementation in
           MySQL/PHP
Developing a solution
 Tables
 Queries

 User Views

 Navigation
Introducing SQL
   Relational database management
    systems (RDBMSs) are based on a data
    definition language called Structured
    Query Language or SQL (often
    pronounced “sequel”).
   MySQL takes its name from SQL.
     Introducing SQL
   SQL is a data definition language
   CREATE DATABASE `DVD Rentals`
   CREATE TABLE Member(
    `Member Number` integer,
    Name varchar(30),
    Address varchar(50),
    `Post Code` varchar(7),
    `Telephone Number` varchar(11))
     Introducing SQL
   SQL is also a data manipulation language
   INSERT INTO Member
    VALUES (`233`, `Jo Soap`, `1 Getting Close`,
    `IS99 2QZ`, `123456`)
   SELECT * FROM Member
    WHERE `Member Number` BETWEEN 1 and 250
    ORDER BY `Member Number` DESC
   DELETE FROM Member
    WHERE `Member Number` > 500
phpMyAdmin
   A graphical user interface for MySQL
phpMyAdmin
   Viewing a table definition
phpMyAdmin
   Adding a record
phpMyAdmin
   Selecting records
phpMyAdmin
   Viewing selected records
phpMyAdmin
   Viewing selected records
Referential Integrity
   For the database to work properly, it is
    essential to guarantee that a foreign key
    always refers to a record which exists in the
    other table.
   This is called referential integrity.
   For example, an entry in the Loan table can
    only refer to an existing record in the Member
    table, because a loan can only be made by a
    member.
Referential Integrity
   MySQL has no internal facility to maintain
    referential integrity (with the default MyISAM
    table type) - instead, the database’s interface
    must be designed in such a way that a check
    is made on any value entered to ensure it is
    valid.
   Usually this is done by using a list on a web
    page to display the available choices for a
    field value from which the user may make a
    selection.
Validation: Presence Check
Validation: Range Check
   Range checking is performed by the
    interface
   Use PHP scripting to check numeric
    values
Validation: Restricted Choice
Check
Validation: Default Value
Formatting
   All formatting of data for output is done
    using PHP functions
       e.g. printf
Queries
   Searching
   Sorting
   Calculations
   All searching, sorting and calculation
    queries are performed using an SQL
    expression string
Searching
       Searching is the process of selecting records
        from a table or combination of tables.
       To perform the query, three items must be
        identified:
         Which fields will be used to identify the records
          required?
         Which tables are required to produce the data
          required?
         What are the criteria for identifying the records
          required?
Search 1: Single Condition
   Identify the names and telephone
    numbers of club members with Member
    Number over 1000
    SELECT FORENAME, SURNAME,
    `TELEPHONE NUMBER`
    FROM MEMBER
    WHERE `MEMBER NUMBER` > 1000
Search 1: Single Condition
   The asterisk * symbol can be used in an
    expression string to extract all fields
    from a table
    SELECT *
    FROM MEMBER
    WHERE `MEMBER NUMBER` > 1000
Search 2:
Multiple Conditions (AND)
   Identify the names and telephone numbers of
    club members with Surnames beginning with
    M and Member Number over 1000
   SELECT FORENAME, SURNAME,
    `TELEPHONE NUMBER`
    FROM MEMBER
    WHERE SURNAME LIKE “M*” AND
    `MEMBER NUMBER` > 1000
Search 3:
Multiple Conditions (OR)
   Identify the names and telephone
    numbers of club members with
    Surnames beginning with M or N
   SELECT FORENAME, SURNAME,
    `TELEPHONE NUMBER`
    FROM MEMBER
    WHERE SURNAME LIKE “M*” OR
    SURNAME LIKE “N*”
Search 4: More than one table
   Identify the names of club members who rented a
    DVD in August 2005
   In order to extract the related records from each
    table, a join operation is performed in SQL
   This will combine those records from each table
    which have a corresponding record in the other table
    into a single set of related records (called an answer
    set)
   The keyword used to perform this is INNER JOIN
Search 4: More than one table
   Identify the names of club members who
    rented a DVD in August 2005
   SELECT FORENAME, SURNAME, `DATE
    HIRED`
    FROM (MEMBER INNER JOIN LOAN) ON
    `MEMBER.MEMBER
    NUMBER`=`LOAN.MEMBER NUMBER`
    WHERE `DATE HIRED` LIKE “*/8/2005”
Search 5:
More than two tables
   Identify the names of members who
    have rented “Shrek”
   This time all four tables are required:
    Member, Loan, DVD and Film.
   A nested INNER JOIN statement must
    be performed to combine these
    together into a single answer set
Search 5:
More than two tables
   Identify the names of members who have rented
    “Shrek”
   SELECT `FORENAME`, `SURNAME`, `TITLE`
    FROM
    (((MEMBER INNER JOIN LOAN
    ON MEMBER.MEMBER NUMBER=LOAN.MEMBER
    NUMBER)
    INNER JOIN DVD
    ON LOAN.DVD CODE = DVD.DVD CODE)
    INNER JOIN FILM
    ON DVD.FILM CODE = FILM.FILM CODE)
    WHERE `TITLE` = “SHREK”
Search 6:
Dealing with duplicates
   List those members who have rented “Shrek”
    or “Finding Nemo”
   SELECT `FORENAME`, `SURNAME`, `TITLE`
    FROM (((MEMBER INNER JOIN LOAN
    ON MEMBER.MEMBER NUMBER=LOAN.MEMBER NUMBER)
    INNER JOIN DVD
    ON LOAN.DVD CODE = DVD.DVD CODE)
    INNER JOIN FILM
    ON DVD.FILM CODE = FILM.FILM CODE)
    WHERE `TITLE` = “SHREK” OR `TITLE` = “FINDING NEMO”
Search 6:
Dealing with duplicates
   The Group By option is used to eliminate
    duplicate results
   SELECT `FORENAME`, `SURNAME`, `TITLE`
    FROM (((MEMBER INNER JOIN LOAN
    ON MEMBER.MEMBER NUMBER=LOAN.MEMBER NUMBER)
    INNER JOIN DVD
    ON LOAN.DVD CODE = DVD.DVD CODE)
    INNER JOIN FILM
    ON DVD.FILM CODE = FILM.FILM CODE)
    WHERE `TITLE` = “SHREK” OR `TITLE` = “FINDING NEMO”
    GROUP BY *
Sorting
   To perform a sort, two items must be
    identified:
       Which field (or fields) will be used to
        decide the order of records?
       For each field selected, will the order of
        sorting be ascending or descending?
Sorting
   To produce a list of people with the tallest
    first, the records would be sorted in
    descending order of height.
   To produce a list of people with youngest
    first, the records would be sorted in
    ascending order of age.
   A very common way of ordering records
    relating to people is in alphabetical order. To
    achieve alphabetical ordering requires the
    records to be sorted in ascending order of
    surname.
Complex Sorting
   A complex sort involves more than one sort
    condition involving two or more fields.
   To achieve “telephone book order”, the name
    is sorted in ascending order of surname, then
    ascending order of forename. In this case,
    the Surname field is the primary sort key,
    and the Forename field is the secondary
    sort key.
Sorting
   SELECT FORENAME, SURNAME
    FROM MEMBER
    WHERE `MEMBER NUMBER` > 1000
    ORDER BY SURNAME, FORENAME
   SELECT FORENAME, SURNAME
    FROM MEMBER
    WHERE `MEMBER NUMBER` > 1000
    ORDER BY `MEMBER NUMBER` DESC
    Calculations
              Name       Test Test Test        Total
                          1    2    3          Mark
record 1      J Bloggs    8        9     10       27  horizontal
record 2      J Public    6        7      8       21 calculations
             Average
                          7        8       9
                                vertical
                              calculations
          Horizontal calculations are often known as
           calculated fields, and vertical calculations are
           known as summary fields.
       Functions (MySQL)
Category     Example of functions

Aggregate    MySQL: SUM, AVG, MAX, MIN, COUNT

Mathematical MySQL: SIN, COS, TAN, TRUNCATE, ROUND

Text         MySQL: LEFT, RIGHT, SUBSTRING, LENGTH, LOWER, UPPER,
             LOCATE, REPLACE

Logical      MySQL: IF, IFNULL

Conversion   MySQL: FORMAT, CONVERT, DATEF_ORMAT, STR_TO_DATE

Date         MySQL: CURDATE, DATEFORMAT, DAYOFMONTH, DAYOFWEEK,
             DAYNAME, MONTH, MONTHNAME, DATEDIFF, DATE_SUB

Time         MySQL: CURTIME, HOUR, MINUTE, SECOND, TIMEDIFF
          Functions (PHP)
Category      Example of functions

Aggregate

Mathematical PHP: Sin, Cos, Tan, Floor, Round

Text          PHP: SubStr, StrLen, StrToUpper, StrToLower, StrPos,
              SubStr_Replace

Logical       PHP: If, While, For, Foreach, Switch

Conversion    PHP: StrToTime

Date          PHP: GetDate, Date

Time          PHP: GetTimeOfDay, StrFTime
Working with Dates
   to search for all DVDs rented in the last
    week, you would use the expression
       SELECT * FROM LOAN WHERE
        DATEDIFF(CURDATE(),`DATE HIRED`) < 7
   to calculate a person’s age in years from their
    date of birth, you would use the expression
       SELECT (YEAR(CURDATE()) – YEAR(`DATE OF
        BIRTH`) –
        (RIGHT(CURDATE(),5)<RIGHT(`DATE OF
        BIRTH`,5)) AS AGE
     Parameterised Queries
1 <form action="<?=$PHP_SELF?>" method="post">
2   <p>Enter the date required:<br />
3   <input type="text" name="searchdate"><br />
4   <input type="submit" name="submitsearch" value="SUBMIT" />
5   </p>
6 </form>
7
8 <?php
9   if ($submitsearch == "SUBMIT") {
10      $sql = "SELECT `MEMBER NUMBER`
11          WHERE `DATE HIRED` = $searchdate";
12      if (@mysql_query($sql)) {
13          …
User Views
   User views are created using forms and
    reports.
   A form or report is usually based on a query
    which selects the required fields from the
    appropriate tables, sorting the results if
    necessary, and performing any horizontal
    calculations.
   In a MySQL application, all output is generally
    in the form of a web page.
         Report Structure
Report        Text/data/data to appear at the head of             DVD Rental
Header        the report                                           Statistics
              Text/data to appear at the top of each
Page Header                                                           Page 1
              page of the report

Main Detail   Text/data to appear above each entry in    Details for J Bloggs
Header        the main detail section                    Memb No Address           Tel No

Main Detail   Data from selected records in a table or   142312      Main Street   123456
Section       query

Main Detail   Text/data to appear below each entry in
                                                         Total DVDs rented to date: 26
Footer        the main detail section

              Text/data to appear at the foot of each
Page Footer                                                        End of page 1
              page of the report

Report        Text/data to appear at the bottom of the   Total DVDs rented by all members:
Footer        report                                     3,218
Summary Information
   Sum     to add values to give a total, e.g. Total
            Cost of DVD Rentals last month
   Average to find an average value, e.g. Average
            Cost of Hire per DVD
   Count   to count the number of records found,
            e.g. Number of DVDs rented per member
   Maximum to find the highest value, e.g.
            Highest number of rentals per DVD (to
            find the most popular DVD)
   Minimum to find the lowest value, e.g. Lowest
            number of rentals per member
Summary Information
   In a MySQL application, the aggregate
    functions SUM, AVG, COUNT, MAX and MIN
    can be used in an SQL expression string to
    produce summary information for a set of
    extracted records
   However, certain summary information, such
    as page totals, would have to be
    programmed using PHP (e.g. counting the
    number of records being displayed)
Scripting
   In a MySQL application, all navigation and
    user interface features are implemented
    using scripting
$result = @mysql_query("SELECT Forename, Surname FROM
   Member");
while ( $row = mysql_fetch_array($result) ) {
   $forename = $row["Forename"];
   $surname = $row["Surname"];
   echo("<tr><td>$forename</td><td>$surname</td></tr>");
}
Macros

				
DOCUMENT INFO
Shared By:
Categories:
Stats:
views:25
posted:2/5/2010
language:English
pages:45