Transcript PHP II

PHP II
Interacting with Database Data
 The whole idea of a database-driven website is to enable the
content of the site to reside in a database, and to allow that data
to be accessible on the website.
 ‘Access’ to data is not limited to viewing it alone.
 Very frequently, it is desirable that data is captured on the
website, and transferred (in real time) for storage, or for
manipulation.
 Server-side programming languages such as PHP act as an
intermediary between the client (browser), and the database.
 PHP enables the design of the presentation component of a web
page to be created in HTML (as templates), and then retrieves
the ‘content’ dynamically from the database.
 Data submitted through a web page can also be transmitted to
the database, for processing.
 Most relational database systems implement Structured Query
Language (SQL).
 PHP is able to communicate query requests to the database with
the use of SQL.
Basics of SQL
 Relational database systems store data in tables, that are
conceptually similar to ‘regular’ tables.
 There are mechanism for maintaining the integrity of the data,
such as constraints and database triggers.
 Standard commands exist for creating databases, and database
objects.
 There are also commands that are used for manipulating data,
such as insertion, deletion, and update of records.
Creating a Database
 Basic command for creating a database is as follows:
create database database_name;
 There are rules for naming databases, and database objects
(beyond the scope of this lecture), but generally the names
should begin with an alphabetic character, and should not
contain blank spaces.
 A new database will be empty until you create objects such as
tables in it.
Creating Tables
 In creating a database table, the sort of data to be stored in the
columns must be considered, so that the columns can be defined
appropriately.
 Basic command:
create table tablename(column1 column1’s datatype, column2
column2’s datatype, columnn columnn’s datatype);
 E.g. create table students(student_id int, first_name varchar(20),
last_name varchar(30));
 The datatype for the student_id column was defined as integer,
implying that it can contain integer values, while the other 2
columns were defined as varchar (variable length character
columns).
 The value enclosed in parentheses next to ‘varchar’ specifies the
maximum length of data permitted in the column.
 Other datatypes commonly used are ‘Date’ for storing date
values, and char (for storing fixed character length values)
Altering Tables
 Database tables sometimes have to be altered to accommodate
‘new’ records, or because the existing columns have become
unsuitable to support business requirements
 In altering tables, new columns may be added, or existing
columns expanded.
 Basic commands:
alter table table_name modify column new_column_definition;
E.g. alter table students modify first_name varchar(40);
alter table table_name add new_column datatype;
E.g. alter table students add DOB date;
 In the first example, an existing column was modified, while in
the second example, an entirely new column was added to the
table.
Dropping Tables
 Tables can be dropped (deleted) from the database, when they
are no longer required.
 Syntax:
drop table table_name;
 Be extremely cautious in doing this as it is irreversible.
Inserting Data into a Table
 Basic command:
insert into tablename(column1, column2,column3,...) values
(value1, value2, value3,...);
E.g.
insert into students(student_id, first_name, last_name) values
(19899,’Peter’,’Jackson’);
 Note that values inserted into character or date columns need to
be enclosed in a string.
Retrieving Data from a table
 Achieved with the ‘select’ statement.
select student_id, first_name, last_name from students;
 A short-cut for selecting all the columns in a table is to issue the
command: select * from table_name; e.g. select * from students
 There are times when it might be desirable to restrict the query
output with certain criteria. This is achieved with the use of a
‘where’ clause
 E.g. select * from students where surname = ‘Jackson’; would
retrieve only the stipulated record from the database.
Updating Data
 This is the process of changing values in the database.
 Basic command: update tablename set column = new_value
where condition;
 E.g. update students set student_id = 234099 where
last_name=‘Jackson’;
 It is possible to update more than one column with one statement
e.g. update students set student_id = 234099, first_name = ‘Paul’
where last_name = ‘Jackson’;
Deleting records from the database
 Basic command:
 delete from table_name where condition e.g. delete from
students where last_name=‘Jackson’;
 Without specifying a criterion in the where clause, all the rows
of data in the table would be deleted
Connecting to MySQL with PHP
 Before you can interact with data held in a database, you need to
establish a connection with the database, as they are separate
entities.
 PHP provides a built-in function for connecting to the database
called mysql_connect.
 This function takes the form mysql_connect(address, username,
password); where address is the IP address or host name of the
MySQL server, username and password are the log-in details of the
user on the MySQL server.
 It returns a number value that identifies the connection that has
been established, and this number is usually assigned to a variable.
 If the connection fails, the function evaluates to false.
 E.g. $conn = mysql_connect(‘localhost’, ‘username’, ‘password’);
 The $conn variable is subsequently used as an argument in other
