
PHP, DBs and Application Development

PHP, DBs and Application Development

PSU CS 386 - Introduction to Databases

Most slides used courtesy of Kim Howard and Len Shapiro

OUTLINE

· Setting up an environment

· Overview of PHP and HTML

· PHP Syntax and Semantics

· HTML Forms

· PHP & Databases

§ Postgres and MySQL

· References

Getting Started

Obtain A CS Unix Account

Tutors in CS UNIX lab : FAB 135

TheCAT FAB 60-08 (Doghouse)

CAT Front Desks And Hours: http://www.cat.pdx.edu/students/labs.php

Questions and problems can be addressed by Tutors, Front Desk Workers or by email to support@cat.pdx.edu

Creating Your Webspace

mkdir ~/public_html

chmod 755 public_html

File Permissions

Place all web files in public_html

All files must be world readable

chmod 644 index.html

http://www.cs.pdx.edu/~username

index files: (index.php, index.html, index.cgi) will be displayed if one exists

What is PHP?

recursive acronym for "PHP: Hypertext Preprocessor"

widely-used Open Source general-purpose scripting language

specially suited for Web development

can be embedded into HTML

HTML Requests

Client sends HTTP request from browser to web server

Server locates pages and transmit bac to client

Browser renders webpage from html

PHP Requests

PHP executed by server and HTML sent back to client

PHP is a Server-Side Language

PHP code surrounded by special start and end tags

Jump in and out of PHP "mode"

all PHP tags replaced before outputting to web server

PHP is a preprocessor

Server parses file, if parses correctly, executes and returns to client (web browser), otherwise returns error

More Server-Side

If your HTML/PHP file contains:

<html>
<?php echo "Hello World"?>
</html>

What the end user would see with a "view source" in the browser would be:

<html>
Hello World
</html>

My First PHP Page

Our webservers only parse PHP in files ending with .php

.php files are just text files

index.php

<html>
<?php phpinfo(); ?>
</html>

PHP Version 4.3.11

System	SunOS moosehead.cat.pdx.edu 5.9 Generic_118558-09 sun4u
Build Date	Jun 26 2005 19:06:20
Configure Command	….omitted….
Server API	Apache
Virtual Directory Support	disabled
Configuration File (php.ini) Path	/pkgs/webservers/apache-1.3.33.test/etc
PHP API	20020918
PHP Extension	20020429
Zend Extension	20021010
Debug Build	no
Thread Safety	disabled
Registered PHP Streams	php, http, ftp, https, ftps, compress.zlib

This program makes use of the Zend Scripting Language Engine:
Zend Engine v1.3.0, Copyright (c) 1998-2004 Zend Technologies

PHP Tags

Tag Styles

<html><body>
<? echo 'Short Tags - Most common' ?>
<br />
<?php echo 'Long Tags - Portable, Highly Recommended' ?>
<br />
<script language="php">
echo 'Really Long Tags - rarely used';
</script>
<br />
</body></html>

Output

Short Tags - Most common
Long Tags - Portable, Highly Recommended
Really Long Tags - rarely used

comments

PHP supports 'C', 'C++' and Unix shell-style (Perl style) comments

<?php
$variable= "foobar";  // comment here

/* multi
   line
   comment
   Don't nest these. */     #also a comment ?>

Instruction Separation

Like C or Perl, end each instruction with a semi-colon (;)

<?php $variable = "Whee.... stuff goes here"; ?>
<?php echo "$variable"
// closing tag implies a semicolon?>

Output

Whee.... stuff goes here

Language Basics: Variables and Expressions

type decided at runtime by PHP depending on the context in which that variable is used

null, boolean, integer, float, string, array, object

<?php
    $foo = 1;  // integer
    $bar = "Testing";  // string
    $xyz = 3.14;  // float
    $foo = $foo + 1;  // still an integer
?>

Basic Data Types

Numbers (integers and real), Strings, Booleans

