TP 2543 Web Programming Introduction to PHP and

TP 2543 Web Programming Introduction to PHP and DATABASE

Questions… � What � Why would you need PHP? � What �. . . is PHP? are things that you can do with PHP? And can’t do with PHP?

Goal �Provide the basic knowledge of PHP programming… ◦ Not to teach everything about PHP ◦ Explain how you can code and run PHP scripts ◦ Explain code of examples ◦ Provide useful references � …and related concepts

So far… HTML/ XHTML Structure/Contents CSS Presentation/Layout Java. Script & DOM Action/Interactivity

and now… HTML/ XHTML CSS Java. Script PHP Database

For PHP Programming CLIENT-SERVER ARCHITECTURE

Related concepts � Web servers � HTTP Requests � Client side vs server side processing � Database � SQL

Client-Server WEB SERVERS FTP DATABASE SQL IIS My. SQL Apache Deploying PHP packages SERVERSIDE SCRIPTING PHP

Web Servers � A web server can be referred to as either the hardware (the computer) or the software (the computer application) that helps to deliver content that can be accessed through the Internet. A web server is what makes it possible to be able to access content like web pages or other data from anywhere as long as it is connected to the internet. The hardware houses the content, while the software makes the content accessible through the internet. � The most common use of web servers is to host websites but there are other uses like data storage or for running enterprise applications. There also different ways to request content from a web server. The most common request is the Hypertext Transfer Protocol (HTTP), but there also other requests like the Internet Message Access Protocol (IMAP) or the File Transfer Protocol (FTP).

Web Servers �Responds to client (browser) requests to provide resources (HTML documents) �Communicates using HTTP �Introducing 2 types of web server: ◦ Internet Information Services (windows) ◦ Apache Web Server (open-source for both linux and windows machine)

Web Server � Below is the most recent statistics of the market share of the top web servers on the internet by Netcraft survey in November 2010 Vendor Product Web Sites Hosted Percent Apache 148, 085, 963 59. 36% Microsoft IIS 56, 637, 980 22. 70% Igor Sysoev nginx 15, 058, 114 6. 04% Google GWS 14, 827, 157 5. 94% lighttpd 2, 070, 300 0. 83%

HTTP Requests � GET ◦ Get (retrieves) information from a server ◦ GET request method where only a URL and headers are sent to the server ◦ GET method is less secure, opening up your form to possible hacker activity. ◦ Sends information as part of URL (visible, 2048 chars limit) ◦ Sends information as part of URL �www. search-engine. com/search? name=value

HTTP Requests � POST ◦ POST request method is used when the client needs to send data to the server as part of the request, such as when uploading a file or submitting a completed form. ◦ POST requests also include a message body ◦ This allows for arbitrary length data of any type to be sent to the server ◦ Headers in the POST request may indicate the message body's Internet media type to the server. Comparison Post and Get : http: //www. cs. tut. fi/~jkorpela/forms/methods. html

HTTP Requests � Browsers often cache (save on disk) Web pages ◦ Quickly reload the page (speed up browsing experience) ◦ Browser asks the server if the document has changed or expired ◦ If not, the browser loads the document from the cache

Client Side vs Server Side �Client-side ◦ Validates input ◦ Source code is visible ◦ Limited to browser support ◦ Reduce requests needed to be passed to server ◦ Java. Scipt, VBScript �Server-side ◦ Access server directory, write to database ◦ Source code is invisible ◦ Wider-range of programmatic capabilities ◦ Executed on server ◦ Generate custom response for clients ◦ Access to server-side software that extends server functionality

System Architecture � Multi-tier application (n-tier application): ◦ Information tier (data or bottom tier) �Maintains data for the application �Stores data in a relational database management system (RDBMS) ◦ Middle tier �Implements business logic and presentation logic �Control interactions between application clients and application data ◦ Client tier (top tier) �Application’s user interface �Users interact directly with the application through the client tier 16

Client tier (top tier) Middle tier Information tier (data or bottom tier) 17

Database and SQL � Database – an integrated collection of data � DBMS – mechanism to store and organize data according to database format � Relational databases – popular ◦ Uses SQL (Structured Query Language) to perform queries, manipulate data ◦ MS Access, My. SQL, MS SQL, Oracle, Sybase, DB 2

