Transcript Document

TP2543 Web Programming
Introduction to PHP
and
DATABASE
Questions…

What is PHP?

Why would you need PHP?

What are the 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
JavaScript
&
DOM
Action/Interactivity
and now…
HTML/
XHTML
CSS
JavaScript
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
MySQL
Apache
Deploying PHP
packages
SERVER-SIDE
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 are also different ways to request content from a web server.
The most common request is the Hypertext Transfer Protocol
(HTTP), but there are also other requests like the Internet Message
Access Protocol (IMAP) or the File Transfer Protocol (FTP).
wikipedia
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
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
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
◦ JavaScipt,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, MySQL, MS SQL, Oracle, Sybase, DB2
Web Resources

www.microsoft.com/msdownload/ntoptionpack/askwi
z.asp

www.w3.org/Protocols

www.apache.org

httpd.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 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
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, XHTML, 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… (M1/M2/M3)
◦ …or find a webhosting plan with PHP support (M4)
How to run PHP scripts
PHP on
Windows
M1
M2
IIS
Apache
FastCGI +
PHP
PHP
mySQL
mySQL
M3
WAMP
Packages
M4
Web Hosting
How to run PHP scripts

LAMP – Linux + Apache + MySQL + PHP
operating system
web server
database
scripting language

…as 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, MySQL, PHP, Perl,
phpMyAdmin, JpGraph, FileZilla FTP Server, SQLite etc.
WAMP
◦ http://www.wampserver.com/en/
CODING IN PHP
PHP
BASIC SYNTAX
VARIABLES
DATA TYPE
CONTROL
STATEMENTS
FUNCTION
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;
$ayat = “Hello”;
echo
echo
echo
echo
echo
?>
// Numerical variable
// String variable
$ayat;
$nilai, $ayat;
“5x5=”,$nilai;
“5x5=$nilai”;
‘5x5=$nilai’;
// Outputs Hello
// Outputs 25Hello
// Outputs 5x5=25
// Outputs 5x5=25
// Outputs 5x5=$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 != $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)
Local Scope <?php
$a = 5; // global scope
function myTest(){
echo $a; // local scope
}
myTest();
?>
<?php
$a = 5;
$b = 10;
function myTest() {
global $a, $b;
$b = $a + $b;
}
myTest();
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.w3schools.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"]; ?>.<br />
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
print
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
$string1=“Hello”;
$string2=“PHP”;
$string3=$string1 . “ ” . $string2;
print $string3;
//Hello PHP
?>
$favorite_food = "Ethiopian";
print $favorite_food[2];
#h
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
$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 . "<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(“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 function_name ($var1, $var2…)
{
…
}
Function names are not case sensitive
PHP - functions
<?php
// This is a function
function darab($arg1, $arg2)
{
$arg3 = $arg1 * $arg2;
return $arg3;
}
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 ?>
<h2> The answer is <?= 6 * 7 ?> </h2>
//The answer is 42
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 Array
PHP Calendar
PHP Date
PHP Directory
PHP Error
PHP Filesystem
PHP Filter
PHP FTP
PHP HTTP
•
•
•
•
•
•
•
•
•
PHP Libxml
PHP Mail
PHP Math
PHP Misc
PHP MySQL
PHP SimpleXML
PHP String
PHP XML
PHP Zip
http://www.w3schools.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
TP2543
Web Programming
Database:
PHP
SQL
MySQL
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 welllabeled 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
TrkLahir
….
Introduction
Data Types
 char/varchar/nchar/nvarchar – stores text strings.
 int – stores integers. Called numberin Oracle
 float - stores decimal numbers
 Date – Stores datetime values. By default only shows date as “DDMON-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 primary-key
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-to-many 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.
Column
Description
authorID
Author’s ID number in the database. In the books database, this
integer column is defined as autoincremented—for each row
inserted in this table, the authorID value is increased by 1
automatically to ensure that each row has a unique authorID.
This column represents the table’s primary key.
firstName
Author’s first name (a string).
lastName
Author’s last name (a string).
authors table from the books database.
authorID firstName
lastName
1
Harvey
Deitel
2
Paul
Deitel
3
Andrew
Goldberg
4
David
Choffnes
77
Relational Database Overview :
A books Database
Column
Description
isbn
ISBN of the book (a string). The table’s primary key.
ISBN is an abbreviation for “International Standard
Book Number”—a numbering scheme that publishers
use to give every book a unique identification number.
title
Title of the book (a string).
editionNumber
Edition number of the book (an integer).
copyright
Copyright year of the book (a string).
titles table from the books database.
isbn
title
editionNumber copyright
0131869000
Visual Basic How to Program
3
2006
0131525239
Visual C# How to Program
2
2006
0132222205
Java How to Program
7
2007
0131857576
C++ How to Program
5
2005
0132404168
C How to Program
5
2007
0131450913
Internet and World Wide Web
How to Program
3
2004
78
Relational Database Overview :
A books Database
Column
Description
authorID
The author’s ID number, a foreign key to the authors table.
isbn
The ISBN for a book, a foreign key to the titles table.
authorISBN table from the books database.
authorID isbn
authorID
isbn
1
0131869000
2
0131450913
2
0131869000
1
0131828274
1
0131483986
2
0131828274
2
0131483986
3
0131450913
1
0131450913
4
0131828274
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 keyword Description
SQL query keywords.

