introduction to php/ mysql i

INTRODUCTION TO PHP/MYSQL I

FTSM Lab / May 2011

Goal Provide the basic knowledge of PHP

programming Explain how you can code and run PHP

scripts Creating dynamic pages Basic database interactions – read, insert,

update and delete data Basic session management PHP Application packages

StructureHTML/XHTML

CSS

JavaScript

DatabasePHP

HTML/JavaScript/CSS http://www.w3schools.com HTML, JavaScript and CSS tutorials

Client-server architecture

What is PHP? PHP = ‘Hypertext PreProcessor’ Originally created by Rasmus

Lerdorf in 1994 The main implementation of PHP

is now produced by The PHP Group (de facto standard for PHP) - http://www.php.net/

Open-source (access to source code and free distribution right), server-side scripting language

How PHP works

PHP on Windows

Method1

IIS

FastCGI + PHP

mySQL

Method2

Apache

PHP

mySQL

Method3WAMP

Packages

Method4

Web Hosting

How to run PHP scripts

Webhosting Webhosting information

WAMP Packages - XAMPP http://www.apachefriends.org/en/

index.html

Version for Windows includes: Apache, MySQL, PHP, Perl, phpMyAdmin, JpGraph, FileZilla FTP Server, SQLite etc.

WAMP Packages - WAMPServer http://www.wampserver.com/en/

Version for Windows includes: Apache, PHP, Mysql (version 64 and 32 bits), PhpMyadmin, SQLBuddy, XDebug, webGrind, XDC

Basic syntaxes, data types, variable, control structures, arrays, function

PHP Scripts

PHPVARIABLESBASIC

SYNTAX DATA TYPE

FUNCTIONCONTROL STATEMEN

TSDATABASE

CONNECT/ READ INSERT UPDAT

EDELET

E

SESSION PHP PACKAGES

PHP code Structurally similar to C/C++ All PHP statements end with a semi-colon Each PHP script must be enclosed in the

reserved PHP tag<?php……?>

PHP code - comments Standard C, C++, and shell comment

symbols// C++ and Java-style comment

# Shell-style comments

/* C-style comments These can span multiple lines */

PHP code - output

<?php$nilai = 25; // Numerical variable$ayat = “Hello”; // String variable

echo $ayat; // Outputs Helloecho $nilai, $ayat; // Outputs 25Helloecho “5x5=”,$nilai; // Outputs 5x5=25echo “5x5=$nilai”; // Outputs 5x5=25echo ‘5x5=$nilai’; // Outputs 5x5=$nilai?>

Use ‘echo’ or ‘print’ Strings in single quotes (‘ ’) are not

interpreted or evaluated by PHP

If the string has a set of double quotation marks that must remain visible, use the \ [backslash] before the quotation marks to ignore and display them.<?php

$jab=“\”PHP\””;

print $jab; //”PHP”

?>

PHP – escape character

PHP code - variables PHP variables must begin with a “$” sign Case-sensitive ($var != $VAR != $vAr) Global and locally-scoped variables

Global variables can be used anywhere Local variables restricted to a function or

class Certain variable names reserved by PHP

Form variables ($_POST, $_GET) Server variables ($_SERVER)

PHP code – variables<?php

$nilai = 25; //Numerical variable$ayat = “Hello”; //String variable

$nilai = ($nilai * 7); //Multiplies variable nilai by 7

?>

PHP code - operations<?php

$a=30;$b=5;$total=$a+$b;print $total; //35print “<p>Jumlah ialah $total</p>”;

// Jumlah ialah 35

print $a-$b; //25print $a*$b; //150print $a/$b; //6print $a+=$b; //35print $a%$b; //0

?>

Use a period to join strings into one.<?php

$string1=“Hello”;$string2=“PHP”;

$string3=$string1 . “ ” . $string2;

print $string3; //Hello PHP

?>

PHP code – strings function

