Lecture 4:Introduction to PHP 3

PHP & MySQL

Web based 3-tier architecture

3-tier architecture (application view)

o the ability to separate logical components of an application ensures that applications are easy to manage and understand.i.e experts can be employed that specialise in one of the layers e.g. user interface design

o because communication can be controlled between each logical tier of an application, changes in one tier, for example, the database access tier, do not have to affect the client componente.g. a change from one DBMS to another would only require a change to the component in the data access layer with little or no effect on the business/logic (middle) or UI layer.

o specific tools and technologies suited to each layer can be deployed (and may evolve at a different pace) .

Advantages of the 3-tier architecture approach

Web based 3-tier architecture: tools & technologies

o Presentation tier – Browser / custom client, Client Side Scripting (JavaScript, ActionScript, VBScript etc.), Applets.

o Logical Tier – Web Server (Apache, IIS, Websphere etc.); Scripting Languages (PHP, Perl etc.), Programming Languages (Java, C, C# etc), Application Frameworks (Ruby on Rails etc.)

o Data Tier – Database Management System (DBMS) (Oracle, MySQL, SQL Server, DB2 etc.), XMLDB

Using MySQL in the persistence tier:

MySQL Strengths

o High performance benchmarks very well against commercial dbs

o Low-cost no cost under open source licence

o Easy to configure and learn easy to set up, SQL compliant

o Portable Linux, Unix and Windows versions

o Open Source source code available for modification

Tools to create a simple web-editable database

o QSEEo MySQL client (or PHPMyAdmin)o PHP Script

Define ER model in QSEE

Generate SQL

Create DatabaseMySQL

Write Script to process DB

QSEE

• Multi-case diagramming tool– All UML diagrams– ++

• Entity-Relationship Diagrams– Generates SQL for various targets (including MySQL 4.0)– Implements relationships automatically

• 1- many : adds primary key on 1-side as columns in the many side to make foreign keys

• Many-many : adds an new link table with the primary keys from both sides as foreign keys (and a joint primary key)

• Dependent (weak) entities : foreign key becomes part of the primary key

– Sets up the appropriate integrity constraints• Action on delete and update for foreign keys

PHPMyAdmin

o GUI interface to MySQL server (shares)o GUI runs on stocks (the PHP server)o Full access to a database for

o Creating tableso Added datao Browsing the datao Import and Export of tables…o Execute SQL queries (DML) or definitions (DML)

o But ..

PHPMyAdmin Interface

Command line MySQL

o Generated SQL comments are rejected by PHPMyAdmin

o login to shares directly using puttyo change directory to your project directoryo Enter:

o mysql –po use databaseo source proj.sql

Command line MySQL

PHP/MySQL a simple example (1):

DB Library - Table Book

id title author

1 MySQL in Nutshell Dyer

2 PHP and MySQL Ullman

3 Javascript Smith

<?php // set up server, username, password & database $host="localhost"; // would be "shares" in cems $un="root"; // would be unix user in cems $pw=""; // would be original cems passsword $db="Library"; // would be unix user name

// connect to the mysql server mysql_connect($host,$un,$pw);

// select the database @mysql_select_db($db) or die( "Unable to select database");

PHP/MySQL a simple example (2):

// build the query $query="SELECT * FROM book";

// run the query $result=mysql_query($query);

// count the rows returned $num=mysql_numrows($result);

// close the connection mysql_close();

PHP/MySQL a simple example (3):

// run a loop outputting one row at a time while ($i < $num) { $id=mysql_result($result,$i,"id"); $title=mysql_result($result,$i,"title"); $author=mysql_result($result,$i,"author");

echo "<p>id: $id<br/>Title: $title<br/>Author: $author</p>";

$i++; }?>

PHP/MySQL a simple example (4):

PHP/MySQL a simple example (5):