Introduction to PHP MySQL-Introduction-March 2011

obtainablerabbiData Management

Jan 31, 2013 (4 years and 9 months ago)

118 views

Bedford College


PHP is an open
-
source server
-
side scripting language used for the
creation of dynamic web pages for e
-
commerce and other web based
applications.

It is also one of the most popular server
-
side languages commercially
used today.

With consistent fast performance, high reliability and ease of use,
MySQL has become the open
-
source database of choice for many
organisations.

Together, these two technologies provide you with a simple, free and
powerful platform for building dynamic, database
-
driven web
applications.

This hands
-
on practical course is designed to enable web developers
and others with limited programming experience to build dynamic e
-
commerce websites using the PHP programming language and MySQL
database.


An understanding of basic HTML/XHTML is assumed.

Understanding PHP Basics


Introducing PHP


Using Variables and Operators


Controlling Program Flow


Working with Arrays


Using Functions and Classes

Understanding MySQL Basics


Relational Database Design


Structured Query Language


Data Manipulation Language


Simple and Advanced SQL Queries


MySQL Administration Tools


Creating a Content Management System

Working with Data from Other Sources


Working with Files and Directories


Working with XML


Working with Cookies, Sessions and Headers

Security and Troubleshooting


Handling Errors


Securing PHP


Extending PHP

PHP


PHP Hypertext Preprocessor (recursive acronym)


In use in over 20 million websites and over a third of the
world’s Web servers


Developed by a worldwide community of volunteers and
freely available at no cost


Developed by Rasmus Lerdorf 1994


called PHP/FI


Further improved by Andi Gutmans and Zeev Suraski
1997


PHP 3.0

PHP


2000


PHP 4.0


2004


PHP 5.0


2008


‘Netcraft’ survey showed PHP in use on over 30
million websites


We are using PHP 5.3xxx


Latest version is PHP 6.0

PHP


Unique Features


Performance


fast execution of scripts


Portability


available for UNIX, MS Windows, Mac OS and OS/2


and portable between platforms


Ease of Use


“Simplicity is the ultimate sophistication” plus
exhaustive documentation


Open Source (like MySQL)


Any developer, anywhere, can inspect
the code tree, spot errors and suggest possible fixes. The result is a
stable, robust product where bugs are rapidly resolved.

PHP


Unique Features


Community Support


The results of which include the libraries of
extensions
-


PEAR (PHP Extension and Application Repository) and


PECL (PHP Extension Community Library)


Third
-
Party Application Support


Support for a wide range of
databases


including MySQL, PostgreSQL, Oracle, Microsoft SQL
Server. XML support


And it doesn’t stop there…..GIF, JPEG, PNG formats, SMTP, IMAP,
POP3, SOAP and REST protocols. PHP can even manipulate PDF
documents!!

PHP


Client
-
Server Framework

PHP


Development Environment


Base
operating system

(OS) and server environment


Linux
, Windows


Web server (usually
Apache

on
Linux

or
IIS

on
Windows
)


PHP

interpreter

to parse and execute PHP code


and return results to
the web server


Database

engine (for example,
MySQL
)

PHP is executed on the
server

cf. client/browser
-
side scripting


and
only the output is sent to the browser

Thus the code is completely independent of browser quirks!!

PHP


Sample Applications


phpMyAdmin (
www.phpmyadmin.net
) = A PHP
-
based administration
tool for MySQL


phpBB (
www.phpbb.com
) = PHP
-
bases online bulletin board


Gallery (
www.gallery.meanlto.com
) = PHP
-
based digital photo
archive


PoMMo (
www.pommo.org
) = mass mailing application


Smarty (
www.smarty.net
) = PHP
-
based framework of templates for
separating business logic from user imterface


Wordpress (
www.wordpress.org
) = Weblog publishing tool


Squirrelmail, eZ Publish, Mantis……..


View phpinfo() on the department’s web server
(
www.bedford
-
college.co.uk
)


This is currently the department’s server’s default
page


Enter
www.bedford
-
college.co.uk

in your
browser’s address bar:


What is the department’s web server’s (
www.bedford
-
college.co.uk
) IP address?


