PHP

1
PHP MySQL

• CSI4118
• Fall 2005

2
Introduction to PHP

• Basic principles and syntax

3
What is PHP?

• PHP Hypertext Preprocessor
• Open-source, server-side scripting language
• Used to generate dynamic web-pages
• PHP scripts reside between reserved PHP tags
• This allows the programmer to embed PHP scripts
  within HTML pages

4
What is PHP (contd)
What is PHP (Contd)

• Interpreted language, scripts are parsed at
  run-time rather than compiled beforehand
• Executed on the server-side
• Source-code not visible by client
• View Source in browsers does not display the
  PHP code
• Plethora of built-in functions allow for fast
  development
• Compatible with many popular databases

5
What does PHP code look like?

• Structurally similar to C/C
• Supports procedural and object-oriented paradigm
  (to some degree)
• All PHP statements end with a semi-colon
• Each PHP script must be enclosed in the reserved
  PHP tag

lt?php ?gt
6
Comments in PHP

• Standard C, C, and shell comment symbols

// C and Java-style comment Shell-style
comments / C-style comments These can span
multiple lines /
7
Variables in PHP

• PHP variables must begin with a sign
• Case-sensitive (Foo ! foo ! fOo)
• Global and locally-scoped variables
• Global variables can be used anywher
• Local variables restricted to a function or class
• Certain variable names reserved by PHP
• Form variables (_POST, _GET)
• Server variables (_SERVER)
• Etc.

8
Variable usage
lt?php foo 25 // Numerical variablebar
Hello // String variable foo (foo
7) // Multiplies foo by 7bar (bar 7) //
Invalid expression ?gt
9
Echo

• The PHP command echo is used to output the
  parameters passed to it
• The typical usage for this is to send data to the
  clients web-browser
• Syntax
• void echo (string arg1 , string argn...)
• In practice, arguments are not passed in
  parentheses since echo is a language construct
  rather than an actual function

10
Echo example
lt?php foo 25 // Numerical variablebar
Hello // String variable echo bar //
Outputs Hello echo foo,bar // Outputs
25Hello echo 5x5,foo // Outputs 5x525 echo
5x5foo // Outputs 5x525echo 5x5foo //
Outputs 5x5foo ?gt

• Notice how echo 5x5foo outputs foo rather
  than replacing it with 25
• Strings in single quotes ( ) are not
  interpreted or evaluated by PHP
• This is true for both variables and character
  escape-sequences (such as \n or \\)

11
Functions

• Functions MUST be defined before then can be
  called
• Function headers are of the format
• Note that no return type is specified
• Unlike variables, function names are not case
  sensitive (foo() Foo() FoO())

function functionName(arg_1, arg_2, , arg_n)
12
Functions example
lt?php // This is a function function
foo(arg_1, arg_2) arg_2 arg_1
arg_2   return arg_2 result_1 foo(12,
3) // Store the function echo result_1 //
Outputs 36 echo foo(12, 3) // Outputs 36 ?gt
13
The Big Picture for Assignment 2

• Learn about web-servers
• Learn about Apache
• Download / Installation
• Learn about PHP
• Download / Installation
• Configure Apache
• Modify and save the configuration file, then
  restart
• Learn about file I/O without the aid of SQL
• Design, write, and test scripts to solve
  Assignment 2

14
Saving Data in Text Files

• PHP has built in functions for File I/O
  processing
• fopen (..), fwrite(..), fclose(..), fflush(..),
  file_get_contents(..)
• Using these pre-made functions, File I/O in PHP
  is similar to that of C
• General Flow
• Open file
• Read data
• Modify data
• Write data
• Close file

15
Saving Data in Text Files

• Reading from a file
• lt?php    file fopen("sample.txt",
  "r")    while (!feof(file))         echo
  fgets(file), "ltBRgt"    ?gt
• Reading from a URL
• lt?php file fopen("http//www.php.net/file.txt",
  "r") ?gt
• Writing to a file
• lt?php    file fopen("agent.log",
  "a")    fputs(file, HTTP_USER_AGENT."\n")?gt

