INFO 321 Server Technologies II

1
INFO 321Server Technologies II

• LAMP

Partly adapted from notes by Dr. Randy M. Kaplan
2
Overview

• This set of notes are in these sections
• LAMP Overview
• PHP Introduction
• PHP Basic Syntax
• Installing PHP
• PHP Configuration
• Installing MySQL
• PHPMyAdmin
• MySQL Basics

3
LAMP Overview
4
The LAMP Stack

• LAMP comes from
• L Linux
• A Apache
• M MySQL
• P Perl/PHP/Python
• The LAMP stack is open source software that
  enables rapid development of web-based and
  database-based applications

5
Installing LAMP

• Apache needs to be installed with some special
  entries in its configuration script and files
• Before we get to mySQL, well need PHP to help
  administer mySQL
• Therefore assume that well be using PHP
• The P in LAMP can refer to any web-friendly
  programming language

6
Installing LAMP

• To install the LAMP stack you will need to
  install
• Linux
• Apache
• MySQL
• PHP or Perl or Python
• Linux is presumably already installed

7
Installing LAMP

• Theres a sneaky way to install LAMP all at once
  on Windows, Linux, and other platforms
• WAMP (as in Windows, Apache, MySQL, PHP)
• See also here for other options, e.g. MAMP for
  Mac OS X, XAMPP for Linux/UNIX, etc.

8
PHP Introduction
9
PHP

• PHP is a widely-used general-purpose scripting
  language that is especially suited for Web
  development and can be embedded into HTML
• PHP is a recursive acronym (!) for PHP
  Hypertext Preprocessor
• PHP is available from http//www.php.net
• PHP is on version 5.3.5 as of 6 Jan 2011

10
PHP Platforms

• PHP can be used on all major operating systems,
  including
• Linux
• Many Unix variants (e.g. HP-UX, Solaris and
  OpenBSD)
• Microsoft Windows
• Mac OS X (should this be under Unix variants?)
• RISC OS

11
PHP Binaries also exist for

• AS/400
• Mac OS X
• Novell NetWare
• OS/2
• RISC OS
• SGI IRIX 6.5.x
• Solaris (SPARC, INTEL)
• Solaris OpenCSW packages

From http//www.php.net/downloads.php
12
PHP Web Servers

• PHP has support for most web servers
• Apache
• Microsoft IIS and PWS
• Netscape and iPlanet servers
• OReilly Website Pro server
• Caudium, Xitami, OmniHTTPd, and others

13
PHP database support

• PHP can communicate with almost any database
  management system
• Adabas D, dBase, Empress, FilePro (read-only),
  Hyperwave, IBM DB2, Informix, Ingres, InterBase,
  FrontBase, mSQL, Direct MS-SQL, MySQL, ODBC,
  Oracle (OCI7 and OCI8), Ovrimos, PostgreSQL,
  SQLite, Solid, Sybase, Velocis, Unix dbm

14
What can PHP do?

• PHP is mainly focused on server-side scripting,
  so you can do anything any other CGI program can
  do
• Collect form data, generate dynamic page content,
  send and receive cookies, etc.
• But PHP can do much more

Summarized from http//www.php.net/manual/en/intro
-whatcando.php
15
What can PHP do?

• Command line scripting
• You can make a PHP script and run it without any
  server or browser
• You only need the PHP parser
• This type of usage is ideal for scripts regularly
  executed using cron (on Unix or Linux) or Task
  Scheduler (on Windows)
• Scripts can also be used for simple text
  processing tasks

16
What can PHP do?

• Writing desktop applications
• PHP is probably not the best language to create a
  desktop application with a graphical user
  interface, but it can be done
• Use PHP-GTK to write such programs
• WinBinder is a (Windows only) alternative to
  PHP-GTK

17
What can PHP do?

• Server-side scripting is the most traditional and
  main target field for PHP
• You need three things to make this work, a PHP
  parser (CGI or server module), a web server and a
  web browser
• You need to run the web server, with a connected
  PHP installation
• You can access the PHP program output with a web
  browser, viewing the PHP page through the server

18
PHP output types

• A PHP server often outputs HTML, but it can also
  output