Start

/
Run..



Enter
cmd




Enter
ping www.bedford
-
college.co.uk

Connecting to MySQL database on the department’s
Web Server (
www.bedford
-
college.co.uk
)


Start

/
Run..



Enter
mysql

h
www.bedford
-
college.co.uk


u
studentX

p

or


Enter
mysql

h 82.71.47.152

u studentX

p


[For this class your MySQL database username is
studentX and your password is studentX]


Connecting to MySQL database on your WAMP
Web Server


Start

/
Run..



Enter
mysql

h localhost

u root

p

There is no password set by default on WAMP
Server. Do NOT set a password.

Connecting to MySQL database on your WAMP
Web Server


Start

/
Run..



Enter
mysql

h localhost

u root

p

Or


Access the command line interface using WAMP’s
pop
-
up menu

MySQL commands:


Show databases;


Use
database
-
name;


Explain

table
-
name; or
describe

table
-
name;



MySQL commands:


Create database garage;


Show databases;


Use garage;


Create table cars(id int, make text, model text);


Describe cars;


Drop table cars;



MySQL commands:


Create table cars (id int auto_increment, make
varchar(20) not null, model varchar(20) not null unique,
primary key (id) );


Explain cars;


Insert into cars values (1, “Porsche”, “Carrera”);


Select * from cars;


Insert into cars (make, model) values (“Ferrari”,
“Dino”), (“Lamborghini”, “Diablo”);


Select * from cars;



MySQL commands:


Alter table cars add top_mph int;


Select * from cars;


Update cars set model = “911 Turbo” where
id=1;


Update cars set model = “F355” where
make=“Ferrari”;

MySQL commands:


Update cars set model = “Murcielargo” where
make=“Lamborghini”;


Update cars set top_mph=189 where id=1;


Update cars set top_mph=183 where id=2;


Update cars set top_mph=205 where id=3;


Select * from cars;


MySQL commands:


Delete from cars where id = 3;


Alter table cars drop top_mph;


Drop table cars;


Drop database garage;


Select * from cars;


Accessing PHPMyAdmin on the department’s
Web Server (
www.bedford
-
college.co.uk
)


In your browser, enter http://www.bedford
-
college.co.uk/PHPMyAdmin



[For this class your MySQL username is
studentX and your password is studentX]


Accessing PHPMyAdmin on the department’s
Web Server (
www.bedford
-
college.co.uk
)


Cookies MUST be enabled on your browser


PHPMyAdmin is a GUI interface MySQL database
management tool


cf. command line MySQL console


Enter your username and password for your
MySQL account



Accessing PHPMyAdmin on the department’s Web
Server (
www.bedford
-
college.co.uk
)


You do not have privileges to create a new database


You have a ‘default’ database called studentX


You have been granted
ALL PRIVILEGES

for this
database


You can create tables within this database


You can access other databases within MySQL database


and you have
SELECT

privileges only for these databases



Using PHPMyAdmin on the department’s Web Server
create a table called Books within the studentX
database:


Access the studentX database


Under Create New Table, enter Books


Number of Fields = 6


First field:


Field = Bkid, Type = INT, Index = Primary, A_I for Auto
Increment.






Add records to your table:








Field

Type

Lengths/Values

Title

VARCHAR

255

Author

VARCHAR

60

Publisher

VARCHAR

60

Price

DECIMAL

10, 2

Category

VARCHAR

20


Other fields:








Title

Author

Publisher

Price

Category

Nightfall

Asimov

Bantam

5.99

Sci Fi

Patriot Games

Clancy

Berkeley

4.95

Thriller

2010

Clarke

Ballantine

3.95

Sci Fi

Lie Down with Lions

Follett

Signet

4.95

Mystery

A Thief of Time

Hillerman

Harper

4.95

Mystery

The Fly on the Wall

Hillerman

Harper

4.95

Mystery

Hornet Flight

Follett

Signet

7.99

Thriller

The Innocent Man

Grisham

Dell

7.99

Thriller

Mission of Honour

Clancy

Berkeley

7.99

Thriller