16
Saving Data in Text Files

• Reading from a file
• lt?php    file fopen("sample.txt",
  "r")    while (!feof(file))         echo
  fgets(file), "ltBRgt"    ?gt
• Reading from a URL
• lt?php file fopen("http//www.php.net/file.txt",
  "r") ?gt
• Writing to a file
• lt?php    file fopen("agent.log",
  "a")    fputs(file, HTTP_USER_AGENT."\n")?gt

17
Saving Data in Text Files

• Reading a File directly into Array or String
• The file() function reads entire file into an
  array array file (string filename , int
  use_include_path) Each element of the array
  corresponds to a line in the file, with the
  newline still attached. You can use the optional
  second parameter and set it to "1", if you want
  to search for the file in the include_path, too.
  
• Example (read a web page into an array and
  print it out)
• fcontents file ('http//localhost')
• while (list (line_num, line) each
  (fcontents))
• echo "Line line_num " .
  htmlspecialchars (line) . "\n"
• Example (read a web page into an array and join
  it to a string)
• fcontents join ('', file
  ('http//localhost'))

18
Saving Data in Text Files Example
lt?phpfilename 'test.txt' / Filename for
writing. This is assumed to be in the same
directory as the script /somecontent
"Add this to the file\n" // String to append
to the file// Let's make sure the file exists
and is writable first.if (is_writable(filename))
// Open the file in append mode ( a ) so
that the string being stored is written at the
end of the // file rather than replacing the
existing text    if (!handle fopen(filename,
'a'))          echo "Cannot open file
(filename)"         exit      // Write
somecontent to the opened file.   if
(fwrite(handle, somecontent) FALSE)     
  echo "Cannot write to file (filename)"      
exit        echo "Success, wrote
(somecontent) to file (filename)"     
fclose(handle) else    echo "The file
filename is not writable" ?gt
19
For more information

• http//www.php.net/manual/en/
• http//www.w3schools.com/php/default.asp
• http//www.zend.com/zend/tut/

20
Various Notes on PHP

• Several useful tidbits

21
Header

• The PHP function header(string)is used to send a
  raw HTTP header to the browser
• Most useful for redirecting the browser to
  another page (after a successful login, for
  example)