Web Resources � www. microsoft. com/msdownload/ntoptionpack/askwi z. asp � www. w 3. org/Protocols � www. apache. org � httpd. apache. org/docs-2. 0 � www. apacheweek. com � linuxtoday. com/stories/18780. html � www. iisanswers. com � www. iisadministrator. com 19

An introduction PHP PROGRAMMING

What is PHP? PHP = ‘Hypertext Pre. Processor’ � 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 � Rasmus Lerdorf, who wrote the original Common Gateway Interface component, and Andi Gutmans and Zeev Suraski, who rewrote the parser that formed PHP 3

What is PHP? � Used to generate (and write to file) dynamic web-pages and web contents ◦ Text files – HTML, XML… ◦ Images – JPG, PNG… ◦ PDF files ◦ Flash movies � File extension - *. php

How PHP works

PHP Scripts � PHP scripts reside between reserved PHP tags within HTML pages <? php print (“$nama_saya”); ? > � Interpreted language, scripts are parsed at runtime rather than compiled before hand � Source-code not visible by client � Various built-in functions allow for fast development � Compatible with many popular databases

How to run PHP scripts � A web server with PHP support, you can: ◦ Set up your own server… (M 1/M 2/M 3) ◦ …or find a webhosting plan with PHP support (M 4)

How to run PHP scripts PHP on Windows M 1 M 2 IIS Apache Fast. CGI + PHP my. SQL M 3 WAMP Packages M 4 Web Hosting

How to run PHP scripts �LAMP – Linux + Apache + My. SQL + PHP operating system language web server database �…as scripting opposed to WISA – Windows + IIS + MS SQL Server + ASP �WAMP for Windows platform

XAMPP � http: //www. apachefriends. org/en/index. html � Version for Windows includes: Apache, My. SQL, PHP, Perl, php. My. Admin, Jp. Graph, File. Zilla FTP Server, SQLite etc.

WAMP ◦ http: //www. wampserver. com/en/

CODING IN PHP

PHP BASIC SYNTAX DATA TYPE FUNCTION VARIABLES CONTROL STATEMENT S DATABASE CONNECT/ READ SESSION INSERT UPDATE DELETE 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 Use ‘echo’ or ‘print’ � Strings in single quotes (‘ ’) are not interpreted or evaluated by PHP <? php $nilai = 25; // Numerical variable $ayat = “Hello”; // String variable � echo echo ? > $ayat; $nilai, $ayat; “ 5 x 5=”, $nilai; “ 5 x 5=$nilai”; ‘ 5 x 5=$nilai’; // Outputs Hello // Outputs 25 Hello // Outputs 5 x 5=25 // Outputs 5 x 5=$nilai

PHP – escape character � 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 code - variables � PHP variables must begin with a “$” sign � Case-sensitive ($var != $VAR != $v. Ar) � 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)

Local Scope <? php $a = 5; // global scope function my. Test(){ echo $a; // local scope } my. Test(); ? > <? php $a = 5; $b = 10; function my. Test() { global $a, $b; $b = $a + $b; } my. Test(); echo $b; ? > Global Scope

<form action="welcome. php" method="get"> Name: <input type="text" name="fname" /> Age: <input type="text" name="age" /> <input type="submit" /> </form> When the user clicks the "Submit" button, the URL sent to the server could look something like this: http: //www. w 3 schools. com/welcome. php? fname=Peter&age=37 The "welcome. php" file can now use the $_GET variable to collect form data (the names of the form fields will automatically be the keys in the $_GET array): Welcome <? php echo $_GET["fname"]; ? >. You are <? php echo $_GET["age"]; ? > years old!

PHP code – variables <? php $nilai = 25; $ayat = “Hello”; //Numerical variable //String variable $nilai = ($nilai * 7); // Multiplies variable // nilai by 7 ? >

PHP code - operations <? php $a=30; $b=5; $total=$a+$b; print $total; //35 print “<p>Jumlah ialah $total</p>”; // Jumlah ialah 35 print print ? > $a-$b; $a*$b; $a/$b; $a+=$b; $a%$b; //25 //150 //6 //35 //0

Type � basic types: int, float, boolean, string, array, object, NULL ◦ test what type a variable is with is_type functions, e. g. is_string ◦ gettype function returns a variable's type as a string (not often needed) � PHP converts between types automatically in many cases: ◦ string → int auto-conversion on + ◦ int → float auto-conversion on / � type-cast with (type): ◦ $age = (int) "21";