• Images
• PDF files
• Flash movies
• Any text, such as XHTML or other XML file

19
PHP Basic Syntax
Summarized from http//www.php.net/manual/en/langu
age.basic-syntax.php
20
PHP example

• lt!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Trans
  itional//EN"    "http//www.w3.org/TR/html4/loose
  .dtd"gtlthtmlgt    ltheadgt        lttitlegtExamplelt/t
  itlegt    lt/headgt    ltbodygt        lt?php       
       echo "Hi, I'm a PHP script!"        ?gt    
  lt/bodygtlt/htmlgt

21
PHP example

• The lt?php and ?gt are start and end processing
  instructions (a.k.a. opening and closing tags)
• The PHP server interprets them, and sends HTML to
  your web browser ? key concept!
• PHP is done server-side, whereas JavaScript is
  done on the client

22
PHP is server interpreted
23
semicolons!

• Notice the commands end with a semicolon, like
  most C-ish languages
• PHP requires instructions to be terminated with a
  semicolon at the end of each statement
• The closing tag of a block of PHP code
  automatically implies a semicolon
• You do not need to have a semicolon terminating
  the last line of a PHP block, it adds an extra
  whitespace if you do

24
Short and long tags

• Youll see examples of PHP with start and end
  processing tags like these
• lt? Stuff ?gt
• These are called short tags, which by default are
  enabled, but should be avoided
• short_open_tag On
• Please use the officially correct long tags
• lt?php other stuff ?gt

25
Script tags

• Note If you are embedding PHP within XML or
  XHTML you will need to use the lt?php ?gt tags to
  remain compliant with standards
• Other allowed opening and closing tags define the
  script language
• ltscript language"php"gt stuff lt/scriptgt
• FrontPage prefers this

26
Accidental closing tags

• One aspect of PHP that you need to be careful of,
  is that ?gt will drop you out of PHP code and into
  HTML even if it appears inside a // comment
• This does not apply to / blah / multi-line
  comments

27
Comments

• lt?php    echo 'This is a test' // This is a one-
  line c style comment    / This is a multi line
   comment       yet another line of comment /   
   echo 'This is yet another test'    echo 'One Fi
  nal Test'  This is a one-line shell-style commen
  t?gt

28
Comments

• PHP supports three different styles 'C', 'C'
  and Unix shell-style (Perl style)
• // This is a one-line c style comment
•  / This is a multi line comment       yet anothe
  r line of comment /
• Dont try to nest them!
•  This is a one-line shell-style comment
• One-line comments go to EOL or end of php block

29
Installing PHP
30
Prerequisites for building PHP

• The following software is needed to build PHP
• An ANSI C compiler
• flex Version 2.5.4
• bison Version 1.28 (preferred), 1.35, or 1.75
• A web server
• Any module specific components (such as GD, PDF
  libs, etc.)

From http//www.php.net/manual/en/install.unix.php

31
Installing PHP

• Download the PHP source follow the standard
  procedure for installing a new software
  application
• 1. gzip -d httpd-2_0_NN.tar.gz
• 2. tar xvf httpd-2_0_NN.tar
• 3. gunzip php-NN.tar.gz
• 4. tar -xvf php-NN.tar
• 5. cd httpd-2_0_NN
• 6. ./configure --enable-so For multi-core
  processors add --enable-shared-core
• 7. make
• 8. make install

http//www.php.net/manual/en/install.unix.apache2.
php
32
Installing PHP

• PHP uses an ini file a kind of configuration
  file
• A configuration file is supplied in the source
  directory
• Copy the php.ini file to the appropriate
  directory
• cp php.ini-dist /usr/local/lib/php.ini

33
http.conf Modifications for PHP

• Heres the Apache connection
• The http.conf file needs to be modified so that
  Apache knows what to do when it encounters PHP
• Lines are added to the .conf file where similar
  lines are placed (have a look at the default
  http.conf file)

34
http.conf Modifications for PHP

• Load the PHP 5 module
• LoadModule php5_module modules/libphp5.so
• Handle how file types are to be processed
• AddHandler application/x-httpd-php    .php
• AddHandler application/x-httpd-php-source   
  .phps