SELECT
Retrieves data from one or more tables.
FROM
Tables involved in the query. Required in
every SELECT.
WHERE
Criteria for selection that determine the rows
to be retrieved, deleted or updated. Optional
in a SQL query or a SQL statement.
GROUP BY
Criteria for grouping rows. Optional in a
SELECT query.
ORDER BY
Criteria for ordering rows. Optional in a
SELECT query.
INNER JOIN
Combine rows from multiple tables.
INSERT
Insert rows into a specified table.
UPDATE
Update rows in a specified table.
DELETE
Delete rows from a specified table.
82
Basic SELECT Query

The basic form of a query is
SELECT * FROM tableName
where the asterisk (*) indicates that all columns from
tableName should be selected, and tableName 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 authorID, lastName FROM authors
authorID firstName
lastName
1
Harvey
2
authorID
lastName
Deitel
1
Deitel
Paul
Deitel
2
Deitel
3
Andrew
Goldberg
4
David
Choffnes
3
Goldberg
4
Choffnes
Sample authorID and lastName 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 columnName1, columnName2, … FROM
tableName 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
isbn
title
editionNumber copyright
0131869000
Visual Basic How to Program
3
2006
0131525239
Visual C# How to Program
2
2006
0132222205
Java How to Program
7
2007
0131857576
C++ How to Program
5
2005
0132404168
C How to Program
5
2007
0131450913
Internet and World Wide Web
How to Program
3
2004
SELECT Title, editionNumber, copyright
FROM Titles
WHERE copyright > '2005'
title
editionNumber copyright
Visual C# How to Program
2
2006
Visual Basic 2005 How to Program
3
2006
Java How to Program
7
2007
C How to Program
5
2007
Sampling of titles with copyrights after 2005 from table titles.
86
SELECT AuthorID, FirstName, LastName
FROM authors
WHERE LastName LIKE 'D%'
authorID firstName
lastName
1
Harvey
Deitel
2
Paul
Deitel
3
Andrew
Goldberg
4
David
Choffnes
authorID
firstName
lastName
1
Harvey
Deitel
2
Paul
Deitel
Authors whose last name starts with D from the authors table.
87
SELECT AuthorID, FirstName, LastName
FROM authors
WHERE LastName LIKE '_o%'
authorID firstName
lastName
1
Harvey
Deitel
2
Paul
Deitel
3
Andrew
Goldberg
4
David
Choffnes
authorID
firstName
lastName
3
Andrew
Goldberg
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 columnName1, columnName2, … FROM
tableName ORDER BY column ASC
SELECT columnName1, columnName2, … FROM
tableName 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 column1 sortingOrder, column2 sortingOrder, …

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 AuthorID, FirstName, LastName
FROM authors
ORDER BY LastName ASC
authorID firstName
lastName
authorID
firstName
lastName
1
Harvey
Deitel
4
David
Choffnes
2
Paul
Deitel
1
Harvey
Deitel
3
Andrew
Goldberg
2
Paul
Deitel
4
David
Choffnes
3
Andrew
Goldberg
authors sample data in ascending order by lastName.
90
SELECT AuthorID, FirstName, LastName
FROM authors
ORDER BY LastName DESC
authorID firstName
lastName
1
Harvey
Deitel
2
Paul
Deitel
3
Andrew
Goldberg
4
David
Choffnes
authorID
firstName
lastName
3
Andrew
Goldberg
1
Harvey
Deitel
2
Paul
Deitel
4
David
Choffnes
authors sample data in descending order by lastName.
91
SELECT AuthorID, FirstName, LastName
FROM authors
ORDER BY LastName, FirstName
authorID firstName
lastName
authorID
firstName
lastName
1
Harvey
Deitel
4
David
Choffnes
2
Paul
Deitel
1
Harvey
Deitel
3
Andrew
Goldberg
2
Paul
Deitel
4
David
Choffnes
3
Andrew
Goldberg
authors sample data in ascending order by lastName and firstName.
92
SELECT ISBN,Titles, EditionNumber, Copyright
FROM titles
WHERE Titles LIKE '%How to Program'
ORDER BY Titles ASC
isbn
title
editionNumber
copyright
0132404168
C How to Program
5
2007
0131857576
C++ How to Program
5
2005
0131450913
Internet and World Wide Web
How to Program
3
2004
0132222205
Java How to Program
7
2007
0131869000
Visual Basic 2005 How to
Program
3
2006
0131525239
Visual C# How to Program
2
2006
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 columnName1, columnName2, …
FROM table1
INNER JOIN table2
ON table1.columnName = table2.columnName
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 FirstName, LastName, ISBN
FROM authors
INNER JOIN authorISBN
ON authors.AuthorID = authorISBN.AuthorID
ORDER BY LastName, FirstName
firstName lastName isbn
firstName
lastName isbn
David
Choffnes
0131828274
Paul
Deitel
0131869000
Harvey
Deitel
0131869000
Paul
Deitel
0131525239
Harvey
Deitel
0131525239
Paul
Deitel
0132222205
Harvey
Deitel
0132222205
Paul
Deitel
0131857576
Harvey
Deitel
0131857576
Paul
Deitel
0132404168
Harvey
Deitel
0132404168
Paul
Deitel
0131450913
Harvey
Deitel
0131450913
Paul
Deitel
0131869000
Harvey
Deitel
0131869000
Paul
Deitel
0131828274
Harvey
Deitel
0131828274
Andrew
Goldberg
0131450913
Sampling of authors and ISBNs for the books they have written
in ascending order by lastName and firstName.
95
authorID
isbn
1
0131869000
2
0131869000
1
0131483986
2
0131483986
1
authorID
firstName lastName
1
Harvey
Deitel
2
Paul
Deitel
0131450913
3
Andrew
Goldberg
2
0131450913
4
David
Choffnes
1
0131828274
2
0131828274
3
0131450913
4
0131828274
firstName lastName isbn
firstName
lastName isbn
David
Choffnes
0131828274
Paul
Deitel
0131869000
Harvey
Deitel
0131483986
Paul
Deitel
0131483986
Harvey
Deitel
0131450913
Paul
Deitel
0131450913
Harvey
Deitel
0131869000
Paul
Deitel
0131828274
Harvey
Deitel
0131828274
Andrew
Goldberg
0131450913
INSERT Statement