<?php
    $a= 1234;
    $b = 0777;
    $c = 0xff;
    $d = 1.25;
    echo"$a $b $c $d<br />\n";
?>

Output: 1234 511 255 1.25

<?php
    $name= 'Rasmus $last'; // Single-quoted
    $str  = "Hi $name\n";   // Double-quoted
    echo $str;
?>

Output: Hi Rasmus $last

<?php
    $greeting= true;
    while ($greeting) {
        echo"Hi Carl";
        $greeting = false;
    }
?>

Output: Hi Carl

Dynamic Typing

Don't have to declare types

Automatic conversion done

<?php
echo 5 + "1.5" + "10e2";
?>

Output: 1006.5

Strings

escape literal quotes with a backslash: \' \"

variables will expand if string in double quotes (")

<?php
echo 'this is a simple string<br/>';
$foo= "bar";
echo "This should say bar: $foo<br/>";
echo 'This should say $foo: $foo<br/>';
?>

this is a simple string
This should say bar: bar
This should say $foo: $foo

Language Basics: Arrays

Useful for many things besides an array: list (vector), hashtable, dictionary, collection, stack, queue, and more

<?php
    $arr[1] = 1;
    $arr["stuff"] = "more stuff"; // always use quotes around string literal array indexes
    $arr2[1][2] = 3;
?>

Useful array functions built-in

array_push, array_pop, array_rand, array_shift, array_search, sort, ...

Predefined Variables

Superglobals

$_SERVER : array containing information such as headers, paths, and script locations

$_GET : associative array of variables passed to the current script via the HTTP GET method

$_POST : associative array of variables passed to the current script via the HTTP POST method

<?php
echo "script: $_SERVER[SCRIPT_NAME] <br/>\n";
echo"user-agent: $_SERVER[HTTP_USER_AGENT] <br/>\n";
?>

Output

script: /~johnj/talks/show.php
user-agent: Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.1; SV1)

C-Like syntax

<?php
$loop = 0;
$i= 0;
for ($loop = -3; $loop < 3; $loop++) {
    if ($i < 0) {
        echo"-";
    } elseif ($i > 0) {
        echo"+";
    }
    echo"$loop<br/>\n";
}
while(--$loop) {
    switch($loop % 2) {
      case0:
        echo"Even<br/>\n";
        break;
      case1:
        echo"Odd<br/>\n";
        break;
    }
}
do {
    echo"$loop<br/>";
} while (++$loop< 5);
?>

Output

-3
-2
-1
0
1
2
Even
Odd

0
1
2
3
4

HTML Forms

<form action="<?php echo $PHP_SELF ?>" method="POST">
  Your name: <input type="text" name="name"><br>
  Your age: <input type="text" name="age"><br>
  <input type="submit">
</form>

Output

Hi <?echo $_POST['name'] ?>.
You are<?echo $_POST['age'] ?> years old.

PHP and Databases Overview

open a connection to the database - php returns a "connection resource"

run a query - php returns a query "result resource" (This is a cursor under another name!)

Do something with the result - display output to user or do some other actions

close the connection resource - tell database and PHP we don't need it any more

Connecting to a Database

PHP Postgresql Functions: http://www.php.net/manual/en/ref.pgsql.php

pg_connect() returns a connection resource that is needed by other PostgreSQL functions.

Connection Errors

<?php
$username= "introdb_readonly";
$password = "foobar"; // wrong password
$databasename= "introdb_library";
$hostname = "db.cecs.pdx.edu";

$connection = pg_connect("host=$hostname dbname=$databasename user=$username password=$password")
or die ("Could not connect");

// do stuff
pg_close($connection);?>

Error Message

Warning: pg_connect(): Unable to connect to PostgreSQL server:
FATAL: Password authentication failed for user "introdb_readonly" in ./display.php(433) : eval()'d code on line 7
Could not connect

Getting a Query Result

