Slide 1

PHP & MYSQL

This Lecture

HTML

PHP
Variables

Arrays

IF…ELSE statements

Loops

Connecting to MySQL

Further reading
W3Schools online tutorials at

http://www.w3schools.com/html

http://www.w3schools.com/php
Learn here

http://www.w3schools.com/html
http://www.w3schools.com/php

PHP & MYSQL

example.zip [files on computer]

Examples HTML/PHP scripts are available to
download from blackboard: example.zip

Unzip it to xampp/htdocs/example

e.g. c:\xampp\htdocs\example\html1.html
e.g. /Application/XAMPP/xamppfiles/htdocs/example/html1.html

Switch on both
Apache (webserver)
MySQL

(MacOSX)

(Win7/8)

PHP & MYSQL

example.zip [files seen on webserver]

Switch on your browser
Go to:
http://localhost/example or
https://localhost/example

Click on any file, note the URL
e.g.

We now discuss them one by one

http://localhost/example/html1.html
http://localhost/example/exp6.php

http://localhost/example
https://localhost/example

PHP & MYSQL

HTML

Readable TEXT

PHP & MYSQL

Basic HTML

My First Heading

My first paragraph.

http://www.w3schools.com/html/tryit.asp?filename=tryhtml_intro

See html1.html

http://www.w3schools.com/html/tryit.asp?filename=tryhtml_intro

PHP & MYSQL

Basic HTML Table

100	200	300
400	500	600

http://www.w3schools.com/html/tryit.asp?filename=tryhtml_tables

tr: table row

td: table data

See html2.html

http://www.w3schools.com/html/tryit.asp?filename=tryhtml_tables

PHP & MYSQL

Basic HTML image

Norwegian Mountain Trip

http://www.w3schools.com/html/tryit.asp?filename=tryhtml_pulpitimage

See html3.html

http://www.w3schools.com/html/tryit.asp?filename=tryhtml_pulpitimage

PHP & MYSQL

The Limitations of SQL

SQL is not a general purpose language

It is designed to create, modify and query
databases

Base on set theory (relational algebra)

Not much normal programming constructs
available

Cannot handle platform-specific challenges such
as formatting output

PHP & MYSQL

Extending SQL

Some DBMSs add programming structures
such as variables and loops to SQL.

Very specific to a DBMS

Essentially a new language that includes SQL

Not hugely flexible

Connect to SQL from another language

Access SQL to run the relevant queries

All other work can be done using procedural code

PHP & MYSQL

ODBC

Connections to databases from programs are
often handled using Open DB Connectivity

Provides a standard interface for communication
with a DBMS

Can run queries, updates etc.

Results of queries can be used inside the program
code

PHP & MYSQL

PHP

PHP is a free, server-side scripting language

Often embedded into web pages to produce
dynamic content

Can connect to most modern DBMSs, and those
implementing ODBC.

Contains specialised functions for connecting to
MySQL

PHP & MYSQL

New to Programming?

Don’t worry!

We will not be introducing too much PHP in this
course

We will teach just enough to connect to MySQL
and produce web pages.

There will be plenty of examples to use

PHP & MYSQL

PHP Basics

PHP is procedural code that can be embedded
into html documents inside php tags. Like this:

example/exp1.php

See exp1.php

PHP & MYSQL

PHP Basics

You can have any number of php blocks,
separated by html. All php blocks will be
connected when the file is run

Code you write in an earlier block can be seen
by code you write in later blocks. This will be
important later

Anything outside a php block is HTML text

PHP & MYSQL

Outputting Text

Inside a PHP block, you can output text using
the echo command. Like SQL, commands end
with a semicolon:

example/exp2.php

See exp2.php

PHP & MYSQL

Outputting HTML

Remember, you’re working in an HTML
document, so anything you output will be
read by the browser as HTML:

“;

echo ““;

echo ““;

?>

example/exp3.php

See exp3.php

PHP & MYSQL

Variables

All programming languages use variables as a
means to store values using names. For
example, to create a number, called “num1”
that has a value of 5:

PHP is a weakly typed language, which means
you don’t need to specify that num1 is of type
“integer”, because it works it out.

$num1 = 5;

PHP & MYSQL

Variables

We use variables to keep track of the values in
our program. For example:

Then later:

“; ?>

example/exp4.php

See exp4.php

PHP & MYSQL

Strings

Strings are lists of characters

Similar to varchar(n) in SQL

Can be declared using ‘single’ or “double” quotes

Can be appended together using ‘.’

example/exp5.php

See exp5.php

PHP & MYSQL

