Databases

1
Databases

• MySQL and its Administration

2
Building Data Dynamic Web Sites

• Truly dynamic web sites
• Content changes over time
• Content customised for individual user
• Content automatically generated
• Content Programmatically generated
• Can be File system based
• HTML and Images stored on File System
• Gets hard to manage over time
• Database based
• HTML, Images etc all generated from database
• Easier to manage
• If data is too large, can overload the database

3
Databases

• Fast, Efficient back end storage
• Easier to manage than file system based approach
• Relational Database structure
• Well developed theory and practise
• MultiUser capable
• Multithreaded, multiprocessor, sometimes cluster
  based systems
• Standards based queries
• Structured Query Language (SQL)

4
MySQL Database

• Open Source License- free
• GNU General Public License
• Free to modify and distribute but all
  modification must be available in source code
  format
• Commercial- not free
• Full paid for professional support

5
Basic Database Server Concepts

• Database runs as a server
• Attaches to either a default port or a
  administrator specified port
• Clients connect to database
• For secure systems
• authenticated connections
• usernames and passwords
• Clients make queries on the database
• Retieve content
• Insert content

6
MySQL Administration

• Start database server
• The mysqld daemon is already running in the labs
• Default port 3306
• At home or in your own installations
• Use mysqld --console to start the server for
  testing
• Normally mysqld daemon will be started as a
  service
• Use mysqladmin -u root shutdown to stop your
  server
• No need to do this in the labs
• Use mysqlshow to test your installation
• using administrative client tools
• Create databases
• Create tables
• Insert data
• Add usernames/ passwords and privilleges
• Archive data
• Delete data

7
Interacting with MySQL using mysqlDatabases

• Using mysql client
• To connect to the database server
• Run the mysql.exe in the mysql bin directory
• A secure system would require a username and
  password
• Create database
• Create database MyDatabase
• Use database
• Use MyDatabase
• Drop database
• Drop Database MyDatabase
• Only do this if you really want to delete all the
  data stored in the database

8
mysqladmin

• Alternative administrative interface
• mysqladmin create MyDatabase
• mysqladmin drop MyDatabase
• mysqladmin is in the bin directory of the mysql
  installation

9
Interacting with MySQL using mysqlTables

• Note MySQL statements are no case sensitive
• Create table
• Create table MyTable (name VARCHAR(20), sex
  CHAR(1), birth DATE)
• Show table structure
• Describe MyTable
• Drop table
• Drop table MyTable
• Only do this if you really want to delete all the
  data stored in the table

10
mysqlInserting Data

• Inserting Data
• insert into MyTable values ('Chris',
  'm','1968-03-30')
• Single quotes are preferred though some systems
  can handle double quotes
• Inserting Data from file
• load data local infile '/path/info.txt' into
  table MyTable LINES TERMINATED BY '\r\n'
• Note lines terminated by for files created on
  windows machines
• Default is fields sperated by tab and no quote
  marks around values
• Missing values should be specified as NULL

11
mysqlRetrieving Data

• Retrieving Data
• SELECT what_to_select FROM which_table WHERE
  conditions_to_satisfy
• Everything
• select from MyTable
• Named Columns
• select name, birth from MyTable
• select name, birth from MyTable order by birth
• Select Row
• select from MyTable where name 'Chris'

12
Modifying data Update, Delete

• update MyTable set birth '1968-01-05' where
  name 'Chris'
• Note if original data was loaded from a file,
  then that file should also be updated
• Delete from MyTable where name 'Chris'
• Take care your where conditions only select those
  rows you want to delete.

13
Mysql Miscellaneous

• Running mysql in batch mode
• mysql lt batch-file gt mysql.out
• Redirect the input from your batch file of sql
  commands
• Redirect any output to a file
• Dumping database content
• Mysqldump myDatabase gt myDatabase.sql
• Loading from a dumped file
• mysql myDatabase lt myDatabase.sql
• NOTE if you want to move to another mysql server
  you will need to create the database myDatabase

14
Interacting with MySQL using PHP

• Make a connection to the mysql server
• db mysql_connect()
• Add a hostname if not localhost
• Specify a port if different from default
• Add a username if security settings require it
• Add password if required
• Note more secure method is to add passwords to a
  not world redable configuration file for Php
• Select a database to use
• mysql_select_db("databaseName",db)

15
PHP/mysqlRetrieving Data

• Make a query on the database
• result mysql_query("SELECT FROM
  srecord",db)
• Process result
• mysql_result(result,row, "field_name")
• This function retrieves each field individually
  which can be slow compared to row processing

16
PHP/mysqlRetrieving Data in Rows

• Retrieve Row
• myrow mysql_fetch_row(result)
• Access fields using array index from 0
• myrow0
• Can be confusing for many fields
• Will be inconsistent if fields are removed and
  added in a different sequence
• myrow mysql_fetch_array(result)
• Access fields using field names
• myrow"name"

17
PHP/mysqlInserting Data

• Make SQL query
• sql "INSERT INTO srecord values(chris)"
• result mysql_query(sql)
• Return TRUE for success
• Return FALSE for errors

18
Summary

• Main points to remember
• Dynamic websites with Databases
• MySQL administration and use
• PHP/ MySQL interaction