Developing mysql database applications
1 / 58

- PowerPoint PPT Presentation

  • Updated On :

Developing MySQL Database Applications. 6th IT Support Staff Conference Andrew Slater (IT Support Officer: Phonetics & Modern Languages) and John Ireland (Computing Manager, Jesus College). Workshop Synopsis. Introduction to MySQL Open Database Connectivity (ODBC)

I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
Download Presentation

PowerPoint Slideshow about '' - Rita

An Image/Link below is provided (as is) to download presentation

Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.

- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript
Developing mysql database applications l.jpg

Developing MySQL Database Applications

6th IT Support Staff Conference

Andrew Slater (IT Support Officer: Phonetics & Modern Languages)


John Ireland (Computing Manager, Jesus College)

Workshop synopsis l.jpg
Workshop Synopsis

  • Introduction to MySQL

  • Open Database Connectivity (ODBC)

  • Home-cooking: writing clients

  • Interacting with the web / PHP

  • Other APIs explained

  • Illustration: college noticeboard

  • Links and references

Welcome to mysql l.jpg
Welcome to MySQL

  • Relational Database Management System (RDBMS)

  • Open source (GNU Public License)

  • MySQL server: Windows 9x/NT/2000, Linux, Solaris, OS/2, BSD…

  • Clients can be different platforms, both via legacy interfaces and open standards (e.g. ODBC)

Features and compliance l.jpg
Features and Compliance

  • ANSI SQL92 (almost!) except:

    • Sub-select SELECT * FROM table1 WHERE id IN (SELECT id FROM table2);

    • SELECT INTO table…

  • Multi-threaded (good multi-processor performance)

  • Handles large files (e.g. 200GB) efficiently

  • Flexible security model

  • Highly optimised JOINs

Performance benchmarks l.jpg
Performance / Benchmarks

  • Comparison of competing DBMSs

  • Identical hardware for each test

  • Same platform / OS for each test

  • Graphs shown are summary from MySQL web site

Smart datatypes l.jpg
Smart Datatypes

  • AUTONUMBER fields are available: a non-revisiting incremental field.

    • In MySQL you can set the value of an AUTONUMBER field (but beware the consequences).

  • First TIMESTAMP field is automatically set to current date/time whenever record is updated

    • Last change time can be a very useful per-record property.

    • Format is ‘YYYYmmddHHMMSS’, e.g. 20010621142532

Security 1 l.jpg
Security (1)

  • Username / password (and optionally client hostname) checked before any commands are accepted;

  • Different access for each operation (SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, …)

  • Access is allow / deny at a global, database, table or column level







Security 2 l.jpg


Security (2)

  • For given SQL statement, permissions are sum of:

    • global ‘user’ permissions;

    • permissions specific to table or column;

    • database (i.e. ‘all tables’) restricted by host.






Odbc overview l.jpg
ODBC Overview


  • ODBC provides the application with a standard interface to different DBMS

ODBC Driver





Centralised data l.jpg


  • ODBC gives access to data held elsewhere, with benefits such as information-centric locking and security





Centralised Data

  • Local application has access to data via operating system (e.g. local files, shared drives, UNC path)



Using myodbc l.jpg
Using MyODBC

  • Small, free download from;

  • Install adds ‘MySQL’ to options in control panel (ODBC applet);

  • Create a ‘Data Source Name’ for each MySQL database.

Ms access connectivity l.jpg

Link tables from external databases (e.g. other Access *.mdb files or ODBC source)

Linked data appears as just another table

(even DLookup)

MS Access Connectivity

Ms access and mysql l.jpg
MS Access and MySQL files or ODBC source)

  • Generally good, fast integration (especially compared to Access with data on shared drive)

  • Occasional (documented) caveats, e.g. saving a new record can show all fields as #DELETED (use TIMESTAMP)

  • ‘Find first’ operation can be very slow

  • Transaction support and roll-back recently added

  • No direct OLE support, but simple work around available

Home cooked clients the application programming interface l.jpg
Home-cooked Clients: files or ODBC source)the Application Programming Interface

Why write clients l.jpg

Tools to write clients

VBA / Access


Crystal reports


C / C++ / PERL