Arrays
Sometimes it is more helpful to store variables
in lists rather than as individual names. For
example:

example/exp6.php

See exp6.php

PHP & MYSQL

Arrays

For even a few variables, this will become
messy. An alternative is to store them in a list
structure, called an Array

Arrays act like normal variables, but hold
much more data:

Arrays are lists, and individual elements are
accessed by the [] operator

example/exp7.php

See exp7.php

PHP & MYSQL

Arrays
Arrays are usually accessed by a number that
represents the position of the variable we want

Arrays can also be created and accessed by a
keyword:

Arrays are important because MySQL will give us an
array of data when we write a query.

“Database Systems”,

“PRG”=>”Programming”);

echo $courses[“DBS”];

?>
example/exp8.php

See exp8.php

PHP & MYSQL

Arrays

$var1 =

array(2,4,8,16,32);

$var2 = array(

“DBS”=>”Database Systems”,

“PRG”=>”Programming”,

“MCS”=> “Maths”);

See example/exp9.php

See exp9.php

PHP & MYSQL

IF…ELSE

Sometimes we might want to choose what
code to run depending on our variables:

if (condition)

{

// Code to run if

// condition is true;

}

PHP & MYSQL

IF…ELSE

Conditions can be
boolean variables, or
other expressions.

Conditions will include a
single IF, any number of
ELSE IFs, and then an
optional ELSE

Conditional operators
are similar to those in
MySQL.

E.g. <, >, ==, !=, <>, etc.

$var1 = true;

$var2 = 15;

if ($var1)

{

// Code

}

else if ($var2 < 5) { // Code } PHP & MYSQL IF...ELSE Be careful when using IF statements with or without ELSE. These two IF...ELSE statements are not the same! $x = 15; if ($x > 10)

{

echo “Out”;

}

else if ($x > 5)

{

echo “put”;

}

$x = 15;

if ($x > 10)

{

echo “Out”;

}

if ($x > 5)

{

echo “put”;

}

example/ex10.php

See exp10.php

PHP & MYSQL

Loops

Sometimes we need to run similar code
multiple times

We can use a loop to run the same code
repeatedly

Three types of loops (We only need WHILE for
this course though)

WHILE

DO…WHILE

FOR

PHP & MYSQL

While Loops

While loops are structured like this:

Code a will be run repeatedly until that
condition is false

while (condition)

{

// Code a

}

PHP & MYSQL

While Example

Let’s echo the contents of the array we
created earlier, using loops:

$index = 0;

$var1 = array(2,4,8,16,32);

while ($index < count($var1)) { echo $var1[$index] . ", "; $index = $index + 1; } example/exp11.php See exp11.php PHP & MYSQL Functions If you wish to reuse code, you can put it in a function to access it later. We won’t go into creating functions now, but sometimes it’s helpful to make use of functions that already exist. For example: count($array); mysqli_close($connection); print_r($array); PHP & MYSQL Connection to MySQL PHP includes various functions for communicating with a MySQL server Connects to the database and returns a connection resource Host will usually be ‘localhost’ if you’re running at home Will change the server to required database Will return a boolean stating whether this action was successful mysqli_connect("server", "username", "password"); mysqli_select_db(connection resource, "dbname"); PHP & MYSQL Connecting to MySQL In both the previous commands, if anything goes wrong, we should stop processing the PHP file You can terminate a PHP script using the die keyword: die ("A problem has occurred!"); PHP & MYSQL Connecting to MySQL $conn = mysqli_connect("localhost", "username", "password"); if(!$conn) { die ("Error connecting to MySQL: ” . mysqli_error($conn)); } // // database cdcol likely does not exist, and so die $db_select_success = mysqli_select_db($conn, "cdcol"); if(!$db_select_success) { die ("Error selecting database: ” . mysqli_error($conn)); } else { echo "MySQL database: cdcol selected."; } See exp12.php See exp12.php PHP & MYSQL Connecting to MySQL Cannot connect to DB Cannot select DB (not exist) Success! Change username/password Change "testdb" to "cs250" If database "cdcol” exists PHP & MYSQL Includes Keeping our password in plain text inside our PHP document isn’t very secure In PHP you can include code from other files for reuse later In this case, we can separate out our connection code for security. It also makes our code more concise. PHP & MYSQL Includes There are 4 commands that can include files: Includes all code from file.php at this location in the current php script As above, but only once. If you include_once a second time, nothing will happen As above, but if any errors occur in the included file, the php scripting will stop immediately include_once("file.php") include("file.php") require("file.php") / require_once("file.php") PHP & MYSQL Includes See mainfile.php See dbconnect.php

$conn = mysqli_connect(

“localhost”,

“username”, “password”);

if(!$conn)

{

die (“Error connecting to

MySQL:” .

mysqli_error($conn));

}

$db_select_success =

mysqli_select_db($conn,

“cdcol”);

if(!$db_select_success)

{

die (“Error selecting

database:” .

mysqli_error($conn));

}

See mainfile.php, dbconnect.php

See full content in dbconnect.php
it will create database cdcol if necessary.

PHP & MYSQL

Using a MySQL Connection

All SQL commands are sent to the server using
the following commands:

Sends the SQL statement to the database at the
given connection

mysqli_query($conn, “SQL Statment”);

PHP & MYSQL

Example Query

You can use any SQL command via the
mysqli_query() function. For example:

$query = “CREATE TABLE Artist(

artID INT NOT NULL,

artName VARCHAR(255) NOT NULL,

PRIMARY KEY(artID))”;

$success = mysqli_query($query);

// success will be true if the table was

// created

example/create.php

See create.php

PHP & MYSQL

HTML form and PHP insert
PHP can interact with HTML form

Give me an artist:

ID:

Name:

PHP use these names to
retrieve submitted data

The PHP that handles
the form

Press

See artist_insert.html

See artist_insert.html

PHP & MYSQL

PHP handling form

Must match HTML
form element name

Attribute names
and SQL statements

See insert.php

See insert.php

PHP & MYSQL

SELECT in PHP

For SELECT, SHOW and DESCRIBE commands,
mysqli_query() will return a set of results:

$result will now hold all our returned rows

$query = “SELECT * FROM Artist”;

$result = mysqli_query($conn,$query);

PHP & MYSQL

Using SELECT Results

To use the values in $result, we use the
following command:

$row will be an array containing all the data
from one row of our result set

Each time we use the above statement the next
row will be returned

When no rows are left, $row will be false

$row = mysqli_fetch_array($result);

PHP & MYSQL

Using SELECT Results

Because mysqli_fetch_array() will return false
when no rows remain, we can use a while
loop to make things easier:

while($row = mysqli_fetch_array($result))

{

// Use the data in $row

}

// We reach this point when we have

// used every row

PHP & MYSQL

Using SELECT Results

Once we have each row individually, we can
use the data like any regular array:

“;

echo “Artist Name: ” . $row[“artName”];

echo “

“;

}