35
Stop and Restart Apache

• Once you have modified the http.conf file, in
  order to recognize the new setting you will need
  to stop and restart the server
• Use apachectl to accomplish this

36
Testing PHP and Apache

• One way to test to see if Apache is correctly in
  place is to write some PHP and see if it runs as
  it should
• A quick and dirty test would be the canonical
  Hello World program in PHP
• A better test is to continue configuration of the
  LAMP stack so that you can see some significant
  functionality demonstrated

37
PHP Configuration
38
PHP configuration file

• There is a configuration file in PHP, php.ini
• Its in the path designated by the environment
  variable PHPRC
• Under Linux/Unix, its default location is
  /usr/local/lib or ltinstall-pathgt/lib
• On Windows, its in the Windows directory
• For the server versions of PHP, its read only
  once when the web server is started

39
Sample php.ini file

• any text on a line after an unquoted semicolon
  () is ignored
• php section markers (text within square
  brackets) are also ignored
• Boolean values can be set to either
• true, on, yes
• or false, off, no, none
• register_globals off
• track_errors yes
• you can enclose strings in double-quotes
• include_path "./usr/local/lib/php"
• backslashes are treated the same as any other
  character
• include_path ".c\php\lib"

Notice that path statements do not include the
actual file name
40
php.ini file syntax

• Notice that the syntax in the PHP configuration
  file is different from within PHP scripts!
• Comments start with a semicolon, and can start
  mid-line
• Section markers anything between square
  brackets are also ignored
• Most lines are directive value format

41
PHP configuration file

• The php.ini file has core directives, and may
  have extensions
• The default php.ini file has well documented
  dozens of options from which you can choose
• The php.ini file must have that name!
• You can have multiple versions in different
  directories (hence the PATH importance)

42
PHP directives

• Directive names are case sensitive
• The value assigned can be
• A string, a number
• A PHP constant (e.g. E_ALL or M_PI)
• An INI constant (On, Off, True, False, Yes, No or
  None)
• An expression (e.g. E_ALL E_NOTICE)
• A quoted string ("foo")

43
User configuration file

• The php.ini file pertains to the entire PHP
  install
• Individual users may have a personal
  configuration file, .user.ini
• user_ini.filename ".user.ini

44
Shy PHP

• Your PHP install can hide itself from the outside
  world (e.g. for security reasons) by changing
  this default setting
• expose_php On

45
Php.ini sections

• Language Options
• Resource Limits
• Error handling and logging
• Data Handling
• Unicode settings
• Paths and Directories
• File Uploads (to allow or not)
• Fopen wrappers (allows treatment of URLs as
  files)
• Dynamic Extensions
• Module Settings (incl. mySQL and cookie settings)

46
Installing MySQL
47
Install MySQL

• MySQL is an open source, enterprise class,
  database management system
• It is fully compliant with the SQL standard
  although, unlike products like Oracle that have
  opted for a rich base of features, MySQL has
  opted for simplicity
• All basic functionality is available with
  perhaps a bit less slickness than other products

48
Getting MySQL

• MySQL is available from http//www.mysql.com/
• The MySQL Community Server is the free version
• The MySQL Enterprise Subscription is about
  600/year per server

49
Download an Installable Image

• In the case of MySQL, building (compiling) the
  database management system does not result in
  major benefits unless the platform you are using
  is special
• Downloads are available from here
• The current version is 5.5.9

50
MySQL Installation

• In the case of windows, the installation package
  comes in a zipped file
• In the zip file is another named setup.exe
• Double click (Windows) this file and an installer
  will launch and walk you through installation
• Once the MySQL server is started, you can check
  to see if it is running using the command line
  client

51
MySQL Installation

• When you install MySQL, an All Programs menu
  option is added to start the command line client

52
PHPMyAdmin
53
PHPMyAdmin

• One of the benefits of open source is that
  programmers are free to develop tools of their
  own choosing to benefit the community
• One such tool is PHPMyAdmin, currently on version
  3.3.9.2
• PHPMyAdmin is available from here
• Its compatible with PHP 5 and MySQL 5

54
PHPMyAdmin

