PHP and MySQL

1
PHP and MySQL
2
PHP

• Written as a set of CGI binaries in C in 1994 by
  R. Lerdorf
• Didnt just want to post his resume
• Created PHP to display resume and collect data
  about page traffic, e.g. dynamic web pages
• Personal Home Page tools publicly released 1995
• In 1998 became PHP Hypertext Preprocessor

3
PHP

• Creates DYNAMIC web pages
• HTML traditionally static
• Contents regenerated every time visit or reload
  site
• (e.g. can include current time)
• PHP is a scripting language
• a programming language that controls a software
  application (program is independent of any other
  application)
• Strong at communicating with program components
  written in other languages
• E.g. can embed PHP statements within HTML
• Script like a dialogue for play interpreted by
  actors
• PHP parser with web server and web browser, model
  similar to MS ASP.NET, Sun JavaServer Pages

4
PHP

• Takes input from a file or stream containing text
  and PHP instructions
• Outputs stream of data for display
• PHP originally interpreted, not converted to
  binary executable files
• PHP 4 parser compiles input to produce bytecode
  (semi-compiled) Zend engine (better performance
  than interpreted PHP 3)
• PHP 5 robust support for OO programming, better
  support for MySQL, support for SQLite,
  performance enhancements

5
PHP - specifics

• Delimiters lt?php ?gt or just lt? ?gt
• PHP parses code within delimiters
• Code outside delimiter sent to output, not parsed
• Block comments / /
• Inline comments //

6
PHP vs. C

• Similarities
• Syntax nearly the same (For/While/If)
• Requires semicolons after each statement
• Assignment is right to left (num 56)
• Object-Oriented (Class support, inheritance,
  virtuals, polymorphism)
• Functions!
• Types are nearly the same (booleans, integers,
  strings, etc.)

7
PHP Versus C

• Differences
• Variables begin with sign (name John Doe)
• No explicit declaration of variable types
• Introduction of lazy functions (foreach,
  explode, mail)
• No Function Overloading
• Hidden functions-within-a-function
• Compiled/interpreted during every page load
• Documented!
• Echo for output

8
PHP Versus C

• Web Specific
• Cookies and Sessions
• Dynamic HTML based on user-defined logic
• Interact and process a forms action
• Process URL Parameters
• Easy Database Integration
• Cross-Site-Scripting (XSS) security hacks - taken
  care of by PHP 5
• code injection by web users into web pages viewed
  by other users (e.g. phishing attacks)

9
Introducing/Review HTML

• Hyper Text Markup Language
• Paired by angled brackets like XML (Ex. ltfontgt
  lt/fontgt)
• Dynamic web languages such as PHP simply produce
  HTML

10
Sample code

• lt?php // do not put a space between ? and php
• Echo Hello World // can use either or
• ?gt
• To run this, only need to specify a link to this
  program
• http//lamp.cs.ua.edu/vrbsky/Hello.php

11
Easy Database Integration

• For example
• MySQL

12
MySQL

• MySQL is a relational DBMS
• Has many of the same capabilities as traditional
  DBMSs (newest releases)
• MySQL queries mostly the same as SQL in Oracle
  (subsidiary of Sun)
• Popular for web databases
• Its freeware!

13

• You can connect to MySQL directly
• OR
• You can connect to MySQL through .php

14
MySQL commands

• Can connect directly to MySQL
• mysqlgt SHOW databases
• mysqlgt USE db_name // must specify this each
  time