Why Write Clients?

A client is simply the user interface: we already write these!

A pplication p rogramming i nterface l.jpg

A affect the user;pplication ProgrammingInterface

Key features

MySQL functions

Form processing

Session handling


What s php l.jpg
What’s PHP? affect the user;

PHP: Hypertext Pre-processor

  • “A scripting language that generates dynamic content for the web.”

  • Developed by Rasmus Lerdorf (1994)

Key features l.jpg
Key features affect the user;

  • server-side scripting language

  • tight integration with MySQL

  • available as an Apache module

  • cross-platform

  • open source and free!

Slide23 l.jpg

Total (domains): 6,156,321 affect the user;

IP addresses: 914,146

Usage stats

(source Netcraft, April 2001)

Number of web sites using PHP

Slide24 l.jpg

Apache Module Usage affect the user;(Source: E-soft Inc., April 2001)

Number of Apache web servers

Php apache and mysql l.jpg
PHP, Apache and MySQL affect the user;




Slide26 l.jpg

Applications: affect the user;

Speech Database (Phonetics)

Admissions Database (Modern Languages)

How do i get it l.jpg
How do I get it? affect the user;

  • complete source code

  • win32 binaries [linux RPMs from Redhat]

  • excellent on-line documentation

  • FAQs, recommended books etc.

  • “Teach Yourself PHP4 in 24 Hours”Matt Zandstra,SAMS publishing, 1999

Configure the web server l.jpg
Configure the web server affect the user;

Changes to httpd.conf:

  • AddType application/x-httpd-php .php

  • DirectoryIndex index.html index.php

    Restart the web server, and check it works!

Syntax l.jpg
Syntax affect the user;

  • syntax resembles C

  • some elements borrowed from Java, perl

  • user defined functions / include files

  • choice of tag styles:

    <?php … ?>

    <? … ?>

    <% … %>

    <script language=“php”> …</script>

Welcome php l.jpg
welcome.php affect the user;

<html><h1>6th ITSSC</h1>

<?php echo “Hello ITSSC delegates!”;



<?php $time = date(“H:i:s”);

printf(“The time is now %s”,$time);



Mysql functions l.jpg
MySQL functions affect the user;

PHP has functions that allow you to:

  • connect to the database server

  • runqueries

  • process query results

  • handle errors


Connecting to mysql l.jpg
Connecting to MySQL affect the user;

mysql_connect(hostname, username, pw)

$link = mysql_connect(“localhost”, “webuser”, “mypassword”);


die (“Oops - couldn’t connect”);

Selecting a database l.jpg
Selecting a database affect the user;

mysql_select_db(database_name [, link_id])



die (“Can’t select admissions!”);

Running a query l.jpg
Running a query affect the user;


$result = mysql_query("SELECT lcode from languages where lname =’French’ ");

N.B. A successful query says nothing about number of rows returned!

Processing query results l.jpg
Processing query results affect the user;

  • mysql_fetch_row(result_id)

  • mysql_fetch_array (result_id)

    while ($row = mysql_fetch_array ($result))


    printf (“%s %s\n”, $row[“surname”], $row[“firstname”]);


Form processing l.jpg
Form processing affect the user;

  • Web databases often use forms as part of the user interface

  • Form data variables automatically generate PHP variables of the same name

Slide37 l.jpg

Simple form affect the user;


<form action = "process.php">Please type your name:<input type = text name ="user"></form>


<?phpecho “Hello $user!”;?>

Speech database l.jpg
Speech database affect the user;

Demo 1

“English Intonation in the British Isles”

Grabe, Nolan, Post (ESRC grant)

  • 40 hours of speech

  • 9 dialects of British English

What are sessions l.jpg
What are sessions? affect the user;

HTTP: a stateless protocol

Client 1

Web server

Client 2









Why do we need sessions l.jpg
Why do we need sessions? affect the user;

A SESSION associates DATA with a USER for duration of their entire visit

  • e-commerce

  • CUSTOM web pages for different users(users can log in to web database)

How to store session info l.jpg
How to store session info affect the user;

1. Cookies

Cookies store client-specific dataon the client

N.B. client may reject cookie!Security issues?Max cookie size 4k

