PHP IIInteracting 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 databaseif ( !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