PHP – Control Statements Control structures similar with

JavaScript/C++ if, elseif, else switch, case while for foreach

PHP - if, elseif, else

<?php

$markah = 90;

if ($markah >= 80)echo “Lulus dengan cemerlang";

elseif ($markah >= 40)   echo “Lulus";else   echo “Gagal";

?>

PHP control – switch

…switch ($jantina){

case “L”:   echo “Lelaki";   break;

case “P”:   echo “Perempuan";   break;

default:   echo “Tiada input jantina";

}…

PHP control – while loops

<?php

$nombor = 1;

while ($nombor!=10){print “Bilangan $nombor”;$nombor++;}

?>

PHP control – for loops

<?php

for ($n = 1; $n<=10; $n++){print “Bilangan $n”;}

?>

PHP control – foreach loops<?php

$numbers = array("one","two","three");

foreach ($numbers as $value) {echo $value . "<br />";

  }

?>

PHP arrays Three kind of arrays:

Numeric array - An array with a numeric index

Associative array - An array where each ID key is associated with a value

Multidimensional array - An array containing one or more arrays

PHP – numeric arrays

<?php

//numeric array$cars = array("Saab","Volvo","BMW",“Ford");

echo $cars[2]; //BMW

?>

PHP – associative arrays

<?php

//associative array

$umur = array ("Raju"=>32, "Gopal"=>34, "Samy" => 36); //same as $umur[‘Raju’]=32…

echo $umur[‘Gopal’]; //34

?>

PHP - multi dimensional arrays<?php

//multidimensional array

$kump = array ("Merah"=> array ("Ali", "Raju", "Joan"), "Biru"=> array ("Abu", "Jason", "Lin"), "Hijau" => array ("David", "Jim", "Mary");

echo $kump [‘Merah’][2]; //Joanecho $kump [‘Hijau’][0]; //David

?>

PHP array functions array_push() – adds element/s to an array

<?php

$a=array("Dog","Cat");array_push($a,"Horse","Bird");print_r($a);

/*Array ([0]=>Dog [1]=>Cat [2]=>Horse [3]=>Bird) */

?>

PHP array functions array_pop() – deletes last element in an array

<?php

$a=array("Dog","Cat","Horse");array_pop($a);print_r($a);

// Array ([0]=>Dog [1]=>Cat)

?>

PHP array functions unset() – destroy a variable$array = array(0, 1, 2, 3);unset($array[2]);

/* array(3) { [0]=>int(0)

[1]=>int(1) [3]=>int(3)} */

Functions MUST be defined before they can be called

Function headers are of the format

function function_name ($var1, $var2…){

…}

Function names are not case sensitive

PHP - functions

<?php// This is a functionfunction darab($arg1, $arg2){

$arg3 = $arg1 * $arg2;return $arg3;}

echo darab(12,3); // 36?>

PHP - functions

PHP - Using external files Using external files for:

HTML codes Structure – template files like headers, footers Functions – separate file to store all functions Config – separate configurationsettings in different file

PHP – include and require Four functions:

include() include_once() require() require_once()

Generates warnings when the function doesn’t work

Generates errors and halts scripts when the function doesn’t work

Using “include” to include external files<?phpinclude “header.php”Include “tarikh.php”include “menubar.php”…?>

<?phpprint “Tarikh hari ini ialah $date2”;

?>

PHP - include

PHP References• http://www.php.net <- php home page• http://www.php.net/downloads <- php

download page• http://www.php.net/manual/en/install.wind

ows.php <- php installation manual

• http://www.w3schools.com/php/default.asp <-php online tutorial

SQL, MySQL, phpMyAdmin, creating database and tables

Database and SQL

SQL SQL – Structured Query Language SQL can be used to access and

manipulate databases

SELECT * FROM pelajar

SELECT * FROM pelajarWHERE NoMatrik=‘A123456’

SQL Queries Query database for specific information