• We demonstrated earlier how to test MySQL using
  the command line to fire up a client so that we
  could enter some SQL
• Although this might be a good way for those who
  live and breathe SQL, some help might be a good
  thing to have
• PHPMyAdmin is one such tool that offers help in
  the management of MySQL

55
PHPMyAdmin

• PHPMyAdmin is a GUI based interface for managing
  MySQL
• It goes a little further because with it we can
  carry out extensive data manipulation
• It is written in PHP and its interface mechanism
  is browser-based

56
PHP Beyond Hello World
57
Our first PHP script hello.php

• lthtmlgt ltheadgt  lttitlegtPHP Testlt/titlegt lt/headgt
   ltbodygt  lt?php echo 'ltpgtHello Worldlt/pgt' ?gt  
  lt/bodygtlt/htmlgt

From http//us3.php.net/manual/en/tutorial.firstpa
ge.php
58
About Hello World

• Notice the file is hello.php, not hello.html
• The file does not have to be executable, just a
  plain boring text file

59
phpinfo function

• Make a call to the phpinfo() function and you
  will see a lot of useful information about your
  system and setup such as available predefined
  variables, loaded PHP modules, and configuration
  settings
• lt?php phpinfo() ?gt

60
_SERVER

• _SERVER is a reserved PHP variable that contains
  all web server information
• lt?phpecho _SERVER'HTTP_USER_AGENT'?gt
• May get a response of
• Mozilla/4.0 (compatible MSIE 6.0 Windows NT 5.1)

61
Superglobal variables

• More generally, _SERVER is a superglobal
  variable
• They are available throughout any script
• The others are GLOBALS, _GET, _POST,
  _FILES, _COOKIE, _SESSION, _REQUEST, and
  _ENV

62
Environment Variables

• Environment variables (_ENV) are data from the
  PHP parsers host system
• For example, we can find the host name
• lt?php if (isset(_ENV"HOSTNAME"))    
  MachineName _ENV"HOSTNAME" else if 
  (isset(_ENV"COMPUTERNAME"))     MachineName
  _ENV"COMPUTERNAME" else MachineName ""
  ?gt

63
Environment Variables

• This example determines if a particular variable
  name has been set (isset)
• Then assigns the correct variable to the local
  variable MachineName
• The if / elseif / else structure is from C
• Note that the if and elseif lines dont have
  semicolons

64
Environment Variables

• We can get the user name like this
• lt?phpecho 'My username is ' ._ENV"USER" . '!'
  ?gt
• Or get cookie information from _COOKIE
• lt?php// Print an individual cookieecho _COOKIE
  "TestCookie"// Another way to debug/test is to 
  view all cookiesprint_r(_COOKIE)?gt

65
Other PHP functions

• There are zillions (approximately) of functions
  predefined for use by PHP
• Audio Formats Manipulation
• Authentication Services
• Calendar and Event Related Extensions
• Command Line Specific Extensions
• Compression and Archive Extensions
• Credit Card Processing

66
Other PHP functions

• Cryptography Extensions
• Database Extensions
• File System Related Extensions
• Human Language and Character Encoding Support
• Image Processing and Generation
• Mail Related Extensions
• Mathematical Extensions

67
Other PHP functions

• Non-Text MIME Output
• Process Control Extensions
• Connecting to Java, other Internet apps, general
  networking (sockets, TCP, etc.)
• Search Engine Extensions
• Server Specific Extensions
• Session Extensions

68
Other PHP functions

• Text Processing
• Variable and Type Related Extensions
• Web Services
• Windows Only Extensions
• XML Manipulation

69
PHP Programming
70
Programming variables

• PHP does not require (or support) explicit type
  definition in variable declaration
• A variable's type is determined by the context in
  which the variable is used

71
Programming variables

• PHP has four basic variable types
• boolean (TRUE or FALSE, case-insensitive)
• integer (between /- 2.15E9 or 231)
• float (floating-point number, aka double)
• Precision varies with platform
• string (1 character 1 byte, hence no Unicode
  direct support in PHP5)
• Often use single quotes , with a backslash
  before a literal quote \ or to get a literal
  backslash \\