An INSERT statement inserts a new row into a table. The basic
form of this statement is
INSERT INTO tableName ( columnName1,
columnName2, …, columnNameN )
VALUES ( value1, value2, …, valueN )

where tableName is the table in which to insert the row. The
tableName 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 ( FirstName, LastName )
VALUES ( 'Sue', 'Smith' )
authorID firstName
lastName
1
Harvey
Deitel
authorID firstName
lastName
1
Harvey
Deitel
2
Paul
Deitel
2
Paul
Deitel
3
Andrew
Goldberg
3
Andrew
Goldberg
4
David
Choffnes
4
David
Choffnes
5
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 tableName
SET
columnName1 = value1,
columnName2 = value2, …,
columnNameN = valueN
WHERE criteria
where tableName is the table in which to update data. The
tableName is followed by keyword SET and a comma-separated list
of column name/value pairs in the format columnName = value. The
optional WHERE clause criteria determines which rows to update.
99
UPDATE authors
SET LastName = 'Jones'
WHERE LastName = 'Smith' AND FirstName = 'Sue'
authorID firstName
lastName
authorID firstName
lastName
1
Harvey
Deitel
1
Harvey
Deitel
2
Paul
Deitel
2
Paul
Deitel
3
Andrew
Goldberg
3
Andrew
Goldberg
4
David
Choffnes
4
David
Choffnes
5
Sue
Jones
5
Sue
Smith
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 tableName WHERE criteria
where tableName 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 LastName = 'Jones' AND FirstName = 'Sue'
authorID firstName
lastName
authorID firstName
lastName
1
Harvey
Deitel
1
Harvey
Deitel
2
Paul
Deitel
2
Paul
Deitel
3
Andrew
Goldberg
3
Andrew
Goldberg
4
David
Choffnes
4
David
Choffnes
5
Sue
Jones
Sample data from table authors after a DELETE operation.
102
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)
phpMyAdmin
phpMyAdmin (WAMP)
Creating Database
CREATE DATABASE databasename
Creating Tables