• mysqlgt SHOW tables
• mysqlgt DESCRIBE table_name
• mysqlgt create table
• mysqlgt insert into table values (
• mysqlgt select from table_name
• mysqlgt delete
• mysqlgt update

15
MySQL commands

• mysqlgt LOAD DATA LOCAL INFILE file_name INTO
  TABLE table_name
• mysqlgt file_name (containing a query)

16

• You can connect to MySQL directly
• OR
• You can connect to MySQL through .php

17
Some php mysql functions

• Connecting to MySQL through PHP
• Mysql_connect (localhost, login, password)
• Mysql_select_db (db_name, link_id)
• mysql_query (string , resource link_id)
• Executes a query, place result in variable, like
  a cursor
• Resource specifies a connection, otherwise last
  connection opened used
• mysql_error ( )
• Returns error message from previous sql operation
• mysql_fetch_array (result, how)
• Traverses through cursor of query result
• How is either mysql_assoc (use col. names) or
  mysql_num (use index number) or mysql_both
• Mysql_num_fields ( result)
• Returns number of columns in table (fields in
  recordset)

18
PHP and MySQL example

• lt?php
• linkmysql_connect ("localhost", "vrbsky",
  password")
• mysql_select_db('vrbsky') or die('Cannot select
  database')
• query 'CREATE TABLE contact( '.
• 'cid INT NOT NULL, '.
• 'cname VARCHAR(20) NOT NULL, '.
• 'cemail VARCHAR(50) NOT NULL, '.
• 'csubject VARCHAR(30) NOT NULL, '.
• 'constraint pk PRIMARY KEY (cid) )'
• result mysql_query(query, link)
• if(!result) die( 'Error in SQL ' .
  mysql_error())
• mysql_close(link)
• ?gt

19
Example

• lt?php
• echo "Welcome to Vrbsky's DB"
• // Connect to MySQL
• link mysql_connect("localhost", "vrbsky",
  password")
• if (!link) die('Not connected '.
  mysql_error()) // see if connected
• // Select DB will use
• mysql_select_db('vrbsky') or die ('Could not
  select database') // see if worked
• // Now the query
• query "Select from testit" // testit
  has 2 columns, id and age
• result mysql_query(query, link)
• if (!result) die( 'Error in SQL ' .
  mysql_error())
• // process results using cursor
• while (row mysql_fetch_array(result))
• echo "lthrgt" //horizontal line
• echo "id ". row"id" . "ltbr /gt"

20
Accessing result rows

• lt?php
• linkmysql_connect ("localhost", "vrbsky",
  password")
• mysql_select_db('vrbsky') or die('Cannot select
  database')
• query "SELECT ssn, lname FROM employee"
• result mysql_query(query, link)
• //Using column name
• while(row mysql_fetch_array(result,
  MYSQL_ASSOC))
• echo "SSN row'ssn' ltbrgt" .
• "Last row'lname' ltbrgt ltbrgt"
• // Using an index
• // while(row mysql_fetch_array(result,
  MYSQL_NUM))
• //
• // echo "SSN row0 ltbrgt" .
• //  "Last row1 ltbrgtltbrgt"
• //
• mysql_close(link)
• ?gt

21
Forms and input

• Can use HTML to create forms
• Users can input values to use as host variables
  in calls to mysql

22
Html code

• The following code uses a form to ask for input
  values to a table
• It will execute a php file after values are input
  in the form
• To use those values in php file, must use
  _POSTvar_name

23
Example html and php

• lthtmlgt
• ltbodygt
• ltcentergt
• ltform method"post" action"example.php"gt
• lt!-- places values in associative array called
  _POST --gt
• ltfont size"18"gtSelect Statementlt/fontgtltbrgtSelect
  
• ltinput type"text" name"select" size"60"
  value" from project"gtltbrgt
• ltinput type"hidden" name"_query_form2"
  value"1"gt
• ltinput type"submit nvalue"Get Query"gtlt/formgt
• lttable border"1"gt
• //You could put the code for example.php here
  instead of in a separate file
• lt/tablegtlt/centergtlt/bodygtlt/htmlgt

24
PHP code

• PHP code places values input from form into local
  variables
• Connects to database
• Inserts values into tables
• Prints out values

25

• ltform method"post" action"example.php"gt
• The submission method for a form is post -
  variables from the form will be placed into an
  array _POST
• Post method then executes the code in example.php

26
PHP code segment

• lt?php //This is example.php referenced in
  previous .html code
• link mysql_connect("localhost",
  "vrbsky", password")
• if(!link) die('Not connected
  '.mysql_error)
• mysql_select_db('vrbsky')
• // isset tests if the value of the variable is
  set
• if(isset(_POST'_query_form2')
  isset(_POST'select'))
• select "select
  "._POST'select'
• result mysql_query(select,
  link)
• if(!result) echo
  mysql_error()
• else
• while(row
  mysql_fetch_array(result, MYSQL_NUM))
• echo "lthrgt"
  // horizontal line
• echo "lttrgt"
• for(count
  0 count lt 10 count)
• 
  if(isset(rowcount)) echo "
  lttdgtrowcountlt/tdgt"
• 
  if(!isset(rowcount) isset(rowcount))
  
• echo
  "lttdgtlt/tdgt" count--

27
Our Setup

• This this link to our machine
•  lamp.cs.ua.edu 130.160.47.52

28
Our setup

• A machine for us to use PHP and MySQL
• address of machine is lamp.cs.ua.edu
  130.160.47.52
• This is a linux machine
• Emacs, vi (I havent used this since the 80s)
• username is 1st name initial followed by last
  name with a password of CWID
• Ex. John Doe username is jdoe
• You need to use SSH Secure Shell to directly
  Quick Connect to this machine

29
Our setup

• Use vi (or whatever) to create new PHP and HTML
  files
• OR you can just edit files locally then use SSH
  file transfer to this machine

30
Our Setup

• To run MySQL directly
• To start up MySQL type in
• mysql u your_login p
• It will then prompt you for your password
• You must create a db created with the same name
  as your login using
• create database your_login
• The next time you start up MySQL type in
• mysql u your_login D your_login p
• where D is you database

31
Our setup

• To use MySQL through PHP
• Create/save a .php file using an editor
• Make sure the file is on the lamp.cs.ua.edu
  machine in the root directory
• Sample program
• lt?php
• Echo Hello World
• ?gt
• To run it, from IE, type in
• ip address/yourlogin/filename
• 130.160.47.52/vrbsky/Hello.php or
• lamp.cs.ua.edu/vrbsky/Hello.php

32

• Wont this be fun for an assignment?
• Lots of great links on the web to get into
• Disadvantage How to determine what is error?

33

• lthtmlgt
• ltheadgt
• lt/headgt
• ltcentergt
• lt!-- The following line results in php code
  executed after input values in form -gt
• ltform method"post" action"example3.php"gt
• lttablegt
• lttrgtlttd align"left"gtDnameslt/tdgt
• lttdgtltinput type"text" name"id"gtlt/tdgt
• lt/trgt
• lttrgtlttd align"left"gtLnamelt/tdgt
• lttdgtltinput type"text" name"age" size"15"gtlt/tdgt
• lt/trgt
• lttrgtltcolspan"2"gt
• ltp align"center"gt
• ltinput type"submit" value"Enter record"gt
• lt/tdgt
• lt/trgt
• lt/tablegt

34

• lt?php
• // This is example3.php used in previous .htm
  code
• link mysql_connect("localhost", "vrbsky",
  "NewPass")
• if (!link) die('Not connected '.
  mysql_error())
• mysql_select_db('vrbsky') or die ('Could not
  select database')
• dname _POST'id'
• lname _POST'age'
• query "insert into testit values ('dname',
  'lname')"
• result mysql_query(query)
• if (!result) die('SQL error ' .
  mysql_error())
• mysql_close(link)
• print "lthtmlgtltbodygtltcentergt"
• print "ltpgtYou have just entered this recordltpgt"
• print "Dname dnameltbrgt"
• print "Lname lname"
• print "lt/bodygtlt/htmlgt"
• ?gt