String print "text"; print "Hello, World!n"; print "Escape "chars" are the SAME as in Java!n"; print "You can have line n breaks in a string. "; print 'A string can use "single-quotes". It's cool!'; Hello, World! Escape "chars" are the SAME as in Java You can have line breaks in a string. A string can use "single-quotes". It's cool!

PHP code – strings function �Use a period to join strings into one. <? php $string 1=“Hello”; $string 2=“PHP”; $string 3=$string 1. “ ”. $string 2; print $string 3; //Hello PHP ? > $favorite_food = "Ethiopian"; print $favorite_food[2]; #h

PHP – Control Statements �Control structures similar with Java. Script/C++ ◦ if, else ◦ switch, case ◦ while ◦ foreach

PHP - if, else <? php $markah = 90; if ($markah >= 80) echo “Lulus dengan cemerlang"; elseif ($markah >= 40) echo “Lulus"; else echo “Gagal"; ? >

PHP control – switch $jantina = "L"; 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. " "; } ? >

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(“Toyota", "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(“Ahmad“, “Muthu“, "Mary"); echo $kump [‘Merah’][2]; echo $kump [‘Hijau’][0]; ? > //Joan //Ahmad

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() – delete 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) } */

PHP - functions � � � Functions MUST be defined before they can be called Function headers are of the format function_name ($var 1, $var 2…) { … } Function names are not case sensitive

PHP - functions <? php // This is a function darab($arg 1, $arg 2) { $arg 3 = $arg 1 * $arg 2; return $arg 3; } echo darab(12, 3); // 36 ? >

Math operations $a = 3; $b = 4; $c = sqrt(pow($a, 2) + pow($b, 2));

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

PHP expression blocks <? = expression ? > <h 2> The answer is <? = 6 * 7 ? > </h 2> 42 //The answer is PHP expression block: a small piece of PHP that evaluates and embeds an expression‘s value into HTML • <? = expression ? > is equivalent to: <? php print expression; ? > • useful for embedding a small amount of PHP (a variable's or expression's value) in a large block of HTML without having to switch to "PHP-mode"

PHP expression blocks <body> <? php for ($i = 1; $i <= 3; $i++) { ? > <h<? = $i ? >>This is a level <? = $i ? > heading. </h<? = $i ? >> <? php } ? > </body>

PHP Reference � PHP � PHP � PHP Array Calendar Date Directory Error Filesystem Filter FTP HTTP • • • PHP Libxml PHP Mail PHP Math PHP Misc PHP My. SQL PHP Simple. XML PHP String PHP XML PHP Zip http: //www. w 3 schools. com/php/default. asp

PHP References • http: //www. php. net <- php home page • http: //www. php. net/downloads <- php download page • http: //www. php. net/manual/en/install. windows. php < - php installation manual

TP 2543 Web Programming Database: PHP SQL My. SQL 66

OBJECTIVES In this chapter you will learn: � Relational database concepts. � To use Structured Query Language (SQL) to retrieve data from and manipulate data in a database. 1. 2. 3. 4. Introduction Relational Databases Relational Database Overview: The books Database SQL • Basic SELECT Query • WHERE Clause • ORDER BY Clause • Combining Data from Multiple Tables: INNER JOIN • INSERT Statement • UPDATE Statement • DELETE Statement 67

Introduction � A database is an integrated collection of data. A database management system (DBMS) provides mechanisms for storing, organizing, retrieving and modifying data. � Today’s most popular database management systems are relational database systems. � SQL is the international standard language used almost universally with relational database systems to perform queries and manipulate data. � Programs connect to, and interact with, relational databases systems via an interface—software that facilitates communications between a database management system and a program. 68

Introduction Database � A database is an organized collection of data. Think of it as a well-labeled filing cabinet � A database consists of one or more tables. . Table � A table holds data on one specific type of data, for example customers or invoices. � Think of the table as one drawer in the filing cabinet Row � A table consists of zero or more rows(also called records) � A row represents one specific data item in the collection, such as a single customer or a single invoice. � Think of a row as one folder in the drawer.