?>

Attribute names in the returned table

See view.php

See view.php

PHP & MYSQL

HTML Tables (revisit)

Sometimes it might be useful to output our
results into an HTML Table. A table takes the
following form:

Row 1 Col 1	Row 1 Col 2
Row 2 Col 1	Row 2 Col 2

PHP & MYSQL

HTML Tables (revisit)

Row 1 Col 1	Row 1 Col 2
Row 2 Col 1	Row 2 Col 2

PHP & MYSQL

Creating a HTML table in PHP

Creating a table in php is simply a case of
using ECHO to output the necessary tags.

echo “

“;

while ($row = mysqli_fetch_array($result))

{

echo “

“;

echo “

“;

echo “

“;

echo “

“;

}

echo “

” . $row[“artID”] . “

” . $row[“artName”] . “

“;

See view2.php

See view2.php

PHP & MYSQL

Putting all together

See artist.php

= artist_insert.html + insert.php + view2.php

See artist.php

Understand this will help you on CW2

PHP & MYSQL

HTML Tables (revisit 2)

Row 1 Col 1	Row 1 Col 2
Row 2 Col 1	Row 2 Col 2

PHP & MYSQL

Nested Tables for Layout

Row 1 Col 1	Row 1 Col 2
Row 2 Col 1	Row 2 Col 2 Row A Row 2 Col 2 Row B

CW2 don’t need CSS style
for layout.
Don’t waste time on it.

https://www.tutorialspoint.com/html/html_tables.htm

PHP & MYSQL

More PHP?
This is a database course, NOT web programming.

These slides are enough for your CW2 : )

If you want to learn more, you can try these:

Remember:
We are NOT code monkey

We are computer scientists
learn the theory and adapt

through lifelong self-learning

PHP & MYSQL

References

XAMPP installation procedure

Accessing MySQL data from PHP
http://www.youtube.com/watch?v=UFbHWTkeB-I

PhpMyAdmin
http://localhost/phpmyadmin

PHP manual
http://php.net/manual/en/index.php

http://www.youtube.com/watch?v=UFbHWTkeB-I
http://localhost/phpmyadmin
http://php.net/manual/en/index.php