lt?php // string storing the destination url
http//www.uottawa.ca / Sends a header telling
the browser to navigate to url /
header(Location . url) ?gt
22
IMPORTANT NOTE!
It is absolutely vital that any calls to
header() must be made before any other output is
sent to the browser (either by standard HTML
tags, HTML comments, PHPs echo command, or even
blank lines. Failure to ensure this will result
in an error when your PHP script runs
23
Preventing Browser-caching

• The header() function can also be used to
  prevent the browser from caching the data sent by
  PHP. This forces the browser to refresh the page
  everytime the page is loaded.

24
Anti-caching example
lt?php// Date in the pastheader("Expires Mon,
26 Jul 1997 050000 GMT")// always
modifiedheader("Last-Modified " . gmdate("D, d
M Y His") . " GMT") // HTTP/1.1header("Cache
-Control no-store, no-cache, must-revalidate")h
eader("Cache-Control post-check0, pre-check0",
false)// HTTP/1.0header("Pragma
no-cache")?gt
25
Anti-caching example
IE 6 Fix when you click your back button to
make changes in the form, you have to click the
REFRESH button on that page to get the
information that you posted back into the form.
This only works about 50 of the time, the other
50 the users information is lost and they have
to type it over again. Not a good thing if you
are trying to get the person to enter their
billing information to process an order. They
might just get irritated and leave. So, here's a
solution for that. Enter this right below the
session_start() of each script (yes it still must
be before anything is output to the browser).
Solution PHP Example header("Cache-control
private")
26
Anti-caching example
lt?php // start the session session_start()
header("Cache-control private") //IE 6 Fix
echo "ltstronggtStep 2 - Register Session
lt/stronggtltbr /gt" // Get the user's input from
the form    name _POST'name' //
Register session key with the value
   _SESSION'name' name // Display the
sssion information ?gt Welcome to my website
ltstronggtlt? echo _SESSION'name'
?gtlt/stronggt!ltbr /gt Let's see what happens on the
lta href"page3.php"gtnext page.lt/agtltbr /gtltbr /gt
27
MD5 Hashing

• The PHP Function md5(string) takes a string
  parameter and returns the md5 hash of that string
  (a 32-bit hexadecimal number)
• This function does not encrypt the data sent to
  it, it merely returns a hash-value for that data.

lt?php password tomatePotate' if
(md5(password) /MD5 of password
stored in file/) echo The passwords
match! ?gt
28
Resources

• PHP function header()http//www.php.net/manual/e
  n/function.header.php
• PHP function md5()
• http//www.php.net/manual/en/function.md5.php
• RSA MD5 Message Digest Algorithmhttp//www.faqs.o
  rg/rfcs/rfc1321

29
PHP the Easy way

• A simple way to configure PHP and Apache for
  Assignment 2

30
Configuration woes

• For various reasons, manually configuring PHP to
  work with Apache can present a great deal of
  trouble
• There exists an elegant software package which is
  designed to abstract the configuration process
  from web-developers

31
EasyPHP

• http//www.easyphp.org/
• Automagically installs and configures Apache,
  PHP, and MySQL
• Defaults to running from http//localhost/
• Installer
• http//www.easyphp.org/telechargements/dn.php?Fea
  syphp1-7
• English Readme files (Extract to EasyPHP
  Installation Directory)
• http//www.easyphp.org/telechargements/dn.php?Fin
  dexUS_1.7

32
Benefits of Web Applications

• Free Infrastructure A major benefit is that the
  whole infrastructure is already in place and well
  developed. Only a web browser is needed. The new
  applications can be available to all the relevant
  users immediately IT support staff do not have
  to go to each client workstation to install the
  Client Software
• Free Upgrades As the application resides on the
  server, new versions will be immediately and
  simultaneously available to every user. There is
  no need to distribute updated application files
  to every user.
• Interchangeable components It is possible to
  exchange either the server or the browser without
  breaking the application.

33
Web Technolgies

• Client-side
• HTML (HyperText Markup Language)
• CSS (Cascading Style Sheets)
• XML (Extensible Markup Language)
• DTD (Document Type Declaration)
• XSLT (Extensible Style Sheet Language Translator)
• JavaScript
• VBScript/Jscript
• Animation (Flash )
• Dynamic Hypertext Markup Language (DHTML HTML,
  JavaScript, CSS Document Object Model (DOM)

34
Web Technolgies

• Server-side
• PHP
• ASP (Active Server Pages) ASP.NE
• JSP (Java Server Pages)
• Java Servlets
• C/Java
• PERL
• XML
• Database
• ODBC
• JDBC
• OleDB

35
Open Source Software

• www.opensource.org
• Software in a community that is
• Freely Used (no warranty, no limits on usage)
• Source code is available for any modifications
• Freely Extended (must share source, represent
  original works and owners)
• License is not specific to a product or restrict
  other software and also technology neutral.
• There's always plenty of professional and peer
  support from documentation and mailing lists.
• Runs on any Platform. Bugs are fixed rapidly, and
  requests for features are always heard,
  evaluated, and if feasible, implemented.

36
LAMP

• Linux (www.linux.com)
• nix flavour that is all the rage at the moment.
• Similar to Unix but free and runs on just about
  anything.
• Latest version 9.2
• Apache (www.apache.org)
• Most popular web page serving software
• Latest version 2.0
• MySQL (www.mysql.com)
• Open source SQL database that is free and
  extremely powerful
• Latest version 4.1 (though 5.0.1 is testing
  version)
• PHP (www.php.net)
• PHP (Hypertext Preprocessor) is mainly focused on
  server-side scripting, so you can do anything any
  other CGI program can do, such as collect form
  data, generate dynamic page content, or send and
  receive cookies. But PHP can do much more.
• Latest version 4.3 (though 5 is testing)

• Free
• Coordinated
• Cross-platform
• Plenty of support
• http//www.lamphost.net/

37
Software Usage
38
AMP

• One of the most powerful development models for
  the Web has been the notion of AMP.
• AMP stands for Apache / MySQL / PHP Perl
  working together.

PHP / Perl is a general purpose scripting
environment widely used for building dynamic web
sites.
Apache is the industry-leading web server that
runs a majority of web servers on the Internet.
MySQL is a very popular database that runs on
most operating systems.
Together, they form the nucleus of a web
application system.
www.easyphp.org -gt contains the software needed
for all three packages. You can install and
configure very easily. I am showing the
installation separately
39
Apache, MySQL and PHP (AMP) Integration
User
User
40
Apache - Benefits

• Apache is well supported - Most support for
  Apache is free and available 24 hours a day via
  Internet mail or newsgroups.
• Apache is multi-platform - Apache can run on
  virtually any hardware platform (from PCs to
  mainframes), and almost any operating system,
  such as Linux, Windows, NetWare, Macintosh, xBSD,
  etc.
• Apache is secure - security holes are rare but
  when they exist they are discovered and fixed
  quickly
• Apache is extensible - anyone can write modules
  that easily plug in to Apache. If Apache doesn't
  do what you want or need it to do, anyone with
  programming skills can write the modules you
  need.
• Apache is database-friendly - you can interface
  Apache with virtually any commercial database,
  such as Oracle, Sybase, DB2, and Informix, as
  well as free databases such as MySQL and
  Postgres.
• Apache is hardware-friendly - Apache generally
  consumes far fewer hardware resources that
  commercial web servers.
• No Microsoft Viruses - Apache is immune to the
  Code Red, Nimda, and other viruses that target at
  Microsoft Web servers.

41
Installation Apache (ver 2.0.52)

• Go to http//httpd.apache.org/download.cgi
• If you are downloading the Win32 distribution,
  please read these important notes at the website
  http//apache.mirror.mcgill.ca/httpd/binaries/win3
  2/README.html
• Download the binary version
• Win32 Binary (MSI Installer)
  apache_2.0.52-win32-x86-no_ssl.msi

42
Installation Apache (ver 2.0.52)

• Move to the folder where the Apache is installed
  and double click on the file to start the
  installation.
• Welcome screen - Press Next to continue
• Apache license Accept the terms and Press Next
• Brief intro Read and Press Next
• Server Information Enter admin users email
• Setup Type Typical
• Destination Folder leave to the default and
  press Next
• Wait for installation to complete
• You can change the settings in httpd.conf file in
  conf folder

43
Starting Apache

• Find the port address
• Open httpd.conf and find the port number 80,
  8080 or 8088 (on this machine) ? c\program
  files\Apachegroup\Apache2\conf folder
• Starting Apache service
• CProgram Files\ApacheGroup\Apache2\bin\ApacheMoni
  tor.exe. You will see at the task bar
• Open the monitor and start the apache service if
  it is not done already
• Testing the Apache web server
• http//localhost8088/ ? You may have a
  different port number
• you will see the test web page.

44
Configuration - Apache

• Edit and save httpd.conf file
• Alter Options Indexes FollowSymLinks into
• Options -Indexes FollowSymLinks
• Reboot Apache (not computer)

45
MySQL - Benefits

• In 1996 T.c.X. DataKonsultAB , a consulting firm
  in Sweden developed MySQL.
• The largest growing relational database out on
  the market as it can handle large databases that
  can be accessed over the Web - Meets the ANSI
  SQL92 regulations (SQL-Structured Query Language)
• Mainly runs on UNIX-based environments, but also
  used on windows
• One of the most used open source databases in the
  world.
• Capacity to handle 50,000,000 records.
• Very fast command execution, perhaps the fastest
  to be found on the market.
• Flexible and secure password system to protect
  your data - powerful security system
• Fast, reliable, easy to use, and affordable!
• On-line help facility - (type help or -?,)
• Comes with a source code
• Multi-User and works on Several Platforms

46
Installation - MySQL

• Go to http//dev.mysql.com/downloads/mysql/5.0.htm
  l and move to Windows (x86)5.0.0a-alpha26.7M
• Click Download
• Create folder called mysql and Unzip the file
• Execute setup.exe
• Welcome screen Press Next to continue
• Information Note down and press Next
• Destination Folder Default (c\mysql)
• Wait for installation to complete - Press Finish

47
Starting MySQL

• Go to C\mysql\bin
• Execute winmysqladmin.exe. You will be prompted
  for username and password. Type as admin with
  password as admin
• You will see the Traffic light signal on the task
  bar and also the admin window

48
Configuration - MySQL
49
MySQL Control Center

• Graphical administrative interface for MySQL
  database(s)
• Can administer several DB servers that are hosted
  on different machines
• Download from http//dev.mysql.com/downloads/other
  /mysqlcc.html
• Execute the file

50
MySQL Control Center - Configuration

• Open program in programs menu
• Establish connection
• to DB server

51
MySQL Control Center - Configuration

• in Databases ignore mysql delete test
• in User Administration delete
• first two users and assign
• password to root users
• ignore Server Administration
• adapt connection (password)

52
PHP - Benefits

• Easy, powerful, popular
• Server-side scripting language
• Supports many DBs (not only MySQL)
• Platform Independent
• Web Server Independent
• Free and Open Source
• PHP Overtakes Microsoft ASP as the Webs Number 1
  server side Web technology for the Internet.
• An April Netcraft surveys indicate 24 percent of
  the 37.6 million websites, or are running PHP
  scripts. PHP adoption is growing by 6.5 percent
  each month. (9 million sites)

53
Installation - PHP

• Download php 5.0 (binaries) from
  http//ca3.php.net/get/php-5.0.2-Win32.zip/from/a/
  mirror
• Unzip the folder to c\php-5.0.2-Win32

54
Configuration- PHP

• Move to c\php-5.0.2-Win32
• Rename php.ini-dist file to php.ini
• Copy php.ini to C\Windows
• Copy php5ts.dll to C\Windows\SYSTEM
• Go to Start-gt All Programs -gt HTTP Apache Server
  2.0.52-gt Configure HTTP Server
• -gt Edit the Apache config.httpd config file
• Look for a section that contains a number of
  LoadModule directives (from line number 131) as
• LoadModule foo_module modules/mod_foo.so

55
Installation Configuration- PHP

• Add the line at the end as
• LoadModule php5_module "c/php-5.0.2-Win32/php5ap
  ache2.dll
• Search for AddType and insert the following lines
• AddType application/x-tar .tgz
• AddType application/x-httpd-php .php
• AddType application/x-httpd-php .phtml .php
• AddType application/x-httpd-source .phps
• Configure the path to php.ini
• PHPIniDir "C/php-5.0.2-Win32
• Start Apache Server

56
Test PHP and Apache

• Test the Apache Server as
• Create php folder in D\SoftwareInstallation\Apa
  cheGroup\Apache2\htdocs or where you have
  installed Apache
• Create first.php in htdocs\php folder
  (D\SoftwareInstallation\ApacheGroup\Apache2\htdoc
  s\php) with the following lines
• lt?php
• phpinfo()
• ?gt
• Open the browser and type the following link
• http//localhost8088/php/first.php
• the port number 8088 may be different in your
  installation. You will be prompted the php
  information on the browser.

57
How it works
58
PHP Overview

• Easy learning curve
• Syntax Perl- and C-like syntax. Relatively easy
  to learn.
• Large function library
• Embedded directly into HTML
• Interpreted, no need to compile
• Open Source server-side scripting language
  designed specifically for the web.
• Conceived in 1994, now used on 10 million web
  sites.
• Outputs not only HTML but can output XML, images
  (JPG PNG), PDF files and even Flash movies all
  generated on the fly. Can write these files to
  the filesystem.
• Supports a wide-range of databases (20 ODBC).
• PHP also has support for talking to other
  services using protocols such as LDAP, IMAP,
  SNMP, NNTP, POP3, HTTP.
• Refer to php manual
• http//www.php.net/manual/en/

59
First PHP script

• Save as sample.php in htdocs
• lt! sample.php --gt
• lthtmlgtltbodygt
• ltstronggtHello World!lt/stronggtltbr /gt
• lt?php
• echo lth2gtHello, Worldlt/h2gt ?gt
• lt?php
• myvar "Hello World"
• echo myvar
• ?gt
• lt/bodygtlt/htmlgt
• Browser http//localhost8088/php/sample.php

60
PHP Control Structures

• Control Structures Are the structures within a
  language that allow us to control the flow of
  execution through a program or script.
• Grouped into conditional (branching) structures
  (e.g. if/else) and repetition structures (e.g.
  while loops).
• Example if/else if/else statement
• if (foo 0)
• echo The variable foo is equal to 0
• else if ((foo gt 0) (foo lt 5))
• echo The variable foo is between 1 and 5
• else
• echo The variable foo is equal to .foo

61
PHP - Forms

• Access to the HTTP POST and GET data is simple in
  PHP
• The global variables _POST and _GET contain
  the request data lt?php
• if (_POST"submit")
• echo "lth2gtYou clicked Submit!lt/h2gt"
• else if (_POST"cancel")
• echo "lth2gtYou clicked Cancel!lt/h2gt"
• ?gt
• ltform action"form.php" method"post"gt
• ltinput type"submit" name"submit"
  value"Submit"gt
• ltinput type"submit" name"cancel"
  value"Cancel"gt
• lt/formgt

62
WHY PHP Sessions ?
Whenever you want to create a website that allows
you to store and display information about a
user, determine which user groups a person
belongs to, utilize permissions on your website
or you just want to do something cool on your
site, PHP's Sessions are vital to each of these
features. Cookies are about 30 unreliable
right now and it's getting worse every day. More
and more web browsers are starting to come with
security and privacy settings and people browsing
the net these days are starting to frown upon
Cookies because they store information on their
local computer that they do not want stored
there. PHP has a great set of functions that can
achieve the same results of Cookies and more
without storing information on the user's
computer. PHP Sessions store the information on
the web server in a location that you chose in
special files. These files are connected to the
user's web browser via the server and a special
ID called a "Session ID". This is nearly 99
flawless in operation and it is virtually
invisible to the user.
63
PHP - Sessions

• Sessions store their identifier in a cookie in
  the clients browser
• Every page that uses session data must be
  proceeded by the session_start() function
• Session variables are then set and retrieved by
  accessing the global _SESSION
• Save it as session.php lt?php
• session_start()
• if (!_SESSION"count")
• _SESSION"count" 0
• if (_GET"count" "yes")
• _SESSION"count" _SESSION"count"
  1
• echo "lth1gt"._SESSION"count"."lt/h1gt"
• ?gt
• lta href"session.php?countyes"gtClick here to
  countlt/agt

64
Avoid Error PHP - Sessions
PHP Example lt?php echo "Look at this nasty
error belowltbr /gt" session_start() ?gt
Error!
Warning Cannot send session cookie - headers
already sent by (output started at
session_header_error/session_error.php2) in
session_header_error/session_error.php on line
3 Warning Cannot send session cache limiter -
headers already sent (output started at
session_header_error/session_error.php2) in
session_header_error/session_error.php on line 3
PHP Example lt?php session_start() echo "Look
at this nasty error below" ?gt Correct
65
Destroy PHP - Sessions
Destroying a Session why it is necessary to
destroy a session when the session will get
destroyed when the user closes their browser.
Well, imagine that you had a session registered
called "access_granted" and you were using that
to determine if the user was logged into your
site based upon a username and password. Anytime
you have a login feature, to make the users feel
better, you should have a logout feature as well.
That's where this cool function called
session_destroy() comes in handy.
session_destroy() will completely demolish your
session (no, the computer won't blow up or self
destruct) but it just deletes the session files
and clears any trace of that session. NOTE If
you are using the _SESSION superglobal array
like we are in this tutorial, you must clear the
array values first, then run session_destroy. Here
's how we use session_destroy()
66
Destroy PHP - Sessions
lt?php // start the session session_start()
header("Cache-control private") //IE 6 Fix
_SESSION array() session_destroy() echo
"ltstronggtStep 5 - Destroy This Session
lt/stronggtltbr /gt" if(_SESSION'name')
    echo "The session is still active" else
    echo "Ok, the session is no longer active!
ltbr /gt"     echo "lta href\"page1.php\"gtltlt Go
Back Step 1lt/agt" ?gt
67
MySQL Overview

• Fast, free, stable database
• Syntax is similar to Oracle
• Many of the same features as Oracle
• Production version still missing subqueries,
  stored procedures, and triggers
• Frequently used in conjunction with Linux,
  Apache, and PHP

68
MySQL Database Basics

• A relational database manager (MySQL) manages
  databases which holds tables which has records
  (rows) with attributes (columns)
• Each record must have a unique ID, also known as
  a Primary Key. When used as an identifier in
  another table its called a Foreign Key. Used for
  joins.
• Each attribute has to have a data type. (e.g.
  int, text, varchar)
• A database language (SQL) is used to create and
  delete databases and manage data

69
MySQL Create Tables

• Table structure for following examples
• Created two tables, oscarpool bestdirector
  using
• (a) use MySQL either in line mode (cd
  c\mysql\bin and mysql) or
• (b) Use MySQL Control Center
• (c) use phpMyAdmin tool which you can get
  from
• http//www.phpmyadmin.net/
• phpMyAdmin is a tool written in PHP intended to
  handle the administration of MySQL over the Web.
  Currently it can create and drop databases,
  create/drop/alter tables, delete/edit/add fields,
  execute any SQL statement, manage keys on fields,
  manage privileges,export data into various
  formats http//localhost8088/mysql/index.php

CREATE TABLE oscarpool ( uid int(4)
auto_increment, username varchar(255),
email varchar(255), bestpicture int(2),
PRIMARY KEY (uid) )
CREATE TABLE bestdirector ( bdid int(4)
auto_increment, name varchar(255), PRIMARY
KEY (bdid) )
70
MySQL INSERT

• Common SQL Statement INSERT
• INSERT INTO
• oscarpool
• (username,email,bestpicture)
• VALUES
• (dolsen',dave_at_usablecode.com',1)
• Creates a new record in the table oscarpool
• Text fields need to have s.
• Tip If you have an in your data you need to
  escape it before inserting it. Can use the PHP
  function addslashes().
• Example John O\Brien

71
MySQL SELECT

• Common SQL Statement SELECT
• SELECT uid,username
• FROM oscarpool
• Selects the attributes uid and username from
  every record in oscarpool
• SELECT is how you query the database. You can
  also
• limit the number of records returned with LIMIT,
• limit retrieval to those records that match a
  condition with WHERE,
• sort the data after the query has been evaluated
  using ORDER BY
• Tip To easily select every attribute replace
  uid with

72
MySQL UPDATE

• Common SQL Statement UPDATE
• UPDATE oscarpool
• SET email david.olsen_at_mail.wvu.edu
• WHERE uid 1
• Updates the email address where uid 1 in the
  table oscarpool
• In this case I know that uid 1 is what my record
  was. In many cases youd pass a uid variable from
  a form.

73
MySQL DELETE

• Common SQL Statement DELETE
• DELETE FROM oscarpool
• WHERE uid 1
• Deletes the record where uid 1 in the table
  oscarpool
• DELETE only removes the record from the table. To
  remove an entire table from the database you need
  to use the SQL statement DROP.
• Tip To remove every record in a table but not
  remove the table just dont include the WHERE
  clause.

74
MySQL JOIN

• SELECT bd.name
• FROM oscarpool op, bestdirector bd
• WHERE op.uid 1 and
• op.bestdirector bd.bdid
• Selects the name of the Best Director that the
  user with uid 1 has chosen
• bestdirector is a Foreign Key of the Primary Key
  for the table BestDirector

75
MySQL ERD

• Entity-Relationship (ER) Modeling
• ER Modeling is the simple and clear method of
  expressing the design (relations) of a database
  between tables and attributes.
• Rectangles Represent entities.
• Diamonds Represent relationships
• between entities
• Ellipses Represent attributes that
• describe an entity
• Lines Connect entities to relationships.
• Can have annotation.
• M many, 1 one.
• Lines Connects entities to attributes. No
  annotation.
• Entity Table, Attributes Attributes

76
MySQL DB Access
lthtmlgt ltbodygt lth1gtA List of Users Who Have Signed
Up For OscarPoollt/h1gt lt? dbh
mysql_connect("localhost","root","") or
die("Couldn't connect to database.") db
mysql_select_db("test", dbh) or
die("Couldn't select database.") sql
"SELECT username, email FROM oscarpool"
result mysql_query(sql, dbh) or
die("Something is wrong with your SQL
statement.") while (row
mysql_fetch_array(result)) username
row'username' email row'email' echo
'lta href"mailto'.email.'"gt'.username.'lt/agtltbr
/gt\n' ?gt lt/bodygt lt/htmlgt
Save it as data.php
77
Sample Projects

• A DB Driven Bulletin Board

• A XML Driven Bulletin Board

• A Bulletin Board with Text File as storage

DB Driven Solutions Front End Design
Forms (Validation) , Web Page Layout
Techs JavaScripts, Html Tags, Back End Design
RDBMS E.g. MySQL, MSSQL, Oracle
PHP Scripts
78
Database Design for BBS

• A Simple Solution
• Users Table
• Attr UserID, Password, First Name, Last
  Name, Email, Addr, Phone.
• Topics Table
• Attr TopicID, PostedTime, UserID,
  TopicSubject, TopicBody.
• Replies Table
• Attr ReplyID, PostedTime, TopicID, UserID,
  ReplySubject, ReplyBody
• .

79
Basic Use Cases for BBS

• Basic Use Cases
• 1. User Registers in the bulletin board
• (Generated UserID and Password) .Create SQL
  Users table
• 2. A user updates his info
• (Change password, personal Info etc..).. Update
  SQL Uers Table
• 3. User posts a topic Create
  SQL, Topics Table
• ( Generated TopicID and ..
• 4 . User posts a reply to a topic Create SQL,
  Replies Table
• ( Generated RepliesID and ..)

80
Thinking about BBS

• 1. If a user was authenticated to post a message
  in name of his userID? How to create a BBS
  Administrator?
• (DB Driven Authentication)
• 2. How to implement that a user just logs on
  successfully from the entrance
• page for one-time and the other web pages
  remember that the user is an
• authenticated user without asking the user to do
  authentication again?
• (Session)
• 3. How to let the browser to remember a userID in
  the log in Form always?
• (Cookie)
• 4. How to implement to sort the bulletin board?
• (Select SQL with some constraints)
• 5. How to do form validations, from Client Side
  or Server Side? Pros and cons?
• Client Scripts Vs. Server Side Scripts

81
PHP References

• www.php.net lt-- php home page
• http//www.phpbuilder.com/
• http//www.devshed.com/
• http//www.phpmyadmin.net/
• http//www.hotscripts.com/PHP/
• http//geocities.com/stuprojects/ChatroomDescripti
  on.htm
• http//www.academic.marist.edu/kbhkj/chatroom/cha
  troom.htm
• http//www.aspfree.com/c/a/ASP-Code/Free-ASP-Based
  -Chat-Program/
• http//www.aus-etrade.com/Scripts/php.php
• http//www.codeproject.com/asp/CDIChatSubmit.asp
• www.php.net/downloads lt-- php downlad page
• http//www.php.net/manual/en/install.windows.php
  lt-- php instllation manual
• http//php.resourceindex.com/ lt-- PHP
  resources like sample programs, text book
  referencs, etc.
• http//www.daniweb.com/techtalkforums/forum17.html
  ? php forums