and have a recordset returned from table

SELECT nama FROM pelajar

namaJalil

GopalAh Meng

SQL Keywords SELECT – select from tables FROM – specifies tables WHERE – specifies criteria

INSERT – inserts data into table UPDATE – updates data in table DELETE – deletes data in table

CREATE – create new table DROP – delete existing table

SQL Keywords UsageSELECT * FROM pelajarOutput: select all columns from table pelajar

SELECT nama, nomatrik FROM pelajarOutput: select columns nama and nomatrik from table

pelajar

SELECT nama, nomatrik FROM pelajarWHERE nomatrik=‘A12345’Output: select columns nama, nomatrik from table

pelajar where row nomatrik equals ‘A12345’

SQL Keywords UsageINSERT INTO pelajar (nomatrik, nama,

jabatan, kumpulan)VALUES (‘A12365’, ‘Hashim’, ‘4’, ‘9’) Action: insert specified value to table pelajar,

creating new row

DELETE FROM pelajarWHERE nomatrik=‘A12369’Action: select row from table pelajar where

nomatrik as specified and delete the row

SQL Keywords UsageUPDATE pelajar SET nama = ‘Hisham’WHERE nama = ‘Hashim’ AND

nomatrik = ‘A12365’Action: update specified row to table

pelajar

MySQL MySQL - "My Structured Query Language“

Created by Michael Widenius from TcX (Sweden) in 1994

Open-source, relational database management system

MySQL is used in web applications and acts as the database component of the WAMP/LAMP

Used in free software projects (e.g. WordPress, Joomla)

MySQL and WAMP/LAMP Download at www.mysql.com

MySQL Interfaces Interfaces to manage and browse

database easily phpMyAdmin heidiSQL MySQL-Front SQLyog

phpMyAdmin

Creating DatabaseCREATE DATABASE databasename

Creating Tables Some of MySQL data types:

Type DescriptionCHAR [length]

Fixed-length, 0 to 255 characters long

VARCHAR [length]

Variable-length, 0 to 255 characters long

TEXT String, maximum 65,535 characters

INT [length] -2.147 millions to 2,147 millions

DATE YYYY-MM-DD formatTIME HH:MM:SS

Creating Tables1. Choose a suitable name and create

table2. Identify columns names3. Identify data types4. Identify suitable MySQL data type5. Identify suitable length

Creating Tables Creating table for a guestbook

application

Creating Tables Identifying column general data type

Column name

Type

ID NumberNAMA TextEMAIL TextTARIKH Date/timeKOMEN Text

Table: guestbookColumn name

Type MySQL data type

ID Number INTNAMA Text VARCHAR [40]EMAIL Text VARCHAR [40]TARIKH Date/time DATETIMEKOMEN Text TEXT

guestbookID NAMA EMAIL TARIKH KOMEN1 Simon simon@yahoo.

com2010-09-

21 07:40:48

Excellent website! Well done!

2 Azizi azizi@hotmail.com

2010-09-23

10:20:48

Sila lawati laman web saya:

azizi.com.my3 Wei

Yoongwy@gloomy.co

.uk2010-09-

23 10:45:12

Still waiting for the updates :D

Read, insert, update and delete data using PHP

PHP and MySQL

DELETE DATACREATE

CONNECTION

CLOSE CONNECTION

INSERT DATA

READ DATA

UPDATE DATA

PHP Creating & Closing Connection Use mysql_connect() and mysql_close()

$con = mysql_connect (servername, username, password);

if (!$con) {die('Could not connect: ' . mysql_error());}

…mysql_close($con);

PHP Selecting Database Use mysql_select_db()

…$con = mysql_connect (servername, username, password);

mysql_select_db (databasename, $con);…

PHP Displaying data Use mysql_query() to run SQL The return result are usually in array

form$result = mysql_query ("SELECT * FROM guestbook");