INFO 321
Weeks 7-8
71
72
Programming variables

• PHP is very lax about variable typing
• Declarations arent needed
• lt?phpa_bool  TRUE   // a boolean True also
  worksa_str   "foo"  // a stringan_int  12 
      // an integerecho gettype(a_bool) // prints
   out  booleanecho gettype(a_str)  // prints ou
  t  string

73
Programming variables

• // If this is an integer, increment it by fourif 
  (is_int(an_int))     an_int  4
  // If bool is a string, print it out// (does n
  ot print out anything)if (is_string(a_bool))  
     echo "String a_bool" ?gt

74
PHP arrays

• An array in PHP is a series of comma-separated
  key gt value pairs
• lt?phparr  array("somearray" gt array(6 gt 5, 
  13 gt 9, "a" gt 42))echo arr"somearray"6 
     // yields 5echo arr"somearray"13   // 9
  echo arr"somearray""a"  // 42?gt
• Key must be an integer or string value may be
  any type

75
Objects

• PHP 5 is object oriented
• new instantiates an object from the class
• lt?phpclass foo     function do_foo()
          echo "Doing foo." 
  bar  new foobar-gtdo_foo()?gt

76
Objects

• lt?phpclass MyClass class NotMyClass
  a  new MyClassvar_dump(a instanceof MyClass
  )var_dump(a instanceof NotMyClass)?gt
• Yieldsbool(true) bool(false)

77
Resources

• A resource is a special variable, holding a
  reference to an external resource
• Resources are created and used by special
  functions (link is to index of them)
• The function is_resource() can be used to
  determine if a variable is a resource
• get_resource_type() will return the type of
  resource it is

78
Resources

• Relevant creation resources include
• mysql_connect() (Link to MySQL database)
• mysql_pconnect() (Persistent link to MySQL)
• mysql_db_query(), mysql_list_dbs(),
  mysql_list_fields(), mysql_list_processes(),
  mysql_list_tables(), mysql_query(),
  mysql_unbuffered_query() (MySQL result)

79
Resources

• odbc_connect() (Link to ODBC database)
• odbc_pconnect() (Persistent link to ODBC
  database)
• odbc_prepare() (ODBC result)

80
NULL variables

• NULL (or null) variables and values are allowed
• lt?phpvar  NULL       ?gt
• See also the functions is_null() and unset()
• The is-exactly-equals comparison () can also
  check a null or boolean variable

81
Timing processing

• lt?phpv NULLs microtime(TRUE)for(i0
  ilt1000 i)     is_null(v) print
  microtime(TRUE)-sprint "ltbrgt"s
  microtime(TRUE)for(i0 ilt1000 i)    
  vNULL print microtime(TRUE)-s?gt
• Results0.0179820060729980.0005950927734375Usin
  g "" is 30x quicker than is_null()

82
Variable empty or not?

• Compare empty(), is_null(), and !isset()
• var ""empty(var) is trueis_null(var) is
  false!isset(var) is false.

83
Type juggling

• lt?phpfoo  "0"  // foo is string (ASCII 48)f
  oo  2   // foo is now an integer (2)foo  f
  oo  1.3  // foo is now a float (3.3)foo  5 
   "10 Little Piggies" // foo is integer (15)foo
    5  "10 Small Pigs"     // foo is integer (15
  )?gt
• Strings with a period, e, or E (e.g. 1e-3 or
  23.4) are interpreted as float, otherwise the
  leading integer value is used

84
Variables

• Variables in PHP are represented by a dollar sign
  followed by the name of the variable
• The variable name is case-sensitive
• A variable can be assigned by reference.
• This means that the new variable "points to" the
  original variable

85
Variables

• Changes to the new variable affect the original,
  and vice versa
• Only named variables may be assigned by reference
  
• lt?phpfoo  'Bob'  // Assign the value 'Bob' to 
  foobar  foo  // Reference foo via bar.b
  ar  "My name is bar"  // Alter bar...echo ba
  recho foo     // foo is altered too.?gt

86
Predefined variables