2. Session files

  • Sessions store client-specific data on the server

  • Sessions are tagged with a unique session id

Session ids l.jpg

37 affect the user;




Session ids

Web server



Session ID

Session ID

Session management l.jpg
Session management affect the user;

  • PHP4 includes functions to:

  • manage session data on the server

  • generate random session ID to identify user

  • saves session ID:

  • either with a cookie (N.B. session ID only)orin the query string

Starting a session l.jpg
Starting a session affect the user;


1. create session file in /tmp on the server

2. sends a cookie called PHPSESSID to the client (client may refuse it)

Set-Cookie: PHPSESSID=8d8e5a520c56e0a2e5751ae7b8c8273e; path=/

Cache-Control: no-store, no-cache, must-revalidate, post-check=0, pre-check=0

Pragma: no-cache

Resuming a session l.jpg
Resuming a session affect the user;


  • An existing session is resumed if:

  • client sends cookie with session id or

  • session id was passed in the query string


Registering variables l.jpg
Registering variables affect the user;


  • session_register registers the variable for use in the current session

  • changes are automatically reflected in the session file



Ending sessions l.jpg
Ending sessions affect the user;


Ends current session

(Gotcha: variables remain available in current script, until the script reloaded)


Wipes all currently registered variables

Sessions example l.jpg
Sessions example affect the user;

<? phpsession_start(); // initialise a sessionsession_register(‘counter’); // register variable

$counter++; // increment counter

echo (“You have visited this page $counter times”);


Slide49 l.jpg

Demo 2 affect the user;

  • Modern Languages Admissions Database

    Andrew Slater, Chris Turner, 2000

    Used by colleges-based ML tutors to track admissions process

    Sessions used to provide college-specific views of candidates / access rights

Other mysql apis l.jpg
Other MySQL APIs affect the user;

  • MySQL ships with APIs for several common languages:

    • C / C++

    • PERL / PHP

    • Java;

  • Each API provides the same core functions such as:

    • connect(), select_db(), query(), store_result(), close();

  • Data types heavily dependent on language:

    • garbage collection in Java

    • query results returned as associative array in PERL

    • lots of pointers in C.

Linux client in c l.jpg
Linux Client in C affect the user;

  • Install mysqlclient-3.2.23-1.i386.rpm

  • Enter and compile code (add error checking etc.)

#include <stdio.h>

#include <mysql.h>

int main() {

MYSQL mdbi, *mdb = &mdbi;



const char *host, *db, *user, *pwd;

int ii, nrows;

/* . . . Input values for host, user, pwd, db */

Linux client mysql core l.jpg
Linux Client: MySQL Core affect the user;

mdb = mysql_connect(mdb, host, user, pwd); mysql_select_db(mdb, db);

mysql_query(mdb, “SELECT * FROM Table”);

res = mysql_store_result(mdb);

nrows = mysql_num_rows(res);

for (ii = 0; ii < nrows; ii++) {

row = mysql_fetch_row(res);

printf(“%5d: %s\n”, atoi(row[0]), row[2]);




return 0;


  • Compile: gcc -o sample sample.c -lmysqlclient

  • Ready to run!

Example jesus college noticeboard l.jpg
Example: affect the user;Jesus College Noticeboard

  • Central MySQL database of ‘articles’

    • ‘Message Of The Day’ articles

    • Announcements (read-once)

    • News articles (with automatic index listing)

  • MS Access front-end: familiar to administrative staff

  • Integrated into website (notices and news board)

  • Client added to system-wide UNIX login scripts

  • Plans for Windows login client

Ex system overview l.jpg
Ex: System Overview affect the user;





MS Access







MS Access




Ex ms access front end l.jpg
Ex: MS Access front end affect the user;

Ex web integration l.jpg
Ex: Web integration affect the user;

Ex unix client at login l.jpg
Ex: UNIX client at login affect the user;

More information l.jpg
More Information... affect the user;

Extensive MySQL documentation at

Teach yourself PHP in 24 hours, Matt Zandstra, SAMS 1999

MySQL manual, Paul DuBois, NewRiders 1999

Other PHP books (e.g. O’Reilly, WROX Press)

[email protected]

[email protected]