Introduction Column � A table row consists of one or more columns. A column represents a specific piece of information about the data, such as the customer's name or the due date of an invoice � Every column has a well-defined data type that determines what kind of data is stored in that column. For example a column may be defined to hold only dates, or only integers. � Within a single table all rows have the same type and number of columns. � • You can also imagine the rows & columns of a table as being like a spreadsheet, but with more stringent restrictions on what kinds of data can go into the cells. �

Database : Column Jawatan Fakulti Bidang UKMPer Nama Jantina Alamat Emel Telefon Tlahir Trk. Lahir ….

Introduction Data Types � char/varchar/nvarchar – stores text strings. int – stores integers. Called numberin Oracle � float - stores decimal numbers � Date – Stores datetime values. By default only shows date as “DD-MON-YY”. � When giving values in a SQL statement (such as in INSERT or UPDATE) you must put single quotes (') around all char and date values. Numeric values should not be quoted. � NULL is always NULL. �

Relational Data � A relational database stores data in tables. Tables are composed of rows, and rows are composed of columns in which values are stored. � A primary key is provides unique values that cannot be duplicated in other rows of the same table. � Each column of a table represents a different attribute in a row of data. � The primary key can be composed of more than one column. � SQL provides a rich set of language constructs that enable you to define complex queries to retrieve data from a database. 73

Relational Data � Every column in a primary key must have a value, and the value of the primary key must be unique. This is known as the Rule of Entity Integrity. � A one-to-many relationship between tables indicates that a row in one table can have many related rows in a separate table. � A foreign key is a column in a table that matches the primarykey column in another table. � The foreign key helps maintain the Rule of Referential Integrity: Every foreign-key value must appear as another table’s primary-key value. Foreign keys can be used to combine information from multiple tables. There is a one-tomany relationship between a primary key and its corresponding foreign key. 74

Example Employee table sample data. 75

Example Result of selecting distinct Department and Location data from table Employee. 76

Relational Database Overview : A books Database � Foreign keys also allow related data in multiple tables to be selected from those tables for analytic purposes — this is known as joining the data. authors table from the books database. 77

Relational Database Overview : A books Database titles table from the books database. 78

Relational Database Overview : A books Database author. ISBN table from the books database. 79

Relational Database Overview : A books Database � An entity-relationship (ER) diagram shows the database tables and the relationships among them. � Every row must have a primary-key value, and that value must be unique in the table. This is known as the Rule of Entity Integrity. � An infinity symbol (∞) indicates a one-to-many relationship, in which an entry from a table can have an arbitrary number of entries in another table. � A many-to-many relationship indicates that multiple entries can be related between tables. 80

Relational Database Overview : A books Database One-to-many relationship Primary keys Foreign keys One-to-many relationship Table relationships in the books database. 81

SQL (Structured Query Language) The next several sections will discuss most of the keywords listed in the following slide in the context of SQL queries and statements. SQL query keywords. � 82

Basic SELECT Query � The basic form of a query is SELECT * FROM table. Name where the asterisk (*) indicates that all columns from table. Name should be selected, and table. Name specifies the table in the database from which rows will be retrieved. � To retrieve specific columns from a table, replace the asterisk (*) with a comma-separated list of column names. 83

SELECT author. ID, last. Name FROM authors Sample author. ID and last. Name data from the authors table. 84

WHERE Clause � The optional WHERE clause in a query specifies the selection criteria for the query. The basic form of a query with selection criteria is SELECT column. Name 1, column. Name 2, … FROM table. Name WHERE criteria The WHERE clause can contain operators <, >, <=, >=, =, <> and LIKE. Operator LIKE is used for string pattern matching with wildcard characters percent (%) and underscore (_). � A percent character (%) in a pattern indicates that a string matching the pattern can have zero or more characters at the percent character’s location in the pattern. � An underscore (_) in the pattern string indicates a single character at that position in the pattern. � 85

SELECT Title, edition. Number, copyright FROM Titles WHERE copyright > '2005' Sampling of titles with copyrights after 2005 from table titles. 86

SELECT Author. ID, First. Name, Last. Name FROM authors WHERE Last. Name LIKE 'D%' Authors whose last name starts with D from the authors table. 87

SELECT Author. ID, First. Name, Last. Name FROM authors WHERE Last. Name LIKE '_o%' The only author from the authors table whose last name contains o as the second letter. 88

ORDER BY Clause � The result of a query can be sorted in ascending or descending order using the optional ORDER BY clause. The simplest form of an ORDER BY clause is SELECT column. Name 1, column. Name 2, … FROM table. Name ORDER BY column ASC SELECT column. Name 1, column. Name 2, … FROM table. Name ORDER BY column DESC where ASC specifies ascending order, DESC specifies descending order and column specifies the column on which the sort is based. The default sorting order is ascending, so ASC is optional. � Multiple columns can be used for ordering purposes with an ORDER BY clause of the form ORDER BY column 1 sorting. Order, column 2 sorting. Order, … � The WHERE and ORDER BY clauses can be combined in one query. If used, ORDER BY must be the last clause in the query. 89

SELECT Author. ID, First. Name, Last. Name FROM authors ORDER BY Last. Name ASC authors sample data in ascending order by last. Name. 90

SELECT Author. ID, First. Name, Last. Name FROM authors ORDER BY Last. Name DESC authors sample data in descending order by last. Name. 91

SELECT Author. ID, First. Name, Last. Name FROM authors ORDER BY Last. Name, First. Name authors sample data in ascending order by last. Name and first. Name. 92

SELECT ISBN, Titles, Edition. Number, Copyright FROM titles WHERE Titles LIKE '%How to Program' ORDER BY Titles ASC Sampling of books from table titles whose titles end with How to Program in ascending order by title. 93

Combining Data from Multiple Tables: INNER JOIN � An INNER JOIN combines rows from two tables by matching values in columns that are common to the tables. The basic form for the INNER JOIN operator is: SELECT column. Name 1, column. Name 2, … FROM table 1 INNER JOIN table 2 ON table 1. column. Name = table 2. column. Name The ON clause specifies a condition that determines which rows are joined. This condition often compares columns from each table If a SQL statement uses columns with the same name from multiple tables, the column names must be fully qualified by prefixing them with their table names and a dot (. ). 94

SELECT First. Name, Last. Name, ISBN FROM authors INNER JOIN author. ISBN ON authors. Author. ID = author. ISBN. Author. ID ORDER BY Last. Name, First. Name Sampling of authors and ISBNs for the books they have written in ascending order by last. Name and first. Name. 95

INSERT Statement � An INSERT statement inserts a new row into a table. The basic form of this statement is INSERT INTO table. Name ( column. Name 1, column. Name 2, …, column. Name. N ) VALUES ( value 1, value 2, …, value. N ) where table. Name is the table in which to insert the row. The table. Name is followed by a comma-separated list of column names in parentheses. The list of column names is followed by the SQL keyword VALUES and a comma-separated list of values in parentheses. � SQL uses single quotes (') as the delimiter for strings. To specify a string containing a single quote in SQL, the single quote must be escaped with another single quote. 97

INSERT INTO Authors ( First. Name, Last. Name ) VALUES ( 'Sue', 'Smith' ) Sample data from table Authors after an INSERT operation. 98

UPDATE Statement � An UPDATE statement modifies data in a table. The basic form of an UPDATE statement is UPDATE table. Name SET column. Name 1 = value 1, column. Name 2 = value 2, …, column. Name. N = value. N WHERE criteria where table. Name is the table in which to update data. The table. Name is followed by keyword SET and a commaseparated list of column name/value pairs in the format column. Name = value. The optional WHERE clause criteria determines which rows to update. 99

UPDATE authors SET Last. Name = 'Jones' WHERE Last. Name = 'Smith' AND First. Name = 'Sue' Sample data from table authors after an UPDATE operation. 100

DELETE Statement � A DELETE statement removes rows from a table. The simplest form for a DELETE statement is DELETE FROM table. Name WHERE criteria where table. Name is the table from which to delete a row (or rows). The optional WHERE criteria determines which rows to delete. If this clause is omitted, all the table’s rows are deleted. 101

DELETE FROM authors WHERE Last. Name = 'Jones' AND First. Name = 'Sue' Sample data from table authors after a DELETE operation. 102

My. SQL � My. SQL ◦ ◦ - "My Structured Query Language“ Created by Michael Widenius from Tc. X (Sweden) in 1994 Open-source, relational database management system My. SQL is used in web applications and acts as the database component of the WAMP/LAMP Used in free software projects (e. g. Word. Press, Joomla)

php. My. Admin

php. My. Admin (WAMP)

Creating Database CREATE DATABASE databasename

Creating Tables �Some of My. SQL data types: Type Description CHAR [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 format TIME HH: MM: SS

Creating Tables 1. 2. 3. 4. 5. Choose a suitable name and create table Identify columns names Identify data types Identify suitable My. SQL data type Identify suitable length

Creating Tables �Creating table for a guestbook application

Creating Tables �Identifying column general data type Column name Type ID Number NAMA Text EMAIL Text TARIKH Date/time KOMEN Text

Table: guestbook Column name Type My. SQL data type ID Number INT NAMA Text VARCHAR [40] EMAIL Text VARCHAR [40] TARIKH Date/time DATETIME KOMEN Text TEXT

guestbook ID NAMA EMAIL TARIKH KOMEN 1 Simon simon@yahoo. c om 2010 -0921 07: 40: 48 Excellent website! Well done! 2 Azizi azizi@hotmail. c om 2010 -0923 10: 20: 48 Sila lawati laman web saya: azizi. com. my 3 Wei Yoong wy@gloomy. co. uk 2010 -0923 10: 45: 12 Still waiting for the updates : D

CREATE CONNECTION SORT DATA DELETE DATA INSERT DATA SELECT DATA UPDATE DATA CLOSE CONNECTION

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 �Use mysql_query() … //run query mysql_query(“ INSERT INTO guestbook (ID, NAMA, EMAIL, TARIKH, KOMEN) VALUES (5, ‘Jason', ‘jason@gmail. com‘, NOW(), ‘Website yang bagus!’)"); …

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 query mysql_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 echo "<tr>"; echo '<td>'. mysql_result($result, $i, 'id'). '</td>'; echo '<td>'. mysql_result($result, $i, 'firstname'). '</td>'; echo '<td>'. mysql_result($result, $i, 'lastname'). '</td>'; echo '<td><a href="edit. php? id='. mysql_result($result, $i, 'id'). '">Edit</a></td>'; echo '<td><a href="delete. php? id='. mysql_result($result, $i, 'id'). '">Delete</a></td>'; echo "</tr>"; $id = $_GET['id']; $result = mysql_query("DELETE FROM players WHERE id=$id") http: //www. killersites. com/community/index. php? /topic/1969 -basic-php-system-vieweditdeleteadd-records/

Reference http: //www. tizag. com/mysql. Tutorial � http: //www. w 3 schools. com/php_ajax_database. asp �

data. html Outline (1 of 2) Posts data to database. php 128

Outline data. html (2 of 2) Creates drop-down menu specifying which data to output to the screen, with * (all data) as the default selection 129

Outline database. php (1 of 3) Builds a SELECT query with the selection made in data. html 130

Outline database. php (2 of 3) Connects to database using server hostname localhost and username and password Specifies products as “iw 3 htp 4” the database to be Returns any error strings queried from the database Queries $database Closes the connection to $query with the database Returns an array with the values for each column of the current row in $result

Outline database. php (3 of 3) 132

Outline dynamic. Form. php (1 of 12) 133

Outline dynamic. Form. php (2 of 12) Checks whether the Register button has been pressed Checks that the first name field is not blank Makes an entry in the error array Sets $iserror to true 134

Outline dynamic. Form. php (3 of 12) Checks that all other form fields are filled in correctly Inserts a backslash before the parentheses in the phone number 135

Outline dynamic. Form. php (4 of 12) 136

Outline dynamic. Form. php (5 of 12) Ends script here if there were no errors in the user input Section to be executed only if $iserror is true 137

Outline dynamic. Form. php (6 of 12) Alerts the user that there are errors Iterates through $inputlist to create the form’s text boxes Outputs the field’s image Sets the name attribute of the text field to $inputname Sets the value attribute of the Puts an asterisk text field to the value of the next to fields that variable with the name of $inputname’s value have errors 138

Outline dynamic. Form. php (7 of 12) Creates drop-down list for books, keeping the previously selected one selected 139

Outline dynamic. Form. php (8 of 12) Creates radio buttons for operating-system selection, keeping the previously selected option selected 140

Outline dynamic. Form. php (9 of 12) 141

Outline dynamic. Form. php (10 of 12) 142

Outline dynamic. Form. php (11 of 12) 143

Outline dynamic. Form. php (12 of 12) 144

Outline form. Database. php (1 of 3) Selects all fields from the contacts database to display 145

Outline form. Database. php (2 of 3) 146

Outline form. Database. php (3 of 3) 147