WebBased Database Programming with PHP

1
Web-Based Database Programming with PHP
2
In this lecture you will learn

• PHP Basics
• PHP functions
• To check and filter user inputs (security)
• To connect to database
• To select database
• To query database
• To work with the results
• BLOB types in MySQL
• Displaying BLOBS
• Debugging tips

3
PHP

• General purpose scripting language
• Can be used to achieve functionality similar to
  Java or C
• Similar to C in syntax
• Server side scripting language
• Code is run on the server and the resulting html
  page is shown in the browser
• This means, users cannot view the php source code
• Libraries used for accessing RDBMS
• Several vendor specific libraries available

4
PHP Variables

• Starts with symbol followed by name
• Name contains letters, numbers and underscore
• Cannot start with a number
• No special characters other than underscore (_)
• Case sensitive
• E.g. query and result
• Variables are not typed
• Type of value assigned determines a variables
  type
• Within a program, same variable can be assigned
  to values of different data types!!!
• this is a special variable that cannot be
  assigned
• Refers to the object of a class, similar to Java

5
PHP Arrays

• Two kind of arrays
• Numeric Arrays
• Similar to Java arrays
• Objects/values are accessed by a numerical index
• E.g. color array(Red,Blue,Green)
• redcolor color0
• Associative Arrays
• Objects/values are stored as pairs of
  (keygtvalue) elements
• Objects/values are accessed by specifying the key
• Similar to Java Maps
• E.g. lecturer array(CS2008gtYaji,
  CS2007gtEhud)
• cs2008Lecturer lecturerCS2008

6
Predefined Variables

• PHP provides several predefined variables to be
  used in scripts
• Depends upon the server setup
• Variables from HTML forms are available in an
  associative array _POST
• E.g. If _POST array(namegtJohn,
  agegt44,emailgtjohn_at_somewhere.com)
• Then age _POSTage and
• email _POSTemail
• In the practical, you will learn using variables
  from HTML forms in your PHP scripts.

7
PHP Strings

• Several Types of strings
• We use two string types
• Single quoted
• Use them for storing literal strings
• E.g. Yaji teaches CS2008
• Double quoted
• Use them with embedded variables
• E.g. cs2008Lecturer teaches CS2008
• Where cs2008LecturerYaji
• Several string manipulation functions
• Useful for checking and filtering user inputs

8
Check and filter data coming from the user

• User inputs may contain character(s) that might
  be harmful to the server or database
• Sometimes they may contain entire scripts that
  may cause problems to the server or database
• Never trust user inputs!!!
• PHP provides a number of functions to remove
  undesirable characters from user inputs.

9
While storing user input into MySQL

• Certain characters have special meaning for MySQL
• Example special characters
• Single quote ()
• Double quote ()
• Backslash (\)
• NULL
• You need to use the escape character,\ to mark
  them
• E.g., \ means
• \ means

10
addslashes() and stripslashes()

• addslashes(X) adds backslash characters to X
• E.g addslashes(Yajis laptop) will return
  Yaji\s laptop
• stripslashes(X) removes backslash characters from
  X
• PHP offers another way of adding and removing
  backslashes switch on the
• magic_quotes_gpc (for incoming data from browser)
  and
• magic_quotes_runtime (for data going to database)
  directives in your php.ini file
• You can use trim() function to remove extra white
  spaces

11
escapeshellcmd(), strip_tags() and
htmlspecialchars()

• escapeshellcmd(X) should be used before passing X
  to system() and exec() which run shell commands
• Before you echo back user input to the browser
  use
• strip_tags(X) strips out HTML and PHP tags from
  X
• htmlspecialchars(X) converts special characters
  to HTML equivalents
• E.g., lt is converted to lt

12
To connect to MySQL

• Syntax
• resource mysql_connect ( string server , string
  username , string password , bool new_link ,
  int client_flags)
• Server name of the server or IP address
• By default mysql_connect returns an existing open
  link
• Specify new_link if you want a new link
• Client_flags
• MYSQL_CLIENT_COMPRESS
• Use compression protocol
• MYSQL_CLIENT_IGNORE_SPACE
• Allow space after function names
• MYSQL_CLIENT_INTERACTIVE
• Allow interactive_timeout seconds (instead of
  wait_timeout) of inactivity before closing the
  connection.

13
include() or require()

• Your PHP script is never seen by your browser
• Password used in mysql_connect cannot be seen by
  users
• You may still like to create dbsetup.php to
  define global variables
• username
• server
• password
• And include(path.dbsetup.php) or
  require(path.dbsetup.php) in your .php file
• Include and require are same
• They differ only when they fail
• Include produces a warning
• Require results in a Fatal Error

14
mysql_error() mysql_errno()

• After calling any mysql functions (mysql_xxx)
• You can call mysql_error(link) and
  mysql_errno(link)
• link is the active connection to MySQL

15
Selecting a database

• After making the connection to MySQL server
• You need to select the database you want to work
  with using
• mysql_select_db()
• Syntax
• bool mysql_select_db ( string database_name ,
  resource link_identifier)
• Link_identifier is the active connection to MySQL

16
Querying

• Define the query string such as
• Query select from staff
• Then use mysql_query() to run the query
• Syntax
• resource mysql_query ( string query , resource
  link_identifier)
• E.g
• result mysql_query(Query,link)

17
Functions for Results Object

• PHP offers many functions for working with the
  results object
• array mysql_fetch_row ( resource result)
  fetches a row as an array
• object mysql_fetch_field ( resource result , int
  field_offset) fetches a field as an object
• array mysql_fetch_assoc ( resource result)
  fetches a row as an associative array
• You can access fields in the row with the field
  names
• E.g. rowlname
• int mysql_num_fields ( resource result)
• int mysql_num_rows ( resource result)

18
Closing the connection

• When the script ends executing connection is
  closed or
• Use
• mysql_free_result(result)
• Mysql_close(link)

19
Storing Multimedia - BLOBS

• How should large text files, images or sound
  files be stored in DBMSs ?
• Solution 1 Store a reference to an external
  file
• Solution 2 Store as a BLOB

20
Storing BLOBS in MySQL

• There are four BLOB data types in MySQL each with
  different maximum capacity
• TINYBLOB
• BLOB
• MEDIUMBLOB
• LONGBLOB
• BLOB columns are treated as binary strings
• For example
• CREATE img (id int(5) PRIMARY KEY, pic BLOB)

21
Displaying BLOB Images

• Usually, images are streamed to the client from a
  file on the server
• ltIMG SRC"images/the_image.jpg"gt
• If the image is stored in a database, we need the
  help of a script to stream the blob to the client
  from the database
• // id select_the_property_somehow()
• printf(ltimg src getImg.php?idbgt,id)
• You will use getImg.php in Practical 8...
• You are NOT required to learn stream
  programming!!

22
Debugging Tips

• Because PHP is not strongly-typed, and is
  interpreted at run-time on the server, debugging
  a faulty script can be very tricky!!!
• Use echo() often to print to the client!!
• Check code for "" at end of every statement!!
• Check code for matching pairs of quotes!!
• Avoid variable names like "system" !!
• Ask yourself is it a DB problem or a script
  problem??
• Check your SQL statement with MS-Access or MySQL
  client!!
• Divide-and-conquer comment-out doubtful code!!