PHP & MySQL CSI4118 Fall 2005

PHP & MySQL CSI4118 Fall 2005

Introduction to PHP Basic principles and syntax

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 24/6/30 3

What WhatisisPHP PHP (Cont’d) (cont’d) 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 24/6/30 4

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 ?php ? 24/6/30 5

Comments in PHP Standard symbols C, C , and shell comment // C and Java-style comment # Shell-style comments /* C-style comments These can span multiple lines */ 24/6/30 6

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. 24/6/30 7

Variable usage ?php foo 25; bar “Hello”; foo ( foo * 7); bar ( bar * 7); expression ? 24/6/30 // Numerical variable // String variable // Multiplies foo by 7 // Invalid 8

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 client’s 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 24/6/30 9

Echo example ?php foo 25; bar “Hello”; echo echo echo echo echo ? 24/6/30 bar; foo, bar; “5x5 “, foo; “5x5 foo”; ‘5x5 foo’; // Numerical variable // String variable // // // // // Outputs Outputs Outputs Outputs Outputs Hello 25Hello 5x5 25 5x5 25 5x5 foo Notice how echo ‘5x5 foo’ 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 “\\”) 10

Functions Functions MUST be defined before then can be called Function headers are of the format function functionName( arg 1, arg 2, , arg n) Note that no return type is specified Unlike variables, function names are not case sensitive (foo( ) Foo( ) FoO( )) 24/6/30 11

Functions example ?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); function echo result 1; echo foo(12, 3); // Store the // Outputs 36 // Outputs 36 ? 24/6/30 12

The Big Picture for Assignment 2 Learn about web-servers Learn about Apache Download Learn / Installation about PHP Download Configure Modify restart / Installation Apache and save the configuration file, then Learn about file I/O without the aid of SQL Design, write, and test scripts to solve Assignment 2 24/6/30 13

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 24/6/30 14

Saving Data in Text Files Reading from a file ?php file fopen("sample.txt", "r"); while (!feof( file)) { echo fgets( file), " BR "; } ? Reading from a URL ?php file fopen("http://www.php.net/file.txt", "r"); ? Writing to a file ?php file fopen("agent.log", "a"); fputs( file, HTTP USER AGENT."\n"); ? 24/6/30 15

Saving Data in Text Files Reading from a file ?php file fopen("sample.txt", "r"); while (!feof( file)) { echo fgets( file), " BR "; } ? Reading from a URL ?php file fopen("http://www.php.net/file.txt", "r"); ? Writing to a file ?php file fopen("agent.log", "a"); fputs( file, HTTP USER AGENT."\n"); ? 24/6/30 16

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')); 24/6/30 17

Saving Data in Text Files: Example ?php filename '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"; } ? 24/6/30 18

For more information http://www.php.net/manual/en/ http://www.w3schools.com/php/default.asp http://www.zend.com/zend/tut/ 24/6/30 19

Various Notes on PHP Several useful tidbits

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) ?php // string storing the destination url http://www.uottawa.ca /* Sends a header telling the navigate to url */ header(“Location: “ . url); ? 24/6/30 browser to 21

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, PHP’s echo command, or even blank lines. Failure to ensure this will result in an error when your PHP script runs 24/6/30 22

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/6/30 23

Anti-caching example ?php // Date in the past header("Expires: Mon, 26 Jul 1997 05:00:00 GMT"); // always modified header("Last-Modified: " . gmdate("D, d M Y H:i:s") . " GMT"); // HTTP/1.1 header("Cache-Control: no-store, no-cache, mustrevalidate"); header("Cache-Control: post-check 0, pre-check 0", false); // HTTP/1.0 header("Pragma: no-cache"); ? 24/6/30 24

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"); 24/6/30 25

Anti-caching example ?php // start the session session start(); header("Cache-control: private"); //IE 6 Fix echo " strong Step 2 - Register Session /strong br / "; // Get the user's input from the form name POST['name']; // Register session key with the value SESSION['name'] name; // Display the sssion information: ? Welcome to my website strong ? echo SESSION['name']; ? /strong ! br / Let's see what happens on the a href "page3.php" next 24/6/30 26

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. ?php password ‘tomatePotate'; 24/6/30 if (md5( password) /*MD5 of password stored in file*/) { echo “The passwords match!” } ? 27

