Intro to LAMP Programming

1
Intro to LAMP Programming

• Presented for
• SAT Linux Users' Group
• by
• Dan Zollars

2
What is LAMP?

• LinuxApacheMySQLPHP
• Number one HTTP server on the Internet
• Most popular open-source database
• Widely used, general purpose scripting language

3
Getting Started

• Website resources see http//cis.sac.accd.edu/d
  zollars, then LAMP demo
• Need text editor and browser programs are
  already set up
• Root access
• Apache document root /var/www/htdocs
• Client Server model

4
Topics

• Testing Apache PHP
• Integrating PHP and HTML
• Targeting a PHP script from an HTML form
• Retrieving information from MySQL databases
• Accessing MySQL databases from PHP
• Practice

5
Testing Apache PHP

• In browser http//localhost
• Any document in document root is available for
  Apache to serve to client
• /var/www/htdocs/sample.html same as
  http//localhost/sample.html
• Using Minimal XHTML document
• Testing PHP Example 1

6
Integrating PHP HTML

• ltbodygtlt?php x 1 for (i 0 i lt 10
  i) echo ltpgt\i ilt/pgt\n // more php
  code?gtlt/bodygt
• Example 2

7
Targeting a PHP script

• ltform actiontarget.php methodpostgt ltinput
  typetext namefield_name /gt ltinput
  typesubmit /gtlt/formgt
• Now in target.php field_name
  _POST'field_name'
• Example 3

8
Practice - 1

• Write the target script for example3.php

9
Practice - 1 (answers)

• lt?php lastname _POST'lastname'
  echo "ltpgtThe name you entered was
  lastnamelt/pgt\n"?gt

10
Retrieving MySQL Information

• SELECT ltcolumn_listgt
• FROM lttable1gt , lttable2gt ...
• WHERE ltconditiongt
• ORDER BY ltordergt DESC
• GROUP BY ltgroup_conditiongt

11
SELECT Clause

• Use the SELECT clause to restrict which columns
  to display
• SELECT firstname, lastname, email
• SELECT qty, item_desc
• SELECT

12
FROM Clause

• Use the FROM clause to specify which table(s) to
  retrieve the data from
• SELECT firstname, lastname, emailFROM customers
• SELECT FROM orders

13
WHERE Clause

• Use the WHERE clause to restrict the number of
  rows to display
• SELECT qty, item_descFROM itemsWHERE qty gt 1
• SELECT FROM ordersWHERE paid IS NULL

14
JOINS

• Several kinds
• Common column
• Can use either the FROM or WHERE clause

15
JOIN - WHERE

• Uses the WHERE clause to specify join condition
• SELECT order_id, order_date, lastnameFROM
  orders, customersWHERE orders.cust_id
  customers.cust_id
• SELECT qty, item_descFROM items, ordersWHERE
  items.order_id orders.order_idAND
  items.order_id 1002

16
Miscellaneous MySQL

• mysql -p
• show databases
• use ltdatabasegt
• show tables
• describe lttablegt
• grant all on testing. to fred_at_localhost
  identified by YabbaDabbaDo
• revoke ltprivilegegt on testing. from user_at_

17
Security in MySQL

• Daemon/client architecture
• Run daemon as mysql user
• Require passwords
• USE mysql
• SELECT host, user, passwordFROM user
• Set up non-root user for specific databases

18
Practice - 2

• Use the satlug database to find out the
  following
• Names and addresses of all the customers
• How many orders for each customer (just list them
  and count)?
• List the unfinished orders (completed IS NULL)
• List the orders that have been shipped but
  haven't been paid for yet
• How many carrots did Bugs Bunny order (join items
  to orders where cust_id 4)?

19
Practice - 2 (answers)

• SELECT firstname, lastname, address, city, state
  FROM customers
• SELECT FROM orders
• SELECT FROM orders WHERE completed IS NULL

20
Practice - 2 (answers)

• SELECT FROM ordersWHERE completed IS NOT
  NULLAND paid IS NULL
• SELECT qty, item_descFROM items, ordersWHERE
  items.order_id orders.order_idAND
  orders.cust_id 4

21
Accessing MySQL from PHP

• link mysql_connect(host, name, pw)
• mysql_select_db(satlug, link)
• result mysql_query(query)
• while (row mysql_fetch_array(result)) echo
  row0 row1 \n // etc.
• die(Error message . mysql_error())
• Example 4

22
Practice - 3

• Modify example 3 source and target as follows
• Client enters last name, target displays first
  and last names
• Client enters cust_id, target displays order id
  and order date for all orders
• Client enters order_id, target displays qty and
  description

23
Using _GET

• In source file, create a link with parameter
• lta href'target.php?idid'gtTextlt/agt
• In target file, use _GET superglobal to get info
• id _GET'id'
• Creates different html for each table entry
• Still only two files

24
Practice - 4

• Modify practice 3 source and target as follows
• Source looks up customer names, presents as links
  to target using HTTP parameter (display name, use
  id as parameter)
• Target uses _GET to determine cust_id, then
  looks up other customer information
• Target displays information

25
PHP Review

• What's wrong with this
• echo "ltpgtZollars' real name is "dufus"lt/pgt"
• query SELECT fname, lname . FROM
  customers . WHERE department Admin
• query SELECT qty, desc . FROM inventory
  . WHERE partno LIKE 'L'

26
PHP Odds and Ends

• PHP Provides lots of useful things
• include() or include_once()
• include_once(connect.php)
• include(header.php)
• foreach (a as x)
• Associative arrays
• state'TX' Texas
• foreach (state as keygtname) echo ltpgtkey is
  the state of namelt/pgt