sistemas de informação e bases de dados - php & sql · sistemas de informação e bases de...

Sistemas de Informação e Bases de Dados

PHP & SQL

SIBD 2019/2020

Departamento de Engenharia InformáticaInstituto Superior Técnico

Os slides das aulas de SIBD são baseados nos slidesoficiais dos livros Database Management Systems, deRamakrishnan e Gehrke, e Database System Con-cepts, de Silberschatz, Korth e Sudarshan

http://pages.cs.wisc.edu/~dbbook/

http://db-book.com/

http://db-book.com/

Outline

PHP

PHP and PostgreSQL

PHP and Transactions

1

Outline

PHP

The PHP Language

Language Constructs

Arrays

Conditional Statements

Loops

Changing Pages

PHP and PostgreSQL


2

PHP

• PHP: recursive acronym for PHP Hypertext Preprocessor

• Server-side scripting language

• Designed for the dynamic generation of Web pages

• Runs on many platforms (Linux, Mac, Windows, ...)

• Integrated with many Web servers (Apache, IIS, ...)

• Supports many databases (MySQL, PostgreSQL, Oracle, ...)• Well integrated with MySQL

• It’s Open Source and Free Software (FSF-approved)

3

How PHP Works

• Installed with a Web server and a DMBS• Usually Apache and MySQL/PostgreSQL

• When the browser is pointed to a PHP file (.php)1. The server processes the file and sends any HTML statements2. When the server reaches a PHP tag (<?php) it executes the

PHP statements and sends the resulting output3. When an ending tag is reached (?>), the server switches back

to sending HTML statements

4

A simple program

<html><head>

<title>PHP Test</title></head><body>

<p>Hello World</p>4567<br/>7890<br/><?php

$a = ’xpto’;$b = "abcd";echo $a . ’ ’ . $b;echo "abcd";echo "xpto\n";echo "1234";

?></body>

</html> (test me here)

5

http://web.ist.utl.pt/ist14497/php//hello.php

Variables

• No need to declare variables, just assign a value

$age = 12;$price = 2.55;$number=-2;$name = "Goliath Smith";

• Variable names:• start with $• can include only letters, numbers and underscore• cannot begin with a number

• Clearing variables

unset($age);

• Note: PHP is case-sensitive

6

Constants

• Constants are set using the define statement

define("COMPANY","ABC Pet Store");define("AGE",29);

echo COMPANY;echo AGE;

7

Operators

• PHP can perform arithmentic operations• +, −, ∗, /, %

• Example:

$result = (1 + 2) * 4 + 1;

• The following comparison operators are available:• ==, >, <, >=, <=, ! = (or <>)

• Example:

$weather == "raining"$age < 13

• Plus the logical operators• and (or &&), or (or ||), xor and !

8

Strings

• A string:

$string = ’Hello World!’;

