Advanced Commercial Web Site Design: ServerSide Scripting

1
Advanced Commercial Web Site Design Server-Side
Scripting

• Session 10
• PHP and MySQL

2
Outline

• Connecting to a database
• Creating SQL queries
• Retrieving data sets

3
Connecting to a database server

• Syntax mysql_connect( hostName, userName,
  password )
• hostName the host where the database server is
  running.In our case, localhost will be the
  literal to use, as the database server is running
  on the same machine (host) as the web server.
• userName-password the user name and the password
  that will authenticate the user to the
  database.They will be given to you at the class.
• Example
• user dimi
• pass
• mysql_connect( localhost, user, pass )

4
Selecting a database

• Syntax mysql_select_db( databaseName )
• databaseName the name of the database a user
  wants to work on
• Example
• mysql_select_db( evbrack1_webw )
• Note Every time a connection to the database
  server is made, a database should also be
  selected. An error message Database not
  selected will indicate that a SQL statement is
  executed without having first selected a
  database.

5
Constructing SQL queries

• Syntax mysql_query( query )
• query a basic SQL statement, such as SELECT,
  INSERT, CREATE etc.
• It returns True (non-zero integer) if the query
  was successful, otherwise it returns False.
• Example
• query SELECT surname FROM grades WHERE mark
  gt 12
• result mysql_query( query )

6
Counting the affected records

• Syntax mysql_affected_rows( )
• It returns the number of records (rows) that
  were modified from the last query.
• Conditions
• The last mysql_query() returned TRUE.
• The last mysql_query() was not a SELECT
  statement.
• Example
• query DELETE FROM grades WHERE mark lt 7
• result mysql_query( query )
• numOfAffectedRows mysql_affected_rows( )

7
Counting the size of the result set

• Syntax mysql_num_rows( )
• It returns the number of records (rows) that
  satisfied the conditions of the SELECT statement.
• Conditions
• The last mysql_query() returned TRUE.
• The last mysql_query() was a SELECT statement.
• Example
• query SELECT surname FROM grades WHERE mark
  gt 12
• result mysql_query( query )
• sizeOfResultSet mysql_num_rows( )

8
Retrieving the result set

• Once a SELECT statement is made, the result set
  (all the records that satisfy the condition)
  exist in an intermediate state.
• In order to get hold of the result set, one
  needs to fetch the records from this
  intermediate state and make them available to
  PHP.
• The fields of a record can be returned as an
  array, an object or a single field.

9
Retrieving the fields as an array

• Syntax mysql_fetch_row( resultSet )
• resutSet the set of the records returned by a
  successful SELECT statement.
• It returns the fields of the record as an
  enumerated array. Multiple calls to this function
  are needed to retrieve the whole result set.
• Example
• query SELECT surname, mark FROM grades WHERE
  mark gt 12
• result mysql_query( query )
• row mysql_fetch_row( result )
• surname row 0
• mark row 1

10
Retrieving the fields as an array (cont.)

• Syntax mysql_fetch_array( resultSet )
• resutSet the set of the records returned by a
  successful SELECT statement.
• It returns the fields of the record as an
  enumerated or associative array. Multiple calls
  to this function are needed to retrieve the whole
  result set.
• Example
• query SELECT surname, mark FROM grades WHERE
  mark gt 12
• result mysql_query( query )
• row mysql_fetch_array( result )
• surname row surname
• mark row mark

11
Retrieving the whole result set

• query SELECT surname, mark FROM grades WHERE
  mark gt 12
• result mysql_query( query )
• while ( row mysql_fetch_array( result ) )
• echo Surname . rowsurname
• echo Mark . rowmark
• query SELECT surname, mark FROM grades WHERE
  mark gt 12
• result mysql_query( query )
• while ( list(surname, mark)
  mysql_fetch_array( result ) )
• echo Surname . surname
• echo Mark . mark

12
Exercise 10.1

• Create a script that
• Connects to our database
• Executes queries that
• Return all the students with no marks
• Change the name of a specific student (given its
  surname)
• Return all the students with a given surname
• Add a new student with all the details (name,
  surname, mark and date)
• (You may need to create the table first, using
  phpMyAdmin, with some records)
• Whenever possible, show the result set returned
  from the query, in a well-presented way.

13
Exercise 10.2

• Consider the Exercise 7.1, where in the first two
  pages (s1 and s2) the departing and arrival
  points in the ltselectgt tags were hard-coded by
  the programmer.
• Based on the same scripts, create two tables in
  our database that contain the departure and
  arrival points, respectively.For example, the
  table that contains the departures may be
  called xx_departurePoints(Remember to name the
  tables with your initials at the beginning!)
• Both tables should have the following attributes
• id (integer, auto-increment, primary)
• name (string, 20 chars)
• terminal (string, 5 chars)
• details (text)

14
Exercise 10.2 (cont.)

• Populate the tables with appropriate values.
• The tables maybe created using either the
  graphical GUI of MySQL (phpMyAdmin) or PHP
  scripts.
• Alter the scripts s1.php and s2.php in order to
  obtain the values from the tables of the
  database,xx_departurePoints, xx_arrivalPoints