<?php
// assuming we already connected with code from previous slide...
$query = "
SELECT DISTINCT A.FirstName, A.LastName
FROM Author A
WHERE A.AuthorID IN (
  SELECT RA.AuthorID
  FROM RelAuth RA
  WHERE RA.Role = 'Translator'
);
";

$result = pg_query($connection, $query) or die("Query error: " . pg_last_error());

?>

Query Result Errors

<?php
// assuming we already connected with code from previous slide...
$query = "
SELECT DISTINCT foobar
FROM Author A
WHERE A.AuthorID IN (
  SELECT RA.AuthorID
  FROM RelAuth RA
  WHERE RA.Role = 'Translator'
);
";

$result = pg_query($connection, $query) or die("Query error: " . pg_last_error());

?>

Warning: pg_query(): Query failed: ERROR: column "foobar" does not exist in ./display.php(433) :
eval()'d code on line 20
Query error: ERROR: column "foobar" does not exist

pg_fetch_row()

pg_fetch_row() returns a row as an enumerated array

<?php
echo "<strong>" . pg_num_rows($result) . " rows returned</strong><hr>\n";
$row  = 0;
while ($row = pg_fetch_row($result)) {
   echo"First Name: $row[0]<br/>\n";
   echo"Last Name: $row[1]<br/>\n";
   echo"<hr>\n";
}
?>

5 rows returned

First Name: J
Last Name: FOSTER

First Name: LEON
Last Name: CHUA

First Name: OREN
Last Name: PATASHNIK

First Name: T
Last Name: ANDERSON

First Name: YAOHAN
Last Name: CHU

Full Working Script

<?php
$username= "introdb_readonly";
$password = "introdb";
$databasename= "introdb_library";
$hostname = "db.cecs.pdx.edu";

$connection = pg_connect("host=$hostname dbname=$databasename user=$username password=$password")
  or die ("Could not connect");

$query = "
SELECT DISTINCT A.FirstName, A.LastName
FROM Author A
WHERE A.AuthorID IN (
  SELECT RA.AuthorID
  FROM RelAuth RA
  WHERE RA.Role = 'Translator'
);
";

$result = pg_query($connection, $query) or die("Query error: " . pg_last_error());
$row  = 0;
echo "<strong>" . pg_num_rows($result) . " rows returned</strong><hr>\n";
while ($row = pg_fetch_row($result)) {
   echo"First Name: $row[0]<br/>\n";
   echo"Last Name: $row[1]<br/>\n";
   echo"<hr>\n";
}

pg_close($connection);
?>

5 rows returned

First Name: J
Last Name: FOSTER

…

Same Example in MySQL

<?php
$username= "akimbo_ro";
$password = "readme";
$databasename= "akimbo";
$hostname = "db.cecs.pdx.edu";

$connection = mysql_connect($hostname, $username, $password)
  or die ("Could not connect");

mysql_select_db($databasename) or die("Database select error: " . mysql_error());

$query = "
SELECT DISTINCT A.FirstName, A.LastName
FROM Author A
WHERE A.AuthorID IN (
  SELECT RA.AuthorID
  FROM RelAuth RA
  WHERE RA.Role = 'Translator'
);
";

$result = mysql_query($query, $connection) or die("Query error: " . mysql_error());

echo "<strong>" . mysql_num_rows($result) . " rows returned</strong><hr>\n";
while ($row = mysql_fetch_row($result)) {
   echo"First Name: $row[0]<br/>\n";
   echo"Last Name: $row[1]<br/>\n";
   echo"<hr>\n";
}

mysql_close($connection);
?>

5 rows returned

First Name: T
Last Name: ANDERSON …

Web References

PHP Website, Manual: http://www.php.net

PHP Beginning Tutorial: http://www.php.net/tut.php

Zend Website: http://www.zend.com/developers.php

Zend Beginning PHP Articles: http://www.zend.com/php/beginners/index.php

PHP Presentations: http://talks.php.net/

More PHP Related Links http://www.php.net/links.php