PHP II Interacting with Database Data

1
PHP IIInteracting with Database Data
2

• 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.

3

• 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.

4

• 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.

5

• Basic command
• create table tablename(column1 column1s
  datatype, column2 column2s datatype, columnn
  columnns 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)

6

• 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.

7

• 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.

8

• 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

9

• E.g. update students set student_id 234099
  where last_nameJackson
• 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_nameJackson
• Without specifying a criterion in the where
  clause, all the rows of data in the table would
  be deleted

10

• 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.

11

• 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(ltPgtUnable to connect to the database at
  this time. Please try later lt/Pgt)
• exit()
• Line 2 could also have been written
• if (conn false)

12

• 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(ltPgtUnable to locate the databaselt/Pgt)
• //alternative actions to perform if
  successful......

13

• 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(ltPgtNew student successfully added to the
  databaselt/Pgt)
• else
• echo(ltPgtUnable to insert record into the
  databaselt/Pgt)

14

• 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(ltPgt . mysql_affected_rows( ) . students
  share the surname and were deleted lt/Pgt)

15

• 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

16

• 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
  rowstudent_id is the value of the student_id
  for that row.

17

• 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(ltpgt. rowstudent_id . lt/pgt
• Example of displaying all the student_ids in a
  web page
• lthtmlgt
• ltheadgt
• lttitlegtList of Student Numberslt/titlegt
• ltbodygt
• lt?php
• //connect to the database server
• conn mysql_connect(localhost, username,
  password)

18

• if (!conn) // if the connection
  failed
• die(ltpgtUnable to connect to the
  serverlt/pgt) // display error msg
• //otherwise go ahead and select the database
• if ( !mysql_select_db(database_name) ) //if
  it cant find the database
• die( ltpgtUnable to select the
  databaselt/pgt) // display message
• ?gt
• ltpgtHere are all the students in our database ltpgt
• lt?php
• result mysql_query(select student_id from
  students)
• if (!result ) die(ltpgt Error performing
  querylt/pgt)
• while (row mysql_fetch_array(result) )
• echo(ltpgt . rowstudent_id . lt/pgt)
• ?gt
• lt/bodygt lt/htmlgt

19

• Class Assignment
• Find out how to send emails dynamically, from a
  web page using PHP.
• 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

20
(No Transcript)
21
(No Transcript)