Some of MySQL 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 MySQL 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
ID
Number
INT
NAMA
Text
VARCHAR [40]
EMAIL
Text
VARCHAR [40]
TARIKH
Date/time
DATETIME
KOMEN
Text
TEXT
Type
MySQL data type
guestbook
ID
NAMA
EMAIL
TARIKH
KOMEN
1
Simon
[email protected]
m
2010-09-21
07:40:48
Excellent website!
Well done!
2
Azizi
[email protected]
m
2010-09-23
10:20:48
Sila lawati laman web
saya: azizi.com.my
3
Wei
Yoong
[email protected]
k
2010-09-23
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', ‘[email protected]‘, NOW(), ‘Website yang bagus!’)");
…
PHP Inserting data
//Create query
$qry = "INSERT INTO guestbook
(ID, NAMA, EMAIL, TARIKH, KOMEN)
VALUES
(5, ‘Jason', ‘[email protected]‘, 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 =
‘[email protected]’ 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/mysqlTutorial
http://www.w3schools.com/php/php_ajax_database.asp
data.html
Outline
(1 of 2)
1
2
3
<?xml version = "1.0" encoding = "utf-8"?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN"
"http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">
4
5
6
<!-- Fig. 23.14: data.html -->
<!-- Form to query a MySQL database. -->
7
8
<html xmlns = "http://www.w3.org/1999/xhtml">
<head>
9
10
11
12
13
14
15
<title>Sample Database Query</title>
<style type = "text/css">
body { background-color: #F0E68C }
h2
{ font-family: arial, sans-serif;
color: blue }
input { background-color: blue;
color: yellow;
font-weight: bold }
16
17
18
</style>
</head>
19
20
<body>
<h2> Querying a MySQL database.</h2>
21
22
23
24
25
Posts data to
database.php
<form method = "post" action = "database.php">
<div>
<p>Select a field to display:
<!-- add a select box containing options -->
<!-- for SELECT query -->
128
Outline
<select name = "select">
26
data.html
27
<option selected = "selected">*</option>
28
<option>ID</option>
29
<option>Title</option>
30
<option>Category</option>
31
<option>ISBN</option>
</select></p>
32
<input type = "submit" value = "Send Query" />
33
</div>
34
35
36
(2 of 2)
</form>
</body>
37 </html>
Creates drop-down menu specifying
which data to output to the screen,
with * (all data) as the default
selection
129
Outline
database.php
1
<?php print( '<?xml version = "1.0" encoding = "utf-8"?>' ) ?>
2
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN"
3
4
"http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">
5
<!-- Fig. 23.15: database.php -->
6
<!-- Querying a database and displaying the results. -->
7
8
9
<html xmlns = "http://www.w3.org/1999/xhtml">
<head>
<title>Search Results</title>
10
<style type = "text/css">
body
11
{ font-family: arial, sans-serif;
12
background-color: #F0E68C }
13
14
table { background-color: #ADD8E6 }
td
{ padding-top: 2px;
15
padding-bottom: 2px;
16
padding-left: 4px;
17
padding-right: 4px;
18
19
20
border-width: 1px;
border-style: inset }
Builds a SELECT query with
the selection made in
data.html
</style>
21
</head>
22
<body>
23
24
<?php
extract( $_POST );
25
26
// build SELECT query
27
$query = "SELECT " . $select . " FROM books";
28
(1 of 3)
130
Outline
29
// Connect to MySQL
30
if ( !( $database = mysql_connect( "localhost",
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
database.php
"iw3htp4", "iw3htp4" ) ) )
die( "Could not connect to database </body></html>" );
Connects to
database using
if ( !mysql_select_db( "products", $database ) )
die( "Could not open products database </body></html>" ); server hostname
localhost and
// query Products database
username and
if ( !( $result = mysql_query( $query, $database ) ) )
password
{
Specifies
products as
“iw3htp4”
print( "Could not execute query! <br />" );
die( mysql_error() . "</body></html>" );
the database to be
} // end if
Returns any error strings
queried
from the database
mysql_close( $database );
?><!-- end PHP script -->
Queries $database
Closes the connection
to $query
<h3>Search Results</h3>
with
<table>
the database
// open Products database
<?php
50
// fetch each record in result set
51
52
for ( $counter = 0; $row = mysql_fetch_row( $result );
$counter++ )
53
{
54
// build table to display results
55
print( "<tr>" );
56
(2 of 3)
Returns an array with
the values for each
column of the current
row in $result
Outline
foreach ( $row as $key => $value )
57
(3 of 3)
print( "<td>$value</td>" );
58
59
print( "</tr>" );
60
} // end for
61
?><!-- end PHP script -->
62
63
</table>
64
<br />Your search yielded <strong>
65
<?php print( "$counter" ) ?> results.<br /><br /></strong>
66
<h5>Please email comments to
67
<a href = "mailto:[email protected]">
68
Deitel and Associates, Inc.</a>
69
70
database.php
</h5>
</body>
71 </html>
132
1
2
<?php print( '<?xml version = "1.0" encoding = "utf-8"?>' ) ?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN"
"http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">
3
4
5
<!-- Fig. 23.21: dynamicForm.php -->
6
7
8
<!-- Dynamic form. -->
<html xmlns = "http://www.w3.org/1999/xhtml">
<head>
9
10
11
12
13
dynamicForm.php
(1 of 12)
<title>Sample form to take user input in XHTML</title>
<style type = "text/css">
td
{ padding-top: 2px;
padding-bottom: 2px;
padding-left: 10px;
14
15
16
div
div div
padding-right: 10px }
{ text-align: center }
{ font-size: larger }
17
18
.name
.email
{ background-color: #ffffaa }
{ background-color: #ffffbb }
19
20
21
.phone
.os
.smalltext
{ background-color: #ffffcc }
{ background-color: #ffffdd }
{ font-size: smaller }
22
23
24
.prompt
{ color: blue;
font-family: sans-serif;
font-size: smaller }
25
26
.largeerror { color: red }
.error
{ color: red;
27
28
29
Outline
font-size: smaller }
</style>
</head>
133
30
31
32
33
<body>
<?php
Outline
extract( $_POST );
$iserror = false;
34
35
// array of book titles
36
37
38
$booklist = array( "Internet and WWW How to Program 4e",
"C++ How to Program 6e", "Java How to Program 7e",
"Visual Basic 2005 How to Program 3e" );
dynamicForm.php
(2 of 12)
39
40
// array of possible operating systems
41
$systemlist = array( "Windows XP", "Windows Vista",
"Mac OS X", "Linux", "Other");
42
43
44
// array of name values for the text input fields
45
$inputlist = array( "fname" => "First Name",
46
"lname" => "Last Name", "email" => "Email",
47
48
49
"phone" => "Phone" );
// ensure that all fields have been filled in correctly
50
if ( isset ( $submit ) )
51
{
52
53
if ( $fname == "" )
{
Checks whether the Register
button has been pressed
54
$formerrors[ "fnameerror" ] = true;
55
$iserror = true;
56
57
} // end if
Checks that the first name field
is not blank
Makes an entry in the error array
Sets $iserror to true
134
Outline
58
59
60
if ( $lname == "" )
{
$formerrors[ "lnameerror" ] = true;
61
62
63
$iserror = true;
} // end if
64
65
if ( $email == "" )
{
66
67
68
69
$formerrors[ "emailerror" ] = true;
$iserror = true;
} // end if
70
71
72
if ( !ereg( "^\([0-9]{3}\)[0-9]{3}-[0-9]{4}$", $phone ) )
{
$formerrors[ "phoneerror" ] = true;
73
74
75
$iserror = true;
} // end if
76
77
if ( !$iserror )
{
78
79
80
81
82
dynamicForm.php
(3 of 12)
Checks that all
other form fields
are filled in
correctly
// build INSERT query
$query = "INSERT INTO contacts " .
"( LastName, FirstName, Email, Phone, Book, OS ) " .
"VALUES ( '$lname', '$fname', '$email', " .
"'" . quotemeta( $phone ) . "', '$book', '$os' )";
Inserts a backslash before
the parentheses in the
phone number
135
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
// Connect to MySQL
if ( !( $database = mysql_connect( "localhost",
"iw3htp4", "iw3htp4" ) ) )
die( "Could not connect to database" );
Outline
dynamicForm.php
(4 of 12)
// open MailingList database
if ( !mysql_select_db( "MailingList", $database ) )
die( "Could not open MailingList database" );
// execute query in MailingList database
if ( !( $result = mysql_query( $query, $database ) ) )
{
print( "Could not execute query! <br />" );
die( mysql_error() );
} // end if
mysql_close( $database );
print( "<p>Hi<span class = 'prompt'>
<strong>$fname</strong></span>.
Thank you for completing the survey.<br />
You have been added to the
<span class = 'prompt'>
<strong>$book</strong></span>
mailing list.</p>
<strong>The following information has been saved
in our database:</strong><br />
136
113
<table><tr>
114
<td class = 'name'>Name </td>
115
116
<td class = 'email'>Email</td>
<td class = 'phone'>Phone</td>
117
<td class = 'os'>OS</td>
118
</tr><tr>
119
120
121
<!-- print each form field’s value -->
<td>$fname $lname</td>
122
<td>$email</td>
123
<td>$phone</td>
124
<td>$os</td>
125
126
</tr></table>
127
<br /><br /><br />
128
<div><div>
129
<a href = 'formDatabase.php'>
130
131
132
Click here to view entire database.</a>
</div>This is only a sample form.
You have not been added to a mailing list.
133
</div></body></html>" );
134
135
136
Outline
dynamicForm.php
(5 of 12)
die();
} // end if
} // end if
137
138
139
print( "<h1>Sample Registration Form.</h1>
Please fill in all fields and click Register." );
140
137
Ends script here if
there were no errors in
the user input
Section to be executed
only if $iserror is
true
141
142
if ( $iserror )
{
143
144
145
print( "<br /><span class = 'largeerror'>
Fields with * need to be filled in properly.</span>" );
} // end if
146
147
148
print( "<!-- post form data to form.php -->
<form method = 'post' action = 'dynamicForm.php'>
149
150
<img src = 'images/user.gif' alt = 'User' /><br />
<span class = 'prompt'>
151
152
153
Please fill out the fields below.<br /> </span>
154
155
156
157
158
159
160
161
162
163
164
Outline
dynamicForm.php
(6 of 12)
Alerts the user that
there are errors
<!-- create four text boxes for user input -->" );
Iterates through
$inputlist to create the
form’s text boxes
foreach ( $inputlist as $inputname => $inputalt )
{
$inputtext = $inputvalues[ $inputname ];
print( "<img src = 'images/$inputname.gif'
alt = '$inputalt' /><input type = 'text'
name = '$inputname' value = '" . $$inputname . "' />" );
if ( $formerrors[ ( $inputname )."error" ] == true )
print( "<span class = 'error'>*</span>" );
165
166
print( "<br />" );
} // end foreach
167
168
169
if ( $formerrors[ "phoneerror" ] )
print( "<span class = 'error'>" );
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
have errors
$inputname’s value
138
170
171
else
print("<span class = 'smalltext'>");
Outline
172
173
174
print( "Must be in the form (555)555-5555
</span><br /><br />
175
176
177
<img src = 'images/downloads.gif'
alt = 'Publications' /><br />
178
179
<span class = 'prompt'>
180
Which book would you like information about?
181
182
</span><br />
183
<!-- create drop-down list containing book names -->
184
185
<select name = 'book'>" );
186
foreach ( $booklist as $currbook )
187
188
189
{
190
191
192
193
194
195
dynamicForm.php
(7 of 12)
print( "<option" );
if ( ( $currbook == $book ) )
print( " selected = 'true'" );
Creates drop-down list
for books, keeping the
previously selected
one selected
print( ">$currbook</option>" );
} // end foreach
139
196
print( "</select><br /><br />
197
<img src = 'images/os.gif' alt = 'Operating System' />
198
<br /><span class = 'prompt'>
199
200
Which operating system are you currently using?
<br /></span>
201
202
203
<!-- create five radio buttons -->" );
204
205
$counter = 0;
206
foreach ( $systemlist as $currsystem )
207
{
208
209
print( "<input type = 'radio' name = 'os'
value = '$currsystem'" );
210
211
if ( $currsystem == $os )
212
213
214
215
216
dynamicForm.php
(8 of 12)
print( "checked = 'checked'" );
elseif ( !$os && $counter == 0 )
print( "checked = 'checked'" );
print( " />$currsystem" );
217
218
// put a line break in list of operating systems
219
220
if ( $counter == 1 ) print( "<br />" );
++$counter;
221
Outline
} // end foreach
222
140
Creates radio buttons
for operating-system
selection, keeping the
previously selected
option selected
223
print( "<!-- create a submit button -->
224
<br /><input type = 'submit' name = 'submit'
225
value = 'Register' /></form></body></html>" );
226
Outline
?><!-- end PHP script -->
dynamicForm.php
(9 of 12)
141
Outline
dynamicForm.php
(10 of 12)
142
Outline
dynamicForm.php
(11 of 12)
143
Outline
dynamicForm.php
(12 of 12)
144
1
2
<?php print( '<?xml version = "1.0" encoding = "utf-8"?>' ) ?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN"
"http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">
3
4
5
<!-- Fig. 23.22: formDatabase.php -->
6
7
8
<!-- Displaying the MailingList database. -->
<html xmlns = "http://www.w3.org/1999/xhtml">
<head>
9
10
body
14
15
16
table { background-color: #ADD8E6 }
td
{ padding-top: 2px;
padding-bottom: 2px;
17
18
padding-left: 4px;
padding-right: 4px;
19
20
21
border-width: 1px;
border-style: inset }
22
23
24
formDatabase.php
(1 of 3)
<title>Search Results</title>
<style type = "text/css">
11
12
13
h3
{ font-family: arial, sans-serif;
background-color: #F0E68C }
{ color: blue }
</style>
</head>
<body>
<?php
25
26
extract( $_POST );
27
28
29
// build SELECT query
$query = "SELECT * FROM contacts";
Outline
Selects all fields
from the contacts
database to display
145
30
31
32
33
34
// Connect to MySQL
if ( !( $database = mysql_connect( "localhost",
"iw3htp4", "iw3htp4" ) ) )
die( "Could not connect to database </body></html>" );
35
36
37
// open MailingList database
if ( !mysql_select_db( "MailingList", $database ) )
die( "Could not open MailingList database </body></html>" );
38
39
// query MailingList database
40
41
42
if ( !( $result = mysql_query( $query, $database ) ) )
{
print( "Could not execute query! <br />" );
43
44
45
die( mysql_error() . "</body></html>" );
} // end if
?><!-- end PHP script -->
46
47
<h3>Mailing List Contacts</h3>
48
49
50
<table>
<tr>
<td>ID</td>
51
52
53
<td>Last Name</td>
<td>First Name</td>
<td>E-mail Address</td>
54
55
<td>Phone Number</td>
<td>Book</td>
56
57
58
<td>Operating System</td>
</tr>
<?php
146
Outline
formDatabase.php
(2 of 3)
59
// fetch each record in result set
60
for ( $counter = 0; $row = mysql_fetch_row( $result );
$counter++ )
61
Outline
{
62
63
// build table to display results
64
print( "<tr>" );
formDatabase.php
foreach ( $row as $key => $value )
(3 of 3)
65
66
print( "<td>$value</td>" );
67
68
print( "</tr>" );
69
} // end for
70
71
mysql_close( $database );
72
?><!-- end PHP script -->
73
74
75
</table>
</body>
76 </html>
147