• The superglobal variables are predefined, as are
• php_errormsg The previous error message
• HTTP_RAW_POST_DATA Raw POST data
• http_response_header HTTP response headers
• argc The number of arguments passed to script
• argv Array of arguments passed to script

87
Detecting settings

• To detect user settings (video resolution,
  browser type, etc.) try this link(link removed)

88
Expressions

• Most logical comparison operators are allowed
• lt gt gt lt !
• (identical, equal to and same type)
• ! (not equal to or not same type).
• These can also be used on arrays

89
Ternary expressions

• lt?phpfirst ? second  third?gt
• If the value of the first subexpression is TRUE
  (non-zero), then the second subexpression is
  evaluated, and that is the result of the
  conditional expression. Otherwise, the third
  subexpression is evaluated, and that is the
  value.

90
Increment/decrement operators

• a Increments a by one, then returns a
• a Returns a, then increments a by one
• --a Decrements a by one, then returns a
• a-- Returns a, then decrements a by one

91
Nesting assignments

• An assignment statement has a value of the value
  assigned, so its possible to nest them
• lt?phpa  (b  4)  5 // a is equal to 9 now, 
  and b has been set to 4.?gt

92
Execution operator

• PHP supports one execution operator backticks
  ()
• Not single-quotes()!
• PHP will execute the contents of the backticks as
  a shell command (e.g. bash, csh)
• The output will be returned (i.e., it won't
  simply be dumped to output it can be assigned to
  a variable)

93
Execution operator

• Use of the backtick operator is identical to
  shell_exec()
• lt?phpoutput  ls -alecho "ltpregtoutputlt/pregt
  "?gt

94
Error control operator

• The only error control operator is the at symbol,
  _at_
• Using it before an expression (variables,
  function and include() calls, constants, etc.)
  prevents error messages from appearing

95
Error message logging