related functions.
 It is important to programmatically check that a connection was
established after calling the MySQL function, so that the error
can be trapped, and handled appropriately.
 E.g.
$conn = mysql_connect(‘localhost’, ‘username’, ‘password’);
if (!$conn) {
echo(‘<P>Unable to connect to the database at this time. Please try
later </P>’);
exit();
}
Line 2 could also have been written:
if ($conn = = ‘false’) {
Selecting the database
 Usually, the database server would contain several databases,
hence there is a logical need to select the required one.
 The built-in function used to carry out this function is
mysql_select_db.
 It takes 2 arguments: the name of the database, and the number
or connection identifier returned by the mysql_connect function.
The latter is defaulted to the value of the last connection
established if no value is supplied.
 The function returns true when it is executes successfully, and
false otherwise.
 E.g.
if (!mysql_select_db(‘database_name’,$conn) {
echo(‘<P>Unable to locate the database</P>’);}
//alternative actions to perform if successful......
Processing SQL Queries with PHP
 The built-in function for processing SQL queries is
mysql_query
 It accepts 2 parameters (query, connection_id).
 The query parameter is a string that contains the SQL command.
 E.g.
$query = ‘insert into students (student_id, first_name, last_name)
values (556283, ‘Peter’, ‘Jackson’);
if (mysql_query($query)) {
echo(‘<P>New student successfully added to the database</P>’);
}
else {
echo(‘<P>Unable to insert record into the database</P>’);
}
 PHP also has built-in functions that keep track of the number of
rows affected by data manipulation commands.
 This function is called mysql_affected_rows( ), and it returns
the number of rows processed as a result of the insert, delete, or
update command.
 E.g.
$query = ‘delete from students where last_name = ‘Smith’);
if (mysql_query($query)) {
echo(‘<P>’ . mysql_affected_rows( ) . students share the surname
and were deleted </P>’);
}
Handling SELECT Result Sets
 PHP provides useful built-in functions that enable access to
record sets retrieved from the database, when select statements
are issued.
 When PHP processes a query successfully, the mysql_query
returns a number that identifies the result set, which contains all
the rows returned from the query. It is therefore necessary to
declare a variable to store the record set, as shown below:
$query = ‘select * from students’
$result = mysql_query($query);
If the above query was successful, the $result variable now
contains the query output.
Individual rows of the result can now be processed by using one of
the available built-in functions, such as mysql_fetch_array
 The my_sql_fetch_array function accepts a result set as a
parameter, and fetches the next row in the result set as an array.
It eventually returns false when there are no more rows to fetch.
 This makes it quite useful in loops.
 E.g.
$query = ‘select * from students’
$result = mysql_query($query);
while ($row = mysql_fetch_array($result)) {
//process the row..........
}
 The rows of a result set are represented as associative arrays,
with the indices corresponding to the table columns in the result
set. If $row is a row in the result set, then $row[‘student_id’] is
the value of the student_id for that row.
 This enables access to individual columns in the result set.
 E.g. to output all the student_ids in our students table, we could
write code like:
while ( $row = mysql_fetch_array($result) ) {
echo(‘<p>’. $row[‘student_id’] . ‘</p>
Example of displaying all the student_ids in a web page:
<html>
<head>
<title>List of Student Numbers</title>
<body>
<?php
//connect to the database server
$conn = mysql_connect(‘localhost’, ‘username’, ‘password’);
if (!$conn)
// if the connection failed
{die(‘<p>Unable to connect to the server</p>’); // display error msg
}
//otherwise go ahead and select the database
if ( !mysql_select_db(‘database_name’) ) //if it can’t find the database
{ die( ‘<p>Unable to select the database</p>’); // display message
}
?>
<p>Here are all the students in our database: <p>
<?php
$result = mysql_query(‘select student_id from students’);
if (!$result ) { die(‘<p> Error performing query</p>); }
while ($row = mysql_fetch_array($result) ) {
echo(‘<p>’ . $row[‘student_id’] . ‘</p>’);
?>
</body> </html>
Class Assignment:
1. Find out how to send emails dynamically, from a web page
using PHP.
2. Find out how to generate PDF files with PHP.
References:
Deitel, Deitel & Nieto Chapter 29
Build your Own Database Driven website Using PHP and MySQL by
Kevin Yank. 2nd Ed. chapters 2, 3, 4 and 9
Some Useful PHP web sites:
 www.php.net
 www.phpworld.com
 www.phpbuilder.com