PHP & MySQL
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
25/3/21
3
WhatisisPHP
What 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
‘ViewSource’ in browsers does not display the
PHP code
Plethoraof built-in functions allow for fast
development
Compatible with many popular databases
25/3/21
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
…
?>
25/3/21
5
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.
25/3/21
6
Variable usage
<?php
$foo = 25; // Numerical variable
$bar = “Hello”; // String variable
$foo = ($foo * 7); // Multiplies foo by 7
$bar = ($bar * 7); // Invalid
expression ?>
25/3/21
7
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
25/3/21
8
Echo example
<?php
$foo = 25; // Numerical variable
$bar = “Hello”; // String variable
echo $bar; // Outputs Hello
echo $foo,$bar; // Outputs 25Hello
echo “5x5=“,$foo; // Outputs 5x5=25
echo “5x5=$foo”; // Outputs 5x5=25
echo ‘5x5=$foo’; // Outputs 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 “\\”)
25/3/21
9
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(…))
25/3/21
10
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); // Store the
function
echo $result_1; // Outputs 36
echo foo(12, 3); // Outputs 36
?>
25/3/21
11
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
25/3/21
12
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
25/3/21
14
Configuration woes…
Download Laragon
Open your web browser and go to the official Laragon website:
👉 https://laragon.org/download/
• Choose a version of Laragon that fits your needs: Full Version – Includes
Apache, MySQL/MariaDB, PHP, and other tools.
• Lite Version – A minimal setup without some components.
Click the
Download Link
15
Configuration woes…
•Locate the downloaded .exe file and double-click it to start
the installation.
•Select the installation directory (default is C:\laragon).
•Click Next and follow the on-screen instructions.
•Once installed, open Laragon by clicking Finish or launching
it from the Start menu.
Click the start all
button
16
[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
25/3/21
17
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)
25/3/21
18
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.
•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
25/3/21
20
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 > 0) && ($foo <= 5)) {
echo ‘The variable foo is between 1 and
5’;
}
else {
25/3/21 echo ‘The variable foo is equal to ‘. 21
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">
25/3/21</form>
22
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>";
?>
25/3/21
<a href="session.php?count=yes">Click here to count</a>23
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
25/3/21
24
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()
25/3/21
25
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>";
}
?>
25/3/21
26
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
25/3/21
27
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
25/3/21
28
MySQL – Create Tables
Table structure for following examples:
CREATE TABLE oscarpool ( CREATE TABLE
uid int(4) auto_increment, bestdirector (
username varchar(255), bdid int(4)
email varchar(255), auto_increment,
bestpicture int(2), name varchar(255),
PRIMARY KEY (uid) 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
25/3/21
http://localhost:8088/mysql/index.php
29
MySQL – INSERT
Common SQL Statement: INSERT
INSERT INTO
oscarpool
(username,email,bestpicture)
VALUES
(‘dolsen',‘dave@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’
25/3/21
30
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
‘*’
25/3/21
31
MySQL – UPDATE
Common SQL Statement: UPDATE
UPDATE oscarpool
SET email = ‘david.olsen@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 you’d pass a uid variable from a
form.
25/3/21
32
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 don’t include the WHERE
clause.
25/3/21
33
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
25/3/21
34
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
25/3/21
35
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>
25/3/21
36
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
25/3/21
37
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…
…………………….
25/3/21
38
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 …..)
……………
25/3/21
39
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
25/3/21
40
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-Chat-
Program/
•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.
25/3/21
41
