INFO 321 Server Technologies II - PowerPoint PPT Presentation

1 / 134
About This Presentation
Title:

INFO 321 Server Technologies II

Description:

There's a sneaky way to install LAMP all at once on Windows, Linux, and other platforms ... Solaris (SPARC, INTEL) Solaris OpenCSW packages. From http://www.php. ... – PowerPoint PPT presentation

Number of Views:204
Avg rating:3.0/5.0
Slides: 135
Provided by: rkap7
Category:

less

Transcript and Presenter's Notes

Title: 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.0 as of 30 June 2009

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

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

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
Write a Comment
User Comments (0)
About PowerShow.com