• lt?php session_start()  function error(error,
  returnFALSE)       global php_errormsg  
     if(isset(_SESSION'php_errors'))        
  _SESSION'php_errors' array()  
  _SESSION'php_errors' error // Maybe use
  php_errormsg  if(return TRUE)    
  message ""       foreach(_SESSION'php_error
  s' as php_error)           messages .
  php_error."\n"       return messages //
  Or you can use use _SESSION'php_errors'
  ?gt

96
Logical operators

• And a and b
• Or a or b
• Xor a xor b
• Not ! a
• And a b
• Or a b
• Why are there two Ors and two Ands?

97
Logical operators

• Cute example using or
• lt?php//If the connection was success, "Connected
  to database" will be shown. //If the connection
  was failed, "Unable to connect" will be
  shown.(NOTE The _at_ will hide error
  messages)_at_mysql_connect("localhost", "root",
  "password") or die("Unable to connect")echo
  "Connected to database"?gt

98
String operations

• The period is critical for string operations,
  specifically concatenation
• lt?phpa  "Hello "b  a . "World!" // now b
   contains "Hello World!"a  "Hello "a . "Wo
  rld!"     // now a contains "Hello World!"?gt

99
Control statements

• Many control structures are available
• if else elseif/else if
• while
• do-while
• for
• foreach (nice for arrays)
• switch (case statement)

100
Custom functions

• Functions can be user-defined, like in most
  languages
• lt?phpfunction foo(arg_1, arg_2, / ..., / arg
  _n)    echo "Example function.\n"    return 
  retval?gt

101
MySQL Basics
102
mySQL structure

• mySQL is running on a host, which may be
  different from the client host youre using to
  access it
• mySQL contains databases
• Each database typically includes many tables
• A table has one or more fields (columns)
• Every data entry in a table is a record (row)

103
Connecting

• You connect to MySQL via a given host server and
  user name with the mysql command
• shellgt mysql -h host -u user -p
• Enter password
• You should get a welcome message
• Welcome to the MySQL monitor. Commands end with
  or \g.
• Your MySQL connection id is 25338 to server
  version 5.1.39-standard
• Type 'help' or '\h' for help. Type '\c' to clear
  the buffer.
• mysqlgt

104
Connecting

• If youre on the mySQL host already, can omit the
  host parameter
• shellgt mysql -u user p
• If your mySQL configuration allows anonymous
  logins, then this will work
• shellgt mysql

105
Disconnecting

• To leave mySQL, QUIT (or \q) works
• mysqlgt QUIT
• Bye
• On UNIX/Linux, control D also exits

106
mySQL command principles

• A command normally consists of an SQL statement
  followed by a semicolon
• There are some exceptions where a semicolon may
  be omitted QUIT is one
• When you issue a command, mysql sends it to the
  host server for execution and displays the
  results, then prints another mysqlgt prompt to
  indicate that it is ready for another command

107
mySQL command principles

• mysql displays query output in tabular form (rows
  and columns)
• The first row contains labels for the columns
• The rows following are the query results
• Normally, column labels are the names of the
  columns you fetch from database tables
• If you're retrieving the value of an expression
  rather than a table column, mysql labels the
  column using the expression itself

108
mySQL command principles

• mysql shows how many rows were returned and how
  long the query took to execute, which gives you a
  rough idea of server performance
• These values are imprecise because they represent
  wall clock time (not CPU or machine time), so
  they are affected by factors such as server load
  and network latency

109
Case sensitivity

• mySQL is case insensitive
• These are all equivalent commands
• mysqlgt SELECT VERSION(), CURRENT_DATE
• mysqlgt select version(), current_date
• mysqlgt SeLeCt vErSiOn(), current_DATE

110
mySQL examples

• mySQL
• mysqlgt SELECT VERSION(), CURRENT_DATE
• -------------------------------
• VERSION() CURRENT_DATE
• -------------------------------
• 5.1.2-alpha-log 2005-10-11
• -------------------------------
• 1 row in set (0.01 sec)
• mysqlgt

111
mySQL examples

• mySQL as a calculator for expressions
• mysqlgt SELECT SIN(PI()/4), (41)5
• ---------------------------
• SIN(PI()/4) (41)5
• ---------------------------
• 0.70710678118655 25
• ---------------------------
• 1 row in set (0.02 sec)

112
Multiple commands

• Many commands can appear on one line, separated
  by semicolons
• mysqlgt SELECT VERSION() SELECT NOW()
• -----------------
• VERSION()
• -----------------
• 5.1.2-alpha-log
• -----------------
• 1 row in set (0.00 sec)
• ---------------------
• NOW()
• ---------------------
• 2005-10-11 151500
• ---------------------
• 1 row in set (0.00 sec)

113
Waiting for semicolon

• Commands can span multiple lines, since mySQL
  wont do anything until after a semicolon
• mysqlgt SELECT
• -gt USER()
• -gt ,
• -gt CURRENT_DATE
• -----------------------------
• USER() CURRENT_DATE
• -----------------------------
• jon_at_localhost 2005-10-11
• -----------------------------

114
Oh, nevermind!

• To cancel a partial command, add \c in it
• mysqlgt SELECT
• -gt USER()
• -gt \c
• mysqlgt
• If \c is inside a text string, it will not cancel
  the command
• this is a string with \c in it

115
Text strings

• You can write strings surrounded by either ' or
  " characters (for example, 'hello' or
  "goodbye")
• mysql lets you enter strings that span multiple
  lines
• mysqlgt SELECT FROM my_table WHERE name
  'Johnson-
• 'gt Smith' AND age lt 30

116
Text strings

• When you see a 'gt or "gt prompt, it means that you
  have entered a line containing a string that
  begins with a ' or " quote character, but
  have not yet entered the matching quote that
  terminates the string.
• This might mean you left out a closing quote
• mysqlgt SELECT FROM my_table WHERE name 'Smith
  AND age lt 30
• 'gt

117
Text strings

• Hence a prompt of 'gt or "gt may mean that mysql
  expects to see the rest of an unterminated string
• How resolve this?
• Often best to close the string, then cancel the
  command
• '\c
• Why not just close the string?

118
SQL commands

• Naturally most mySQL commands are directly from
  SQL (or here or lots of books)
• mysqlgt SHOW DATABASES
• ----------
• Database
• ----------
• mysql
• test
• tmp
• ----------

119
Database permissions

• Check your privileges to use a database with the
  USE command