while($row = mysql_fetch_array ($result)) {echo $row[‘NAMA'] . “-" . $row[‘EMAIL'] .

”-” . row[‘TARIKH'] . “-" . $row[‘KOMEN'];  }

PHP Inserting data

…//run querymysql_query("INSERT INTO guestbook(ID, NAMA, EMAIL, TARIKH, KOMEN) VALUES (5, ‘Jason', ‘jason@gmail.com‘, NOW(), ‘Website yang bagus!’)");…

Use mysql_query()

PHP Inserting data

//Create query$qry = "INSERT INTO guestbook (ID, NAMA, EMAIL, TARIKH, KOMEN) VALUES (5, ‘Jason', ‘jason@gmail.com‘, NOW(), ‘Website yang bagus!’)”;

//Run querymysql_query($qry);…

PHP Inserting data from Form

HTML FORM PHP

PHP Inserting data from Form

PHP Inserting data from Form

…  $sql="INSERT INTO guestbook (NAMA, EMAIL, TARIKH, KOMEN)VALUES ('$_POST[nama]', '$_POST[email]‘, '$_POST[tarikh]‘, '$_POST[komen]')";…

PHP Displaying data in table

…echo "<table>"; while($row = mysql_fetch_array($result)) {

echo "<tr>";echo "<td>" . $row[‘NAMA'] . "</td>";echo "<td>" . $row[‘EMAIL'] . "</td>";echo "<td>" . $row[‘TARIKH'] . "</td>";echo "<td>" . $row[‘KOMEN'] . "</td>";echo "</tr>";  }

echo "</table>"; …

PHP Updating data Use UPDATE /SET /WHERE to update data

mysql_query (“UPDATE guestbook SET EMAIL = ‘simon_new@yahoo.com’ WHERE NAMA = ‘Simon‘ ");  }

PHP Deleting data Use DELETE FROM /WHERE to delete

data from database…mysql_query (“DELETE FROM guestbook WHERE Nama = ‘Simon‘ ");…

Update/Delete in Form Modify display table form to incorporate

update/delete functions//make a link at the end of each table row for UPDATE and DELETE…echo “<td>”;echo "<a href='delete.php?cmd=delete&id=$id‘>Delete</a>"; echo “</td><td>”;echo "<a href=‘update.php?&id=$id‘>Delete</a>"; echo "<br>";

if($_GET["cmd"]=="delete") {…}

HTTP Requests GET

Gets/retrieves information from server (e.g retrieve an image, fetch search results)

Sends information as part of URL (visible, 2048 chars limit)

Cached, bookmark-able POST

Posts/sends data (e.g login information, post form data)

Submitted data is hidden (invisible) Non-cached, non-bookmark-able

Update ProcessSelect data by primary key using SQL SELECT

Show data in HTML form to modify elements

Use SQL UPDATE to update data

User sessions

PHP Session Management

Sessions Using session identifier in server to

locate and store user data Advantage over cookies:

More secure – all data stored in server Not browser/computer dependent – some

users reject cookies/turn off Store more data than cookie

Use session_start() and $_SESSION[variable]

Sessions

…//login processsession_start();$_SESSION[‘user_id’] = A11201;…

…session_start();If (isset($_SESSION[‘user_id’])){…

…//reset session array$_SESSION = array();//delete sessionsession_destroy();…

Installing packages, customizing packages

PHP Application Packages

PHP packages Useful and easy to install PHP

applications Phpmyadmin Joomla modx phpBB Wordpress Mediawiki zend

phpMyadmin MySQL interface Deployment:

Download from site Unzip to web document root

Joomla Multi-user CMS Deployment:

Download and unzip inweb document root Launch install script

modx Multi-user CMS

phpBB Bulletin board

Wordpress Blogging application -

http://www.wordpress.org

MediaWiki Wiki -

http://www.mediawiki.org/wiki/MediaWiki

Zend http://framework.zend.com

THANK YOU