LIS651%20lecture%202%20mySQL%20and%20PHP%20mySQL%20function

1
LIS651 lecture 2mySQL and PHP mySQL function

• Thomas Krichel
• 2006-11-12

2
arrays and tables

• Arrays seem to cause the most confusion in
  student understanding.
• An array is just a way for PHP to address
  multiple things as one variable.
• Arrays can be of multiple dimensions.
• This is typically the case when the array
  represents the contents of a table.

3
example

• Here is an example table
• name type brewer rating price
• Bruch Landbock dark Bruch good 1.18
• Balitka 8 wheat Baltika good 0.88
• Budweiser light A.-B. lousy 0.99
• typically, records appear in lines and fields in
  columns.

4
one way to set out the table

• beers0'name''Bruch Landbock'
• beers0'type''dark'
• beers0'brewer''Bruch'
• .
• beers2'price'0.99
• Here, records are a numeric array. Fields are
  string arrays that are members of the numeric
  array.
• What instruction would improve the rating of
  Budweiser?

5
another way

• beerarray( 'name'gt 'Bruch Landbock',
  type'dark', 'brewer'gt 'Bruch',
  'rating'gt'good', pricegt'1.18')
• beersbeer
• beerarray( 'name'gt'Budweiser', type'light',
  'brewer'gt'A.-B.', 'rating'gt'lousy',
  pricegt0.99)
• beersbeer
• This will give the same array as before.

6
yet another way, as a matrix