• mysqlgt USE test
• Database changed
• Notice the lack of semicolon its optional for
  this command
• And USE must be the only command on the line

120
PHP and mySQL

• The key PHP extension to connect it to mySQL is
  the mysqli class
• lt?php link mysqli_connect( 'localhost',      
          'user',  'password',               'world
  ') / default db / if (!link) printf("Can't
  connect to MySQL Server. Errorcode s\n",
  mysqli_connect_error())    exit

From Zend developer zone
121
PHP and mySQL

• mysqli_connect is an improved version of
  mysql_connect, for PHP5 and mySQL4.1 or higher
• Do not use mysql_pconnect it doesnt play nicely
  with LAMP

122
PHP and mySQL

• / Send a query to the server / if (result
  mysqli_query(link, 'SELECT Name, Population FROM
  City ORDER BY Population DESC LIMIT 5'))
      print("Very large cities are\n")     /
  Fetch the results of the query /     while(
  row mysqli_fetch_assoc(result) )
          printf("s (s)\n", row'Name',
  row'Population')       / Destroy the
  result set and free the memory used for it /
      mysqli_free_result(result)

123
PHP and mySQL

• This example would produce output like
• Very large cities areMumbai (Bombay)
  (10500000)Seoul (9981619)São Paulo
  (9968485)Shanghai (9696300)Jakarta (9604900)

124
PHP and mySQL

• Close a mySQL connection like this
• / Close the connection / mysqli_close(link)
  ?gt

125
Prepared statements

• mySQL has two useful types of prepared statements
• Bound parameter prepared statements
• Bound result prepared statements
• Both help you create queries that are more
  secure, have better performance, and are more
  convenient to write

126
Bound parameter prepared statements

• Bound parameter prepared statements allow query
  templates to be created and then stored on the
  MySQL server
• The body of the query is only sent to the MySQL
  server once
• When a query is needed, data to fill in the
  template is sent to the MySQL server, and a
  complete query is formed and then executed
• To execute the query, only the data to fill in
  the template needs to be delivered to the MySQL
  server

127
Bound result prepared statements

• Bound result prepared statements allow the value
  of variables in a PHP script to be tied to the
  value of fields of data in a query result set
• Create a query
• Ask the MySQL server to prepare the query
• Bind PHP variables to columns in the prepared
  query
• Ask the MySQL server to execute the query
• Request that a new row of data be loaded into the
  bound variables

128
Prepared statement example

• lt?php mysqli new mysqli("localhost", "user",
  "password", "world") if (mysqli_connect_errno())
      printf("Connect failed s\n",
  mysqli_connect_error())     exit()

129
Prepared statement example

• / prepare statement / if (stmt
  mysqli-gtprepare("SELECT Code, Name FROM Country
  WHERE Code LIKE ? LIMIT 5"))     stmt-gtbind_pa
  ram("s", code)     code "C"
      stmt-gtexecute()

130
Prepared statement example

•     / bind variables to prepared statement /
      stmt-gtbind_result(col1, col2)     /
  fetch values /     while (stmt-gtfetch())
          printf("s s\n", col1, col2)     
      / close statement /     stmt-gtclose()

131
Prepared statement example

• / close connection / mysqli-gtclose() ?gt
• This example uses the object oriented format of
  commands instead of the procedural syntax
• mysqli new mysqli("localhost", "user",
  "password", "world")
• link mysqli_connect( 'localhost',  'user',  'p
  assword', 'world')

132
OO versus procedural syntax

• mysqli-gtconnect_error vs mysqli_connect_error
• mysqli-gtclose vs mysqli_close
• mysqli-gtquery vs mysqli_query
• mysqli_stmt-gtbind_param vs mysqli_stmt_bind_param

From Section 2.7 of the PHP API
133
Binding parameters

• In the binding of parameters (bind_param), each
  variable to be bound needs a character to define
  its type
• BIND TYPE COLUMN TYPE
• i All INT types
• d DOUBLE and FLOAT
• b BLOBs
• s All other types

134
References

• PHP
• PHP Manual
• Server processing image
• PHP configuration file
• MySQL
• Tutorial
• Installation guide
• PHPMyAdmin