Document Sample
mysql Powered By Docstoc
					              SYST 28043
            Web Technologies

Databases & MySQL
            Database Overview
        A collection of related Tables
        A collection of records that describe
        items in an entity
        Some sytems call this a “file”
        E.g. Employee Table contains
        Employee Records

1/15/2012             Wendi Jollymore, ACES     2
            Database Overview
        Describes a single item
        Collection of Fields or Columns
        Also called a Row
        A single data element
        Describes a piece of an item

1/15/2012            Wendi Jollymore, ACES   3
            Database Overview

1/15/2012        Wendi Jollymore, ACES   4
            Database Overview

1/15/2012        Wendi Jollymore, ACES   5
              Database Overview
    Primary Key
        A special field that acts as a unique
        identifier for a record
        Must be unique for each record
        Each table must have one
            Student ID, Social Insurance #, Product ID

1/15/2012               Wendi Jollymore, ACES            6
     Database Overview - Exercises

1.     Besides Students, what other
       tables do you think would exist in
       a database for Sheridan College?
2.     Define a structure for a table
       called Mp3s that you would use to
       keep track of all your MP3 files.
3.     Make up three examples of
       records with field values for the
       Mp3s table in #2.
1/15/2012          Wendi Jollymore, ACES    7
             Using MySQL
    In order to create database-driven
    pages, you need a database server
    We will use MySQL
    You will need to go in and create
    databases and tables that your
    pages can use
    We’ll use the PHPMyAdmin tool!

1/15/2012        Wendi Jollymore, ACES   8
             Using MySQL
    Go to http://localhost
    Log in using the user name and
    password you set up when you
    installed XAMPP
    In the left-hand menu, select
    Log in using the root password you
    set up for your Sql server when you
    installed XAMPP
1/15/2012        Wendi Jollymore, ACES    9
                Using MySQL
    On the main page, find “Create
        Name: Media
        Collation: latin1_general_ci
        Click Create
    Create the Table:
        Name: Cds
        Number of fields: 4
        Click Go
1/15/2012            Wendi Jollymore, ACES   10
                 Using MySQL
    Fill in the information for all four
        See notes online for field details
    Add a description
    Set Collation to latin1_general_ci
    Click Save

1/15/2012             Wendi Jollymore, ACES   11
                  Using MySQL
    We can’t use the root account for web
        Too powerful for a regular site visitor
    Add a Guest user:
        On main admin page, click Privleges
        Click Add new user
        Fill in the user name and password
        Select Host: Local
        For Global Privleges, check SELECT, INSERT,
        Click Go

1/15/2012               Wendi Jollymore, ACES         12
               Accessing the DB
    Create the form in the notes
        Use this to get user data to store
        PHP file will be used to retrieve the
        data and save it to the database table
    Open a new PHP file
        Add variables for user name, host,
            Use the guest name and password you

1/15/2012              Wendi Jollymore, ACES      13
             Accessing the DB
    To perform any task with your table
        Connect to the database server
        Select the database you want to work
        Perform the commands you want

1/15/2012           Wendi Jollymore, ACES      14
             Accessing the DB
    Connecting to the server:
    mysql_connect(host, user, passwd)
        Connects to a database server with a
        specific user name and password
        Returns a reference to the database
        connection object
$dblink = mysql_connect($hostname, $user, $passwd)
  or die ("Error: No connection to MySQL

1/15/2012            Wendi Jollymore, ACES           15
             Accessing the DB
    Selecting the database:
    mysql_select_db(db, conn)
        Selects a specific database using a
        connection that has already been

mysql_select_db($dbname, $dblink)
  or die ("Error: MySQL database not selected\n");

1/15/2012            Wendi Jollymore, ACES           16
             Accessing the DB
    Executing an SQL statement:
    mysql_query(cmd, conn)
        Executes a specific command or query
        using a database connection
        If cmd is a SELECT statement, function
        returns a set of records
        If cmd is INSERT, DELETE, UPDATE,
        $result true if successful, false if not
$result = mysql_query($sql, $dblink)
  or die ("SQL query failed: $sql<br

1/15/2012            Wendi Jollymore, ACES     17
               Accessing the DB
    In your PHP file:
        At the top, add variables for host,
        user, password
        Normally these are stored somewhere
        In the <body> tag:
            Connect to the database
            Select the Media table

1/15/2012               Wendi Jollymore, ACES   18
            Retrieving Form Data
    You sent your form data using
        To access in PHP file:
            Fieldname is the value in the input
            element’s name=“” attribute
        isset($_POST[“fieldname”]) function
      Returns true if the field has a value
$cdTitle = (isset($_POST["title"])) ?
  $_POST["title"] : "";

1/15/2012                Wendi Jollymore, ACES    19
            Inserting Records
    The SQL INSERT statement allows
    you to add records:
    INSERT INTO tableName (f1, f2, f3,
    ...) VALUES (v1, v2, v3…);
    Inserts the values v1 into field f1,
    the value v2 into field f2, etc…
    You can build an SQL statement
    using the form data

1/15/2012        Wendi Jollymore, ACES     20
             Inserting Records
    Add the code to
        build the SQL INSERT query
        Execute the query
        If the result returned is greater than 0,
        display a confirmation message
        If the result is 0 or less, display an
        error message
        Check the notes!

1/15/2012             Wendi Jollymore, ACES     21
            Inserting Records
    Use phpMyAdmin to check your
    table and see if the record was

    Check the notes online for complete
    code solution

1/15/2012        Wendi Jollymore, ACES   22

Shared By: