Server-side: PHP and MySQL

(1)

Server-side: PHP and MySQL

• ( webserver (e.g. ‘Apache’) )

• PHP

• MySQL

2 ! " #$$%& ' ( ( ) * & * +" * * , ( * -../ -0 * * & 0/ 1 +"1& ' ( ( * * 1 * ( * ' ( * 2 ( * & <html> <head><title>Hello</title></head> <body> <?phpecho ("<h1>Hello</h1>" ) ; ?> </body> </html> 3

3&

45

5

1

5

( (

)

* &

1

5 4

6

*

7 1

*

8

1

'

+ 9:"&

&

;

http://www.apachefriends.org

4

<

7 =0

* = * * 1 , 7 * > 5

•

+

•

7 =

* ?<@A

•

9

•

!

*

•

B

•

7 =

* C+"

•

+ =

<D

•

E

=

•

A

F

1

7*

A

6 < 7 *

0

6

PHP: Programming

(2)

7

1

+"1

1

<title>Generation of HTML</title> </head> <body>

<h3>Generated HTML-code</h3> <p>If number = 5, then its square = 25</p> 1 2 3 4 5 6 7 8 9 <p>The end ... </p> </body> </html> <html> <head><title>Generation of HTML</title> </head> <body> <?php

echo"<h3>Generated HTML-code</h3>\n" ; $number = 5 ;

$square = $number * $number ;

echo"<p>If number = $number” ,

", then its square = $square </p>" ; for ( $num = 1 ; $num < 10 ; $num++ )

echo"$num "; ?> <p>The end ... </p> </body></html> ) 1 ) 1 9* 1 8

B

'

G

5&H

6

'

*

* *

(

*

C

+"

=

1

'

1

I

5&(

*

2

<?php include 'prepend.php' ; include $somefile ; include ( 'somefile.txt' ) ; ?> 9

J

1

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd"> <html>

<head>

<title>Include files</title>

=

* 5

* *

5& K

&

11

5

L

• <?phpecho ( “<h1>Hello</h1>” ) ; ?> ( <=secure / always)

• <? echo ( “<h1>Hello</h1>” ) ; ?> • <script language="php"> echo ( “<h1>Hello</h1>” ) ; </script> • <% echo ( “<h1>Hello</h1>” ) ; %> reliant upon server confi- gura-tion

Variable names always start with the dollar sign ‘$’, e.g.: $a_variable ;

$name = "John" ; $weight1 = 64.5 ; $weight2 = $weight1 * 1.25 ; $age = 25 ;

When writing a web script, use PHP to output HTML tags, too. 12

@

9

<html><body> <?php echo ("<h1>PHP echo-test</h1>" ) ; $firstname = "John" ; $lastname = "Johnson" ; $age = 21 ; $length = 1.87 ;

echo $firstname, " ", $lastname ; echo ( " is " ) ;

echo $age ;

echo" years old<br>and has length: $lengthm's."; ?> </body></html> ) ' * ((( ((( 9 * A < * * ( ) * 4 M * * * ( 6 * ' ( ? * ' (&

(3)

13 <?php // Create a variable $myVariable; // Do things to variables $augend = 3; $addend = 5;

$sum = $addend + $augend; $also_sum = $augend; $also_sum += $addend; $zero = 0;

$one = ++$zero;

// Variables can be anything!

$integer = 52; $floatvalue = 15.62364;

$stringValue = 'Hello World'; $string2Value = "Hello again";

$boolValue = true; ?>

L

<

*

)

*

9

. 8 1 N

I O 88 11

14

D

<?php

functionputHeader ( $title ) {

echo <<< HEADER

<html><head> <title> $title</title> </head> <body>

HEADER ; } ;

functionmakeH1( $chaptitle ) {

echo"<h1>$chaptitle</h1>"; } ;

functionmultiply( $a, $b ) { return $a * $b; } ; // Function calls: putHeader( "PHP functions" ); makeH1( "Chapter 1" ); echo "127*23 = ",multiply( 127, 23 ); ?> <p>That's all...</p> </body></html> D ' = P ' L & 15

A

9

<?php

// Example control statements

if( $meal == 'Steak') echo'Yum!'; else echo 'Blugh!'; switch( $beverage ) { case 'wine': $BAC = 0.03; break; case 'jungle juice':

$BAC = 0.23; echo 'Death!'; break; } for( $i = 1 ; $i < 5 ; $i++ ) echo $i ;

// Special comparison example

if( 1 === true )

echo'Big problems.';

?> ) * ' ' ' &' * & A - / .. >. /. -. ((( Q ... >.. & A * & 16

<html><head><title>PHP and strings </title></head><body> <?php

// A Simple String

echo 'I am a simple string!'; // Concatenation

echo 'A' .' meets ' .'B'; // Concatenation and assignment

$string1 = 'dog'; $string2 = "fleas"; $uglydog = $string1 .$string2;

echo"<br />", $string1, " ", $uglydog; // Outputting with tags and // Interpolating variables

echo '<p> My dog is cute. </p>'; echo 'I said $string1'; echo "I said $string1"; echo "I have two {$string1}s"; ?> </body></html>

9

9 * A ? 9 1: = $$O < 1: 17

)

Index Value 0 12345

1 ‘Fish swim good.’ 2 [SimpleXML Object]

... ...

Key Value

‘First Name’ ‘Hootie’ ‘Last Name’ ‘Blowfish’

‘Species’ ‘Salmon’ ... ...

Numeric

Associative

*

Q

)

'

R

E 1L

* &

18

J

<html> <head><title>Array Demo</title></head> <body><h1>Array Demo</h1> <p> <?php

$capitals = array('France'=>'Paris', 'Ireland'=>'Dublin', 'Netherlands'=>'Amsterdam', 'Belgium'=>'Brussels'); echo 'The capital of France is ';

echo $capitals['France'],".<br />"; echo "The various capitals are:<ul>"; foreach ($capitals as $city)

{ echo "<li>$city</li>"; }; echo "</ul>"

?> </p> </body></html>

(4)

19

)

*

&

<html><head><title>PHP Arrays</title> </head><body> <?php // Using Arrays

$meals = array( 'Steak', 'Shark', 'Cat' ) ; $steak_meal = $meals[0];

$judges = array( 'Steak' =>'Good', 'Shark' =>'Dry', 'Cat' =>'Dry & Bony' ) ;

// The foreach loop foreach ( $meals as $food )

echo "I want $food. <br />"; echo "<br />" ;

foreach ( $judges as $food => $rev ) echo "$food is $rev <br />"; ?> </body></html> A * & * * & * * * 5 ! ) 20

)

* &

(&

<html><head><title>More PHP arrays</title></head><body> <?php

/* foreach example 1: value only */ $arr = array (1, 2, 3, 17); echo"Content of array \$arr: " ;

foreach( $arr as $value ) echo "$value ";

echo "<br />";

/* foreach example 2: value (with index printed for illustration) */ $arr = array (1, 2, 3, 17);

$index = 0; /* for illustrative purposes only */ foreach( $arr as $value ) {

echo"\$arr[$index] => $value.<br />"; $index++; }

/* foreach example 3: key and value */

$arr = array ( "one" => 1, "two" => 2, "three" => 3, "seventeen" => 17 );

foreach( $arr as $key => $value ) echo "\$arr[$key] => $value.<br />"; ?>

</body></html> There are two syntaxes: foreach( array_expressionas $value) statement ;

foreach( array_expressionas $key => $value ) statement ; Attention for the use of the ‘escape’ symbol ‘\’

21

*

1 ,

*

1

5

* G+ 9:"1H

5

22

*

1 ,

* D

1

9 * ST ?9 K STBJ 1 ( <html> <head><title>HTTP Request</title></head> <body> <h3>Request Form</h3>

<formmethod="POST"action="http://www.cs.ru.nl/.../request.php"> Full name:<br />

<input type="text" name="city"size="15"> <br />Your order:<br />

<textareaname="ordered"cols="15" rows="3"></textarea> <p><input type="submit"value="Submit">

<input type="reset" value="Reset"></p> </form> </body></html> 23

*

1 ,

* D

1

,

( * &

<html><head><title>Request Answer</title></head> <body> <h1>Confirmation</h1> We received your request: <table border="1"><tr> <td>Full name:</td> <?php $fullname = $_POST['fullname']; echo "<td>$fullname </td>"; ?> </tr><tr> <td>City:</td> <?php $city = $_POST['city']; echo "<td> $city </td>"; ?> </tr></table> <p>You ordered:<br /> <?php $ordered = $_POST['ordered']; echo $ordered; ?> </p></body></html> ? 1 1 (((( 9 * ST ?9 1 ( 24

A

*

+ 9:"&

A * + 9:" A I * D 9:"1, * 1 < + 9:" mysql_connect(..., ..., ...) mysql_select_db (...) mysql_query ( $query ) e.g. rows/fields/array... mysql_close ( ) @ * 5'

(5)

25

Contact with which database system?

MySQLis free (no charge, source available) It has good PHP support

Using a different SQL db system should be easy

26

+ 9:"

*

1*

Name Age Height

Tack 19 64

Joel 19 34

<?php

// Connect and Select the database

mysql_connect (‘localhost’,

‘admin’, ‘mypassword’); mysql_select_db (‘awesome_db’);

// Now let’s grab one single name

$name = ‘Joel’;

$result = mysql_query (“SELECT age FROM people WHERE name=’$name’;”); $age = mysql_fetch_assoc($result);

echo $age[‘age’]; mysql_close(); ?> 9:" + 9:" 1 !<@+9& 9:" = 4 M' 4 M People: Possible if only one single value!

27

And more examples ...

<?php

....

// perform the sql-query and return the results: $result = mysql_query( "SELECT * FROM sample;" ); //show all the data via a while loop:

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

// using foreach, list all the data that was returned in the $row array // from mysql_fetch_assoc

foreach ( $row as $key => $value ) {

echo $key . ' = ‘ . $value . '<br />' ; }

echo '<br />'; }

?>

28

Some information from php.net :

mysql_fetch_array (PHP 4, PHP 5, PECL mysql:1.0)

mysql_fetch_array — Fetch a result row as an associative array, a numeric array, or both

Description : array mysql_fetch_array( resource $result [, int $result_type] ) Returns an array that corresponds to the fetched row and moves the internal data pointer ahead.

mysql_fetch_assoc (PHP 4 >= 4.0.3, PHP 5, PECL mysql:1.0)

mysql_fetch_assoc — Fetch a result row as an associative array

Description : array mysql_fetch_assoc( resource $result )

Returns an associative array that corresponds to the fetched row and moves the internal data pointer ahead. mysql_fetch_assoc()is equivalent to calling mysql_fetch_array() with MYSQL_ASSOC for the optional second parameter. It only returns an associative array.

mysql_fetch_row (PHP 4, PHP 5, PECL mysql:1.0)

mysql_fetch_row — Fetch a result row as an enumerated array

Description : array mysql_fetch_row( resource $result )

Returns an array that corresponds to the fetched row and moves the internal data pointer ahead.

=> Which you use is up to you. All functions are pretty similar.

29

Another ‘

mysql_fetch

’-possibility when using OO-php…

mysql_fetch_object (PHP 4, PHP 5, PECL mysql:1.0)

mysql_fetch_object — Fetch a result row as an object Description: object mysql_fetch_object( resource $result

[, string $class_name [, array $params ]] ) Returns an object with properties that correspond to the fetched row and moves the internal data pointer ahead.

Parameters result

The result resource that is being evaluated. This result comes from a call to mysql_query().

class_name

The name of the class to instantiate, set the properties of and return. If not specified, a stdClass object is returned.

params

An optional array of parameters to pass to the constructor for class_name objects.

30

<

*

I+ 9:"

<?php

$connected = mysql_connect( “...science.ru.nl", "B3test-admin", “...“ ); if ( !$connected )

echo "System could not connect to MySQL ...<br />"; else

echo "System connected correctly to MySQL ...<br />"; $selectdb = mysql_select_db( "B3test“ ) ;

if ( !$selectdb )

echo "Error: could not select the database ...<br />"; else

echo "Database was correctly opened ...<br />"; $result = mysql_query ( "SELECT * FROM table_name ;“ ) ;

if ( !$result )

echo "The query could not be executed ...<br />"; else

echo "The query executed correctly ...<br />";

! " # $ !

mysql_close (); ?>

(6)

31

A* =

1

*

45

-

/ &M

$host ="mysql-B3test.science.ru.nl";

$user ="B3test-user"; $password = "L63GHrqw2"; $database = "B3test";

$connection = mysql_connect( $host, $user, $password )

or die("Couldn't connect to server" . mysql_error() ); $db = mysql_select_db( $database, $connection )

or die("Couldn't select database" . mysql_error() ); $query = "…………. " ;

$result = mysql_query( $query )

or die("Couldn't execute query: " . mysql_error());

L * * 4((( (((&M1 * = *

* !<@+9' * * '

, (

32

<

*

I+ 9:"

(&

// ... See first part ..

$result = mysql_query ( "SELECT Voornaam, Achternaam, Plaats FROM Leden ;");

if ( !$result )

echo "The query could not be executed ...<br />"; else

echo "The query executed correctly ...<br />";

// dealing with the results: determine number of received rows and of fields ... $num_rows = mysql_num_rows( $result );

$num_fields = mysql_num_fields( $result ); echo "Received: num_rows= " , $num_rows, "<br />"; echo "Received: num_fields= " , $num_fields, "<br />";

// transfer first received row data from total ‘$result’ to a separate 1-row-variable // through: $row = mysql_fetch_array( $result );

// or: $row = mysql_fetch_assoc( $result ); // get the column headers out of the (partlyassociative) row-array: // through: $keys = array_keys( $row ) ;

// or: foreach( $row as $key => $value ) ….

33

<

*

I+ 9:"

( 3&

if( $num_rows > 0 ) {

$firstRow = true; echo "<table border=\"2\">";

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

if ($firstRow) { echo "<tr>";

foreach ($row as $key => $value) echo "<th>$key</th>"; echo "</tr>\n"; $firstRow = false; }

echo "<tr>";

foreach ( $row as $key => $value ) echo "<td> $value </td>" ; echo "</tr>" ;

}

echo "</table>" ; }

First: place the table headers (the $key-values)

34

<

*

I+ 9:"

( 2&

$row = mysql_fetch_array( $result ); $keys = array_keys( $row ) ; echo "<table border=2><br /> <tr>" ; for ( $index=0 ; $index < $num_fields ; $index++ )

echo "<th>", $keys[ 2 * $index + 1 ], "</th>" ; echo "</tr>" ;

for ( $row_num=0; $row_num < $num_rows; $row_num++) { echo "<tr>" ;

$values = array_values( $row ) ;

for ( $index=0 ; $index < $num_fields ; $index++ ) { $value = htmlspecialchars($values[2*$index +1]); echo "<td> $value </td>" ;

} echo "</tr>" ; // fetch next result row:

$row = mysql_fetch_array( $result ); }

echo "</table>" ;

Much simpler with the while ($row = mysql_fetch_array( $result ); )

and foreach ( $row as $key => $value ) construction with ‘key’ and ‘value’ from the last sheet!

A

di

ffi

cu

lt

wa

y …

d

on

’t

wa

lk

th

is

wa

y i

f n

ot

n

ev

es

sa

ry

!

35

"

7 ' @ 6 ) A* @ * 7 7 7 ! 7( 9 9 * II ( * ( I * II ( , ( I I * II ( F ( I 36

)

U

1

*

1 ,

I + 9:"1

, mysql-B3test.science.ru.nl B = B3test-user * L63GHrqw2 < B3test 6 @2 1 F " @ ( V * ( 2#& * F = (

Groepsindeling met ‘groupnumber’: 1 Koldijk, Saskia + Sappelli, Maya

2 Veltmaat, Maaike + Wijngaarden, Carolien van 3 Dijkstra, Louis + Gerke, Paul K.

4 Neutelings, Jascha + Versteeg, Reinout 5 Haga, Femke + Verdonschot, Taco 6 Barth, Ruud + Wolterink, Jelmer 7 Dijk, Els van + Vroon, Jered 8 Fang, Yuen + Hofman, Jorien 9 Janssen, Loth + Petrachi, Daniel 10 Roerdinkholder, Hans

(7)

37

)

U

1

*

1 ,

I + 9:"1

(&

L = F F * 1 # I #R I I I@2 I 1 I * 1 =( P = = @2 # I @2 #R 8 * * = @1 ( 6 ( 1 & = solost.science.ru.nl * 5& /vol/www/B3test/web-docs/group’n’ = 1 = & * 1& * http://www.B3test.science.ru.nl/group'n' * 1 ( ) ' (* 1 ' ( ( * W * 1 = ( 38

)

U

1

*

1 ,

I + 9:"1

+ = C +" 1 ' + 9:"1 * ( V ' XX = F >

Zorg ervoor, dat alle gegevens (per lid 9 !) in een nette tabel getoond worden. Gebruik webpagina’s die naar eigen ‘opmaak’-voorkeur via PHP (met ‘include’-opdrachten en styles; géén frames meer) gegenereerd worden (test dingen eerst ‘kaal’ uit).