Resources PHP function header( ) http://www.php.net/manual/en/fu nction.header.php PHP function md5( ) http://www.php.net/manual/en/functi on.md5.php RSA MD5 Message Digest Algorithm http://www.faqs.org/rfcs/rfc1321 24/6/30 28

PHP the [Easy] way A simple way to configure PHP and Apache for Assignment 2

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 24/6/30 30

[Easy]PHP 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?F easyphp1-7 English Readme files (Extract to EasyPHP Installation Directory): http://www.easyphp.org/telechargements/ dn.php?F indexUS 1.7 24/6/30 31

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.

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)

Web Technolgies Server-side o PHP o ASP (Active Server Pages) & ASP.NE o JSP (Java Server Pages) o Java Servlets o C /Java o PERL o XML Database ODBC JDBC OleDB 24/6/30 34

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.

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 Free Coordinated Apache (www.apache.org) Cross-platform Most popular web page serving software Plenty of support Latest version: 2.0 http:// www.lamphost.net/ 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)

Software Usage Development Profiles 4,000,000 65,000 4,000,000 12,000,000 21,400,000 7,500,000 Others J ava ASP PHP Apache MySQL

AMP One of the most powerful development models for the Web has been the notion of “AMP.” AMP stands for “Apache / MySQL / PHP &Apache Perl working together.” PHP / Perl is a is the MySQL is a general purpose scripting environment widely used for building dynamic web form the nucleus ofsites. a web application system. industryleading web server that runs a majority of web servers on the Together, they Internet. very popular database that runs on most operating systems. www.easyphp.org - contains the software needed for all three packages. You can install and configure very easily. I am showing the installation separately

Apache, MySQL and PHP (AMP) Integration Use r Us er

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 24/6/30 far fewer hardware resources that commercial web servers. 40

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/w in32/README.html Download the binary version Win32 Binary (MSI Installer): apache 2.0.52-win32-x86-no ssl.msi

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 user’s 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

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 C:Program Files\ApacheGroup\Apache2\bin\ ApacheMonitor.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://localhost:8088/ You may have a different port number you will see the test web page.

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

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 -?,)

Installation - MySQL Go to http://dev.mysql.com/downloads/mysql/5.0.html 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

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

Configuration - MySQL

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/m ysqlcc.html Execute the file

MySQL Control Center - Configuration Open program in programs menu Establish connection to DB server 24/6/30 50

MySQL Control Center - Configuration 24/6/30 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) 51

PHP - Benefits Easy, powerful, popular Server-side scripting language Supports many DB’s (not only MySQL) Platform Independent Web Server Independent Free and Open Source PHP Overtakes Microsoft ASP as the Web’s 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) 24/6/30 52

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

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- All Programs - HTTP Apache Server 2.0.52- Configure HTTP Server - Edit the Apache config.httpd config file Look for a section that contains a number of LoadModule directives (from line number 131) as 24/6/30 54

Installation & Configuration- PHP Add the line at the end as LoadModule php5 module "c:/php-5.0.2Win32/php5apache2.dll“ Search for AddType and insert the following lines #AddType application/x-tar .tgz AddType application/x-httpdphp .php AddType application/x-httpdphp .phtml .php AddType application/x-httpd-source .phps Configure the path to php.ini 24/6/30 55

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

How it works

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/