• namesarray('Bruch Landbock', 'Baltika
  8','Budweiser')
• typesarray( 'dark', 'wheat', 'light' )
• brewersarray( 'Bruch', 'Baltika', 'A.-B.')
• ratingsarray( 'good', 'good', 'lousy')
• pricesarray(1.18,0.88,0.99)
• beersarray( names, types,brewers,ratings,
  prices)
• What instruction would improve the rating of
  Budweiser?

7
another way to set out the table

• namesarray('Bruch Landbock', 'Baltika
  8','Budweiser')
• typesarray( 'dark', 'wheat', 'light' )
• brewersarray( 'Bruch', 'Baltika', 'A.-B.')
• ratingsarray( 'good', 'good', 'lousy')
• pricesarray(1.18,0.88,0.99)
• beersarray( 'name'gtnames, 'type'gttypes,
  brewergt brewers, 'rating'gtratings,
  'price'gtprices)
• What instruction would improve the rating of
  Budweiser?

8
using mySQL

• mySQL is installed on wotan.
• Normally this involves logging into wotan and
  issuing commands to a character interface.
• The command would be
• mysql -u user -p
• and then you type in your password. If you login
  to wotan you can use it in this way.

9
uppercase and lowercase

• Traditionally SQL commands are written with
  uppercase.
• mySQL commands are really case-insensitive.
• But variable names in the commands are
  case-sensitive. I will therefore write them in
  lowercase.
• Statements in SQL are ended with a semicolon,
  just like PHP statements.

10
creating databases

• CREATE DATABASE a mySQL command to create a new
  database.
• Example
• CREATE DATABASE newbase
• creates a database newbase

11
GRANT

• This is a command to create users and give them
  privileges. A simplified general syntax is
• GRANT privileges ON item TO user_name
• IDENTIFIED BY 'password' WITH GRANT OPTION
• If you use WITH GRANT OPTION, you allow the user
  to grant other users the privileges that you have
  given to her.

12
user privileges I

• SELECT allows users to select (read) records from
  tables. Generally select is a word used for read
  in databases.
• INSERT allows users to insert new rows into
  tables.
• UPDATE allows users to change values in existing
  table rows.
• DELETE allows users to delete table rows
  (records)
• INDEX allows user to index tables

13
user privileges II

• ALTER allows users to change the structure of the
  database.
• adding columns
• renaming columns or tables
• changing the data types of tables
• DROP allows users to delete databases or tables.
  In general, the word drop refers to deleting
  database or tables.

14
user privileges III

• CREATE allows users to create new databases or
  tables. If a specific table or database is
  mentioned in the GRANT statement, users can only
  create that database or table, which will mean
  that they have to drop it first.
• USAGE allows users nothing. This is a useful
  point to start with if you just want to create a
  user.
• ALL means just that.

15
REVOKE

• This is the opposite of GRANT.

16
current setup

• As the super user, I did
• CREATE DATABASE user_name
• GRANT ALL ON user_name TO user_name IDENTIFIED
  BY 'secret_word' WITH GRANT OPTION
• Here
• user_name is your wotan user name
• secret_word is your secret word
• means all rights

17
create a web user

• You do not want to give the same access rights to
  people coming in from the web as you have.
• You do not want to do this. You personally have
  too many privileges.
• I have yet to find out how you can create a web
  user by yourself.

18
creating tables

• This is done conveniently in phpmyadmin.
• Here is an example for real SQL code
• CREATE TABLE customers (customer_id INT UNSIGNED
• NOT NULL AUTO_INCREMENT PRIMARY KEY, name
• CHAR(50) NOT NULL, address CHAR(100) NOT NULL,
  email CHAR(40), state CHAR(2) NOT NULL)

19
column data types

• TINYINT can hold a number between -128 and 127 or
  between 0 to 255. BIT or BOOL are synonyms for
  the TINYINT.
• SMALLINT can hold a number between -32768 and
  32767 or 0 and 65535
• INT can hold a number between -231 and 231-1
  or between 0 and 232-1. INTEGER is a synonym
  for INT.
• BIGINT can hold a number between -263 and
  261-1 or between 0 and 264-1.

20
column data types float

• FLOAT is a floating number on 4 bytes
• DOUBLE is a floating number on 8 bytes
• DECIMAL(x,y) where x is the number of digits
  before the decimal point and y is the number of
  digits after the decimal point.

21
column data types dates

• DATE is a day from 1000-01-01 to 9999-12-31.
• TIME is a time from -8385959 to 8385959
• DATETIME is a date and time, usually displayed as
  YYYY-MM-DD HHMMSS
• TIMESTAMP is the number of seconds since
  1970-01-01 at 0 hours. This number may run out in
  2037.

22
field options

• PRIMARY KEY says that this column is a the
  primary key. There can be only one such column.
  Values in the column must be unique.
• AUTO_INCREMENT can be used on columns that
  contain integer values.
• NOT NULL requires the field not to be empty.

23
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, where datebase is the name of
  your database and table is the name of your
  table. You are using the dot to link the two
  together.

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

25
INSERT

• INSERT inserts new rows into a table. 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.
• Mark Sandford says If you use an auto_increment
  variable, you may as well have it last.

26
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','')

27
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

28
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

29
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")

30
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

31
column functions and grouping

• You can use the function on the columns
• SELECT AVG(amount) FROM orders
• You can group the selection. For example, find
  the minimum for each customer
• 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

32
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

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

34
changing values in a row

• The general syntax is UPDATE LOW_PRIORITY
  IGNORE table SET column1expression1,
  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.

35
deleting rows

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

36
PHP mySQL functions

• We are using here the old version of PHP mySQL
  function, starting with mysql_
• The interface is object-oriented, but can also be
  accessed in a non-object-oriented way. This is
  known as the procedural style, in the
  documentation.
• You should use the online documentation at
  http//php.net/mysql

37
mysql_connect()

• This is used to establish a connection to the
  mySQL server. It is typically of the form
  mysql_connect('host', 'user', 'password')
• Example
• link mysql_connect('localhost','boozer','heineke
  n')
• You can use localhost as the host name for wotan
  talking to itself, but you could also connect to
  other Internet hosts, if you have permission.
• The function returns a variable of type
  resource. If there is a mistake, it returns
  false.

38
mysql_connect_error ()

• This function does not exist. Do not study this
  slide.
• This function returns a string with the last
  connection error.
• link mysql_connect("localhost", "bad_user",
  "")if (!link) print "Can't connect to
  localhost. The error isltbrgt"
• print mysql_connect_error()
• print "ltbr/gt"

39
mysql_error()

• This function return the error from the last
  mySQL command. It returns false if there was no
  error.
• 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.

40
mysql_select_db()

• This command has the syntax mysql_select_db('datab
  ase') where database is the name of a database.
• It returns a Boolean.
• 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.

41
mysql_query()

• mysql_query(query) send the query query to mySQL.
  
• link mysql_connect("localhost", "shop_owner",
  "bruch") // you may then add some connection
  checks
• query"SELECT FROM beer_shop.customers"
• resultmysql_query(query)
• Note that the query itself does not require a
  terminating semicolon.
• The result is in result.

42
result of mysql_query()

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

43
mysql_num_rows()

• This command has the syntax mysql_select_db('resul
  t') where result is the result of a query.
• It returns the number of rows that are in the
  result.
• You could also think of it as the number of
  results.

44
examining resulting rows

• mysql_fetch_array(result) returns an array that
  is the result row for the resource result
  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

45
examining a specific result

• mysql_data_seek(result, number) 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

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

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

48
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)

49
http//openlib.org/home/krichel

• Thank you for your attention!
• Please switch off machines b4 leaving!