LIS651 lecture 3

1
LIS651 lecture 3

• Thomas Krichel
• 2005-04-08

2
today

• some more SQL
• interface with mySQL from PHP

3
USE

• USE database tells mySQL to start working with
  the database database.
• If you have not issued a USE command, you can
  still address a table table by using
  database.table, i.e. using the dot to link the
  two together.

4
addressing database tables columns

• Let there by a database database with a table
  table and some column column. Then it is
  addressed as database.table.column.
• Parts of this notation can be left out if it is
  clear what is meant, for example if you have
  issued USE database before, you can leave out the
  database part.

5
INSERT

• INSERT inserts values. In its simples form
• INSERT INTO table VALUES (value1, value2, ..)
• Example
• INSERT INTO products VALUES ('','Neufang
  Pils',1.23)
• Note that in the example, I insert the null
  string in the first column because it is an
  auto_increment.

6
partial INSERT

• If you are only giving a part of a record, or if
  you want to enter them in a different order you
  will have to give a list of column names.
• INSERT INTO products (name,id) VALUES ('Neufang
  Pils','')

7
SELECT

• This is the SQL statement to select rows from a
  table. Here is the full syntax
• SELECT options columns INTO file_details
• FROM table WHERE conditions
• GROUP BY group_type
• HAVING where_definitions
• ORDER BY order_type LIMIT limit_criteria
• PROCEDURE proc_name(arguments)
• lock_options

8
columns to SELECT

• You can have a comma-separated list of columns
• SELECT name, price FROM products
• You can use the star to get all columns
• SELECT FROM products

9
WHERE condition to SELECT

• means equality
• WHERE id 3
• gt, lt, gt, lt and ! also work as expected
• IS NULL tests if the value is null
• IS NOT NULL
• IN allows you to give a set
• WHERE state IN ("NY","NJ","CT")

10
SELECT using multiple tables

• table1,table2 can be used to join both tables to
  build a big table that can be searched
• SELECT orders.id FROM customers, orders
• WHERE customers.id 3
• This type of join is a Cartesian product aka a
  full join For each row of the first table, it
  adds rows from the second table.

11
complicated queries

• who ordered Bruch Landbock?
• SELECT customer.id from customers, orders,
• orders_items, products WHERE
• customers.idorders.customer_id AND
• orders.idorders_items.order_id AND
• orders_items.item_idproducts_id AND
• products.name'Bruch Landbock'

12
left join

• Another way to join tables is to join them "on"
  some column.
• SELECT customers.name FROM customers LEFT JOIN
• orders ON customers.id orders.customerid AND
  orders.id
• IS NULL
• The joint table is filled with NULL for those
  costumers who have not placed an order yet. It is
  also known as a left outer join.

13
table example

• Table A Table B
• A1 A2 B1 B2 B3
• 1 4 2 3 4
• 4 5 6 7 3
• 6 3 1 1 4
• Left outer join by A2 and B3 is
• A1 A2 B1 B2 B3
• 1 4 2 3 4
• 1 4 1 1 4
• 4 5
• 6 3 6 7 3

14
aliases

• You can use AS to create aliases. If you want to
  find out which customers live in the same city as
  another customer
• select c1.name, c2.name, c1.city
• FROM customers AS c1, customers AS c2
• WHERE c1.city c2.city AND c1.name ! c2.name

15
ORDER

• You can order by a field by saying ORDER BY.
• You can add ASC or DESC to achieve ascending or
  descending order.
• SELECT name, address FROM customers ORDER BY name
  ASC

16
column functions

• AVG(column) give average of the column
• COUNT(column) gives you a count of non NULL
  values
• COUNT( DISTINCT column) gives a count of distinct
  values
• MIN(column), MAX(column)
• STD(column) gives the standard deviation
• SUM(column) gives the sum of the items

17
column functions and grouping

• You can use the function on the columns
• SELECT AVG(amount) FROM orders
• You can group the selection
• SELECT MIN(amount) FROM orders
• GROUP BY customerid
• You can use them in conditions with HAVING, such
  as
• SELECT customerid FROM orders
• HAVING AVG(amount) gt 10

18
LIMIT

• This can be used to limit the amount of rows.
• LIMIT 10 19
• This is useful it web sites where you show a
  selection of the results.
• This ends the discussion of the SELECT command.

19
UPDATE

• The general syntax is UPDATE LOW_PRIORITY
  IGNORE table SET column1expession1,
  column2expression2... WHERE condition ORDER
  BY order_criteria LIMIT number. An example is
• UPDATE students SET email 'phpguru_at_gmail.com'
• WHERE name'Janice Insinga'
• IGNORE instructs to ignore errors.
• LOW_PRIORITY instructs to delay if the server is
  busy.

20
DELETE

• The general syntax is DELETE LOW_PRIORITY
  QUICK IGNORE FROM table SET WHERE condition
  ORDER BY order_criteria LIMIT number
• Bad example
• DELETE FROM customers
• Good example
• DELETE form customers WHERE
• customer.name'Thomas Krichel'

21
mySQL PHP function reference

• affected_rows -- Get number of affected rows in
  previous MySQL operation
• change_user -- Change logged in user of the
  active connection
• client_encoding -- Returns the name of the
  character set
• close -- Close MySQL connection
• connect -- Open a connection to a MySQL Server
• create_db -- Create a MySQL database
• data_seek -- Move internal result pointer
• db_name -- Get result data
• db_query -- Send a MySQL query
• drop_db -- Drop (delete) a MySQL database

22
mySQL PHP function reference

• errno -- Returns the numerical value of the error
  message from previous MySQL operation
• error -- Returns the text of the error message
  from previous MySQL operation
• escape_string -- Escapes a string for use in a
  mysql_query
• fetch_array -- Fetch a result row as an
  associative array, a numeric array, or both
• fetch_assoc -- Fetch a result row as an
  associative array
• fetch_field -- Get column information from a
  result and return as an object
• fetch_lengths -- Get the length of each output in
  a result
• fetch_object -- Fetch a result row as an object
• fetch_row -- Get a result row as an enumerated
  array

23
mySQL PHP function reference

• field_flags -- Get the flags associated with the
  specified field in a result
• field_len -- Returns the length of the specified
  field
• field_name -- Get the name of the specified field
  in a result
• field_seek -- Set result pointer to a specified
  field offset
• field_table -- Get name of the table the
  specified field is in
• field_type -- Get the type of the specified field
  in a result
• free_result -- Free result memory
• get_client_info -- Get MySQL client info
• get_host_info -- Get MySQL host info
• get_proto_info -- Get MySQL protocol info

24
mySQL PHP function reference

• get_server_info -- Get MySQL server info
• info -- Get information about the most recent
  query
• insert_id -- Get the ID generated from the
  previous INSERT operation
• list_dbs -- List databases available on a MySQL
  server
• list_fields -- List MySQL table fields
• list_processes -- List MySQL processes
• list_tables -- List tables in a MySQL database
• num_fields -- Get number of fields in result
• num_rows -- Get number of rows in result
• pconnect -- Open a persistent connection to a
  MySQL server

25
mysql_connect()

• This is used to establish a connection to the
  mySQL server. It is of the form
  mysql_connect('host', 'user', 'password')
• Example
• link mysql_connect('localhost','boozer',
• 'heineken')
• The function returns a variable of type
  resource. If there is a mistake, it returns
  false.

26
mysql_error()

• This shows the error from the last mySQL command.
• errormysql_error()
• if(error)
• print "mySQL error errorltbr/gt"
• The value returned from that function is a simple
  string.
• It is a good idea to check out error messages.

27
mysql_select_db()

• This command has the syntax mysql_select_db('datab
  ase') where database is the name of a database.
• This tells mySQL that you now want to use the
  database database.
• mysql_select_db('beer_shop')
• It has the same effect as issuing
• USE beer_shop
• within mySQL.

28
mysql_query()

• mysql_query(query) send the query query to the
  connection identified by link. Usually link in
  left blank and the query goes to the most
  recently opened connection.
• query"SELECT FROM
• beer_shop.customers"
• resultmysql_query(query)
• Note that the query itself does not require a
  terminating semi-colon.

29
result of mysql_query()

• For SELECT, SHOW, DESCRIBE or EXPLAIN mySQL
  queries, mysql_query() return a resource that can
  be further examined with mysql_fetch_array().
• For UPDATE, DELETE, DROP and others,
  mysql_query() returns a Boolean value.

30
mysql_fetch_array()

• mysql_fetch_array(resource) return an array that
  is the result row for the resource resource
  representing the most recent, or NULL if it the
  last result is reached. Its results in an array
  that contains the columns requested both by
  number and by column name
• while(columnsmysql_fetch_array(result))
• print 'name '.columns'name'
• print 'first column columns0

31
utility function from php.net

• function mysql_fetch_all(query)
• r_at_mysql_query(query)
• if(errmysql_error()) return err
• if(mysql_num_rows(r))
• while(rowmysql_fetch_array(r))
  resultrow
• return result
• // usage
• if(is_array(rowsmysql_fetch_all(query))
• // do something
• else if (! is_null(rows))
• die("Query failed!")

32
mysql_data_seek()

• mysql_data_seek(result, point) sets the array
  that is returned by mysql_fetch_array to a number
  number.
• while(rowmysql_fetch_array(result))
• print 'first column '.row0
• mysql_data_seek(result,0)
• // otherwise the second loop would not work
• while(rowmysql_fetch_array(result))
• print 'first column '.row0

33
mysql_real_escape_string()

• mysql_real_escape_string(string) returns a string
  escaped for the using in mySQL.
• name"John O'Guiness"
• s_namemysql_real_escape_string(name)
• print s_name // prints John O\'Guiness
• Note that this function makes a call to mySQL,
  therefore a connection must be established before
  the function can be used.
• This function guards against SQL injections.

34
mysql_close()

• This command connection. When it is invoked
  without an argument, it closes the current
  connection.
• This is the happiest command there is, because it
  means that we have finished.
• Unfortunately it is not used very often because
  the mySQL connection is closed automatically when
  the script finishes running.

35
mySQL PHP function reference

• ping -- Ping a server connection or reconnect if
  there is no connection
• query -- Send a MySQL query
• real_escape_string -- Escapes special characters
  in a string for use in a SQL statement
• result -- Get result data
• select_db -- Select a MySQL database
• stat -- Get current system status
• tablename -- Get table name of field
• thread_id -- Return the current thread ID
• unbuffered_query -- Send an SQL query to MySQL,
  without fetching and buffering the result rows

36
extra sha1()

• This is a function that calculates a combination
  of 40 characters from a string.
• The result of sha1() can not be translated back
  into the original string.
• This makes it a good way to store password.
• s_passwordsha1(password)

37
login.php create_account.php

• Both require a database that has three fields
• id which is an auto_increment int acting as a
  handle
• username is the username of the account. it must
  be unique and this is enforced by mySQL
• password is a varchar(41) because the sha1 of the
  password is stored. This is 40 chars long.

38
login.php

• function show_form(message)
• print "ltdivgtlth1gtmessagelt/h1gtlth2gtLoginlt/h2gt
• ltform action\"_SERVERPHP_SELF\"
  method\"post\"gt
• ltdivgtltinput type\"hidden\"
  name\"submitted\" value\"1\" /gtlt/divgtltpgtUsername
  ltinput type\"text\"
• name\"username\" maxlength\"15\"
  value\"_POSTusername\" /gtlt/pgtltpgtPassword
  ltinput type\"password\" name\"pass\"
  value\"_POSTpass\"/gtlt/pgtltpgtltinput
  type\"submit\" value\"Login\" /gt Not yet a
  member? lta href\"create_account.php\"gtCreate an
  accountlt/agt!
• lt/pgtlt/formgtlt/divgt"

39

• function process_form()
• usernametrim(_POST'username')
• passtrim(_POST'pass')
• sha_passsha1(pass)
• dbmysql_connect('localhost','krichel','laempel
  ')
• query"SELECT FROM beer_shop.users WHERE
• username'username' AND password
  'sha_pass'"
• resultmysql_query(query)
• errormysql_error()
• if(error) return "Sorry query gives an
  errorltbr/gt
• error"
• affectedmysql_affected_rows()
• if(! (affected)) return "Invalid username or
  password"

40
login.php (end)

• if(_POST'submitted')
• errorprocess_form()
• if(error)
• show_form(error)
• else
• user_POST'username'
• print "lth1gtWelcome to userlt/h1gt"
• else
• show_form('')

41
create_account.php

• function show_form(message)
• print "ltdivgtlth1gtmessagelt/h1gtlth2gtCreate
• Accountlt/h2gtltpgtPlease complete the form below to
  create
• your account. lt/pgt ltform action\"_SERVERPHP_SE
  LF
• \" method\"post\"gtltdivgtltinput type\"hidden\"
• name\"submitted\" value\"1\" /gtlt/divgt It must
  be more
• than 5 characters and cannot be your
  username.lt/pgtltpgt
• ltinput type\"submit\" value\"Create Account\"
  /gt
• lt/pgtlt/formgtlt/divgt"

42
create_account.php

• lth3gtPasswordlt/h3gtltpgt Password ltinput
  type\"password\"
• name\"pass1\" value\"_POSTpass1\"/gtConfirm
• Password ltinput type\"password\" name\"pass2\"
• value\"_POSTpass2\"/gt lt/pgtltpgtThe password you
  enter
• will be used to access your account. It must be
  more than 5
• characters and cannot be your username.lt/pgt
  ltpgtltinput
• type\"submit\" value\"Create Account\"
• /gtlt/pgtlt/formgtlt/divgt"

43
create_account.php

• function process_form()
• usernametrim(_POST'username')
• pass1trim(_POST'pass1')
• pass2trim(_POST'pass2')
• if(strlen(username)lt6)
• return "Username is too short."
• if(! (pass1 pass2))
• return "Passwords do not match."
• passpass1
• if(pass username)
• return "Your username can not be your
  password."

44
create_account.php

• if(strlen(pass)lt6) return "Password is too
  short."
• sha_passsha1(pass)
• dbmysql_connect('localhost','krichel','laempel
  ')
• query"INSERT INTO beer_shop.users VALUES
  ('','username','sha_pass')"
• resultmysql_query(query)
• errormysql_error()
• if(error "Duplicate entry 'username' for
  key 2") return "Sorry Username username is
  already taken, choose another."
• else print "lth1gtThank you for registering with
  us!lt/h1gt"

1
45
create_account.php (end)

• if(_POST'submitted')
• errorprocess_form()
• if(error)
• show_form(error)
• else
• show_form('')

46
http//openlib.org/home/krichel

• Thank you for your attention!