First PHP script Save as sample.php in htdocs: !– sample.php -- html body strong Hello World! /strong br / ?php echo “ h2 Hello, World /h2 ”; ? ?php myvar "Hello World"; echo myvar; ? /body /html Browser: http://localhost:8088/php/sample.php 24/6/30 59

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: 24/6/30 if ( foo 0) { echo ‘The variable foo is equal to 0’; } else if (( foo 0) && ( foo 5)) { echo ‘The variable foo is between 1 and 5’; } else { echo ‘The variable foo is equal to ‘. 60

PHP - Forms Access to the HTTP POST and GET data is simple in PHP The global variables POST[] and GET[] contain the request data ?php if ( POST["submit"]) echo " h2 You clicked Submit! /h2 "; else if ( POST["cancel"]) echo " h2 You clicked Cancel! /h2 "; ? form action "form.php" method "post" input type "submit" name "submit" value "Submit" input type "submit" name "cancel" value "Cancel" 24/6/30 /form 61

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 24/6/30 "Session ID". This is nearly 99% flawless in operation and it is 62

PHP - Sessions Sessions store their identifier in a cookie in the client’s 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 ?php session start(); if (! SESSION["count"]) SESSION["count"] 0; if ( GET["count"] "yes") SESSION["count"] SESSION["count"] 1; echo " h1 ". SESSION["count"]." /h1 "; ? 24/6/30 a href "session.php?count yes" Click here to count /a 63

Avoid Error PHP Sessions PHP Example: ?php echo "Look at this nasty error below: br / "; session start(); ? Error! Warning: Cannot send session cookie - headers already sent by (output started at session header error/session error.php:2) 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.php:2) in session header error/session error.php on line 3 PHP Example: ?php session start(); echo "Look at this nasty error below:"; ? Correct 24/6/30 64

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(): 24/6/30 65

Destroy PHP - Sessions ?php // start the session session start(); header("Cache-control: private"); //IE 6 Fix SESSION array(); session destroy(); echo " strong Step 5 - Destroy This Session /strong br / "; if( SESSION['name']){ echo "The session is still active"; } else { echo "Ok, the session is no longer active! br / "; echo " a href \"page1.php\" Go Back Step 1 /a "; } ? 24/6/30 66

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 24/6/30 67

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 it’s 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 24/6/30 68

MySQL – Create Tables Table structure for following examples: 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) ) 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://localhost:8088/mysql/index.php 24/6/30 69

MySQL – INSERT Common SQL Statement: INSERT INSERT INTO oscarpool (username,email,bestpicture) VALUES (‘dolsen',‘[email protected]',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’ 24/6/30 70

MySQL – SELECT 24/6/30 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 ‘*’ 71

MySQL – UPDATE Common SQL Statement: UPDATE UPDATE oscarpool SET email ‘[email protected]’ WHERE uid 1 24/6/30 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 you’d pass a uid variable from a form. 72

MySQL – DELETE Common SQL Statement: DELETE DELETE FROM oscarpool WHERE uid 1 24/6/30 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 don’t include the WHERE clause. 73

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 24/6/30 74

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 24/6/30 75

MySQL – DB Access html body h1 A List of Users Who Have Signed Up For OscarPool /h1 ? 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 ' a href "mailto:'. email.'" '. username.' /a br / \n'; } Save it as data.php ? /body /html 24/6/30 76

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 24/6/30 PHP Scripts 77

Database Design for BBS A Simple Solution: 24/6/30 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 . 78

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 ( Generated TopicID and . 4 . User posts a reply to a topic ( Generated RepliesID and .) 24/6/30 Create SQL, Create SQL, Topics Table Replies Table 79

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 24/6/30 80

PHP References www.php.net -- php home page http://www.phpbuilder.com/ http://www.devshed.com/ http://www.phpmyadmin.net/ http://www.hotscripts.com/PHP/ http://geocities.com/stuprojects/ChatroomDescription.htm http://www.academic.marist.edu/ kbhkj/chatroom/ chatroom.htm http://www.aspfree.com/c/a/ASP-Code/Free-ASP-Based-ChatProgram/ http://www.aus-etrade.com/Scripts/php.php http://www.codeproject.com/asp/CDIChatSubmit.asp www.php.net/downloads -- php downlad page http://www.php.net/manual/en/install.windows.php -- php instllation manual http://php.resourceindex.com/ -- PHP resources like sample programs, text book referencs, etc. 24/6/30 81