• Quote characters (" and ’) must be escaped

$string = ’It is Tom\’s house’;

• String concatenation:

$string1 = ’Hello’;$string2 = ’World!’;$stringall = $string1 . ’ ’ . $string2;

• Formatting strings:

$price = 25;$fprice = sprintf("%01.2f", $price);

9

Single-Quotes versus Double-Quotes

• Single-quoted strings (using ’) are represented as is

$age = 12;echo ’The age is $age’;

yieldsThe age is $age

• Double-quoted strings (using ") are processed$age = 12;echo "The age is $age";

yieldsThe age is 12

• Special characters are also recognized• \n, \t, ...

10

Dates and Times

• Getting the current date and time

$today = time();

• Formatting a date

$cdate = date("d/m/y", $today);$ctime = date("G:i:s", $today);

• Many formatting options: M = month abbreviated, F =month not abbreviated, ...

• Converting strings to dates

$importantDate =strtotime("January 15 2003");

(test me here)

11

http://web.ist.utl.pt/ist14497/php//date.php

Date Operations

• PHP can perform date arithmetic

$timeSpan = $today - $importantDate;

returns the number of seconds between both dates

• Conversion from strings also accepts many operations

$importantDate = strtotime("tomorrow");$importantDate = strtotime("now + 24 hours");$importantDate = strtotime("last saturday");$importantDate = strtotime("8pm + 3 days");$importantDate = strtotime("2 weeks ago");$importantDate = strtotime("next year gmt");$importantDate = strtotime("this 4am");

12

Regular Expressions

• PHP supports regular expressions

ereg("pattern", string);

returns true if pattern matches string

• Example:

ereg("^[A-Za-z’ -]+$", $name)

checks the validity of a name

• Replacements:

$new =ereg_replace($expression,$replacement,$old)

13

Other Useful Statements

• PHP contains self-referring arithmetic operators operators

$counter += 2;$counter -= 3;$counter *= 2;$counter /= 3;

• Exiting a PHP program

exit("The program is exiting");

ordie("The program is dying");

• Comments can be inserted using /* ... */ or #

14

Functions

function add_2_numbers($num1 = 1, $num2 = 1){

$total = $num1 + $num2;return $total;

}

15

Creating Arrays

• Simple array$pets[0] = "cat";$pets[1] = "dog";$pets[2] = "monkey";

or$pets = array("cat","dog","monkey");

• Key-indexed array$capitals[’PT’] = "Lisbon";$capitals[’AU’] = "Canberra";$capitals[’BR’] = "Brasilia";

or$capitals = array("PT" => "Lisbon",

"AU" => "Canberra", "BR" => "Brasilia");

16

Sorting Arrays

• Sorting simple arrays

sort($pets);

• Sorting key-indexed arrays

asort($capitals);

• Other sort methods:• rsort, arsort: reversed sort• ksort, krsort: sort by key• usort($array,function): sort using function

17

Moving Through an Array

• Arrays can behave as iterators

reset($capitals);

$value = current($capitals);echo "$value<br>";

$value = next($capitals);echo "$value<br>";

$value = next($capitals);echo "$value<br>";

• Other statements:• previous, end, sizeof

18

Moving Through an Array (cont.)

• Can use the foreach statement$capitals = array ( "PT" => "Lisbon",

"AU" => "Canberra", "BR" => "Brasilia");ksort ($capitals);foreach ( $capitals as $state => $city ){

echo "$city, $state<br>";}

(test me here)

19

http://web.ist.utl.pt/ist14497/php//foreach.php

Multidimensional Arrays

• Creating a multidimensional array

$productPrices[’clothing’][’shirt’] = 20.00;$productPrices[’clothing’][’pants’] = 22.50;$productPrices[’linens’][’blanket’] = 25.00;$productPrices[’linens’][’bedspread’] = 50.00;$productPrices[’furniture’][’lamp’] = 44.00;$productPrices[’furniture’][’rug’] = 75.00;

• Using multidimensional arrays

$shirtPrice =$productPrices[’clothing’][’shirt’];

20

Multidimensional Arrays (cont.)

<?php$productPrices[’clothing’][’shirt’] = 20.00;$productPrices[’clothing’][’pants’] = 22.50;$productPrices[’linens’][’blanket’] = 25.00;$productPrices[’linens’][’bedspread’] = 50.00;$productPrices[’furniture’][’lamp’] = 44.00;$productPrices[’furniture’][’rug’] = 75.00;

echo "<table border=1>\n";foreach( $productPrices as $category ) {

foreach( $category as $product => $price ) {$f_price = sprintf("%01.2f", $price);echo "\t<tr><td>$product:</td><td>\$$f_price</td></tr>\n";

}}echo "</table>\n";?> (test me here)

21

http://web.ist.utl.pt/ist14497/php//multidim.php

The if Statement

if ($country == "Germany" ){

$version = "German";$message = " Sie sehen unseren Katalog auf Deutsch";

}elseif ($country == "France" ){

$version = "French";$message = " Vous verrez notre catalogue en francais";

}else{

$version = "English";$message = "You will see our catalog in English";

}echo "$message<br>";

22

The switch Statement

switch ($custCountry){

case "PT" :$salestaxrate = 0;break;

case "AU" :$salestaxrate = 1.0;break;

default:$salestaxrate = .5;break;

}$salestax = $orderTotalCost * $salestaxrate;

23

The for Loop

for ($i = 0; $i < sizeof($customerNames); $i++){

echo "$customerNames[$i]<br>";}

for ($i = 0, $j = 1;$t <= 4; $i++, $j++){

$t = $i + $j;echo "$t<br>";

}

24

The while Loop

while ($testvar != "yes"){

if ($customers[$k] == "Smith"){

$testvar = "yes";echo "Smith<br>";

}else{

echo "$customers[$k], not Smith<br>";}$k++;

}

25

The do...while Loop

do{

if ($customers[$k] == "Smith"){

$testvar = "yes";echo "Smith<br>";

}else{

echo "$customers[$k], not Smith<br>";}$k++;

} while ($testvar != "yes")

Note: continue and break statements can be used 26

Redirecting the Browser

• The PHP header function can be used to send a new page tothe browserif ($customer_age < 13){

header("Location: ToyCatalog.php");}else{

header("Location: ElectronicsCatalog.php");}

Note: the header function must appear before any output is sent

27

Using Cookies

• Setting cookies

setcookie("country","PT");

orsetcookie("country","PT",time()+3600);

• All values stored as cookies are available in the $_COOKIE array

echo $_COOKIE[’country’];

Note: the setcookie function must be used before any output is sent

28

PHP Sessions

• Cookies are not always available

• PHP provides a safer mechanism: sessions

• When a session is started, PHP• If the session exists, uses it, if not creates a new session

number• Stores session variables in a file, on the server• Passes the session id number to every page

• through cookies, the URL, or a hidden post variable

• For each new page, gets the variables from the session file• The variables are accessible in the $_SESSION array

29

Using a Session

• To start a sessionsession_start()

Note: session_start must be used before any output is sent

• Using session variables

$_SESSION[’country’] = "PT";

and, on a different page

echo $_SESSION[’country’];

• Closing the session

session_destroy();

30

Session Example

<?phpsession_start();

?><html><head><title>Testing Sessions page 1</title></head><body><?php

$_SESSION[’user’] = "manel";$_SESSION[’pwd’] = "1234";echo "This is a test of the sessions feature.

<form action=’sessionTest2.php’ method=’POST’><input type=’hidden’ name=’form_var’

value=’testing’><input type=’submit’ value=’go to next page’></form>";

?></body></html> 31

Session Example (cont.)

<?phpsession_start();

?><html><head><title>Testing Sessions page 2</title></head><body><?php

echo "{$_SESSION[’user’]}<br>\n";echo "{$_SESSION[’pwd’]}<br>\n";

?></body></html>

(test me here)

32

http://web.ist.utl.pt/ist14497/php//sessionTest1.php

Outline

PHP

PHP and PostgreSQL

Interacting with the Database

Using HTML Forms


33

Connecting to the Database

• Connecting to PostgreSQL and testing for errors

$host = "db.ist.utl.pt";$port = 5432;$dbname = "istxxxxx";$user = "istxxxxx";$password = "xxxx";

$connection = pg_connect("host=$host port=$port dbname=$dbnameuser=$user password=$password

") or die(’ERROR: ’ . pg_last_error());

• Closing the connection

pg_close($connection);

34

Sending SQL Statements

$sql = "SELECT * FROM account";$result = pg_query($sql)or die(’ERROR: ’ . pg_last_error());

• For statements that return data, $result is a pointer thatenables iteration over the pointer returned by the query

• For statements that do not return data, $result containsTRUE if the statement suceeded, or FALSE on failure

35

Fetching Results

• Fetching Results

while ($row = pg_fetch_assoc($result)){

echo($row["account_number"] . ’ ’ . $row["balance"]);}

• Freeing Results

$result = pg_free_result($result)or die(’ERROR: ’ . pg_last_error());

36

Processing Forms

• An HTML form<form action="processform.php" method="post"><p>Your name: <input type="text" name="name" /></p><p>Your age: <input type="text" name="age" /></p><p><input type="submit" /></p></form>

• Program processform.php will have access to the forms fieldsthrough the arrays

• $_POST: contains elements for all the fields contained in a formif the form uses method POST

• $_GET: contains elements for all the fields contained in a formif the form uses method GET

• $_REQUEST: contains all elements in $_POST, $_GET and$_COOKIE

• Example: $_POST[’name’]37

Processing Forms Example

<html><head><title>POST fields</title></head><body><?phpecho $_GET[’name’], "<br>";foreach ($_GET as $field => $value){

echo "$field = $value<br>";}?></body></html>

(test me here)

38

http://web.ist.utl.pt/ist14497/php//form.html

Using Checkboxes

• Checkboxes allow multiple choice values

<input type=’checkbox’ name=’type[account]’value=’account’>

<input type=’checkbox’ name=’type[loan]’value=’loan’>

• In this case, the variable $_POST[’type’] is also an array,such that

$_POST[’type’][’account’] == ’account’$_POST[’type’][’loan’] == ’loan’

if both "account" and "loan" where selected.

39

Cleaning the Input Data

• PHP provides some useful functions to clean the user input

• strip_tags: removes all HTML tags

• htmlspecialchars: converts all special characters to safe HTMLentities

• Example:

$last_name = strip_tags($last_name,"<b><i>");$last_name = htmlspecialchars($last_name);

40

References

• The MDN Learn web development• https://developer.mozilla.org/en-US/docs/Learn

• The W3Schools PHP Tutorial• http://www.w3schools.com/php/default.asp

• The PHP Web site• http://www.php.net/

41

https://developer.mozilla.org/en-US/docs/Learn

http://www.w3schools.com/php/default.asp

http://www.php.net/

Outline

PHP

PHP and PostgreSQL


42

Transaction Concept

• A transaction is a unit of program execution that accesses andpossibly updates various data items

• A transaction must see a consistent database• During transaction execution the database may be temporarily

inconsistent• When the transaction completes successfully (is committed),

the database must be consistent• After a transaction commits, the changes it has made to the

database persist, even if there are system failures• Multiple transactions can execute in parallel

• Two main issues to deal with:• Handling failures (e.g., hardware failures and system crashes)• Concurrent execution of multiple transactions• Concurrency and recovery to be detailed latter on the course

43

ACID Properties

To preserve the integrity of data the database system must ensure:

• Atomicity: Either all operations of the transaction are properlyreflected in the database or none are

• Consistency: Execution of a transaction in isolation preservesthe consistency of the database

• Isolation: Although multiple transactions may executeconcurrently, each transaction must be unaware of otherconcurrently executing transactions

• Durability: After a transaction completes successfully, thechanges it has made to the database persist, even if there aresystem failures

44

Example

Transaction to transfer $50 from account A− 125 to accountA− 230

update accountset balance = balance - 50where account_number = ’A-125’

update accountset balance = balance + 50where account_number = ’A-230’

45

Transaction Definition in SQL

• Data manipulation language must include a construct forspecifying the set of actions that comprise a transaction

• In SQL, a transaction begins implicitly or with starttransaction

• A transaction in SQL ends by:• commit [work] - commits current transaction and begins a

new one• rollback [work] - causes current transaction to abort

46

Example in PHP

Transaction to transfer $50 from account A− 125 to accountA− 230

pg_query("BEGIN") or die("Could not start transaction");

$result1 = pg_query("update accountset balance = balance - 50where account_number = ’A-125’")

$result2 = pg_query("update accountset balance = balance + 50where account_number = ’A-230’")

if ($result1 and $result2) {pg_query("COMMIT") or die("Transaction commit failed");

} else {pg_query("ROLLBACK") or die("Transaction rollback failed");

}

47

Questions?

47

sistemas de informação e bases de dados - php & sql · sistemas de informação e bases de...

Documents