Dr. Natalio Krasnogor

1
G6DHLL High-Level Languages Lecture 9

• Dr. Natalio Krasnogor
• Natalio.Krasnogor_at_Nottingham.ac.uk

2
Last Lecture

• We looked at PHP that is actually useful
• The Danger of Computer Science
• Whats wrong with Cookies?
• The Sessions solution

3
In this lecture

• Relational Data Bases
• mySQL
• Analysing the mySQL data

4
A Web Application

• The diagram below shows the relationship between
  your user, the scripting language and the DB.

Web Application
PHP
Database
5
PHP MySQL

• Open source has brought a lot more than Linux to
  the computing world. It has also given us PHP and
  MySQL.
• PHP and MySQL are viewed by many as the world's
  best combination for creating data-driven sites.
• MySQL databases are ideal for storing that data
  we have collected about a user or for holding
  user preferences between visits. It is free and
  it is easy.

6
MySQL

• MySQL central is http//www.mysql.com/.
• Were not going to go through installation of
  mysql. PHPDEV4 is the way to go if you want to
  setup at home
• http//www.firepages.com.au/phpdev4.htm
• However the university offers mysql accounts
  this is what well be using and instruction are
  in the labs exercise sheets and TSG help
  instructions.

7
Relational Databases
8
2. MySQL Refresher

• The next few slides will refresh how to get mySQL
  working. First here is a function that automates
  connecting to a certain database
• function db_connect()
• result mysql_connect("localhost", nxk",
  pass")
• if (!result) return false
• if (!mysql_select_db("supermodels")) return
  false
• return result

9
The Query

• The Query is the basic method by which data is
  entered or extracted from a database.
• It is common to all database systems.
• It is simply a command to the MySQL database in
  order to tell it to do something.

10
SQL Commands

• SQL is a reasonably powerful query language.
• However it is incredibly simple. You can learn it
  in a night.
• The fundamental SQL commands are
• CREATE
• SELECT
• INSERT
• DELETE
• UPDATE

11
Creating a Table

• CREATE TABLE people (
• first varchar(30),
• last varchar(30),
• address varchar(255)
• )
• INSERT INTO people VALUES
• (Adolf',Hitler',Hell')
• INSERT INTO people VALUES
• (Osam', bin Laden,missing)

12
First MySql/PHP Program

• lt?
• db mysql_connect("localhost", "root")
  mysql_select_db("mydb",db)
• result mysql_query("SELECT FROM
  employees")
• firstname mysql_result(result,0,"first")
• lastname mysql_result(result,0,last")
• address mysql_result(result,0,address")
• ?gt
• Hello lt?firstname?gt lt?lastname?gt ltBRgt
• Your address is lt?address?gt

13
mysql_connect()

• mysql_connect() establishes a connection to a
  MySQL server.
• It takes 3 parameters.
• The address of the server
• Your Username for that db account
• Your password
• conn mysql_connect(address", user,
  pass)
• The university mysql server is found at the
  following address
• mysql.cs.nott.ac.uk

14
mysql_select_db()

• In our code mysql_select_db() then tells PHP that
  any queries we make are against the dbname
  database.
• mysql_select_db(dbname",conn)
• We could create multiple connections to
  databases on different servers. But for now,
  youll only need one database.

15
mysql_query()

• Next, mysql_query() does all the hard work.
• Using the database connection identifier, it
  sends a line of SQL to the MySQL server to be
  processed.
• This is the key command for interacting with the
  database.
• In our example the results that are returned are
  stored in the variable result.

16
mysql_result()

• Finally, mysql_result() is used to display the
  values of fields from our query
• mysql_result(result,0,"first")
• Using result, we go to the first row, which is
  numbered 0, and return the value of the specified
  fields.

17
Its as easy as that

• So there we have it.
• We have successfully executed a simple PHP script
  to retrieve some information.
• We will be extending this to produce some more
  powerful PHP scripts.

18
Making a query

• Now we have connected by calling this function,
  we can access the database to make a query to it.
• Remember, to send a mySQL instruction to the
  database we use mysql_query()
• You can make absolutely any query that you would
  type into the database command line via PHP in
  this way.

19
Unpolitically Correct Create Example

• For example, to create a table from our PHP code
  you might type
• mysql_query(CREATE TABLE supermodel (
• name varchar(30),
• age integer))
• Remember that this is something that you would
  only want to do once once the table is created
  we dont want to wipe it by accident

20
MySQL Insert Example

• Equally we can populate our tables with INSERT
  statements via mysql_query()
• mysql_query(INSERT INTO supermodels VALUES
  (Claudia Schiffer',32))
• mysql_query(INSERT INTO supermodels VALUES
• (Elle McPherson',28))
• mysql_query(INSERT INTO supermodels VALUES
• (Claire Shore,2015))
• These are hard coded examples but we could be
  using variables in these statements

21
Mysql Select Example

• We use a SELECT statement to grab data from a
  certain table and then put the result into a
  variable ready to analyse
• result mysql_query(SELECT FROM supermodels
  WHERE agelt35)
• However now result has all the info we want
  inside it how are we going to extract it in the
  form we want?

22
3. Extracting the data

• We mentioned in the last lecture one way of
  extracting the data we have stored as a result
• name mysql_result(result,0,"first")
• age mysql_result(result,0, "age")
• Simple as this method is, it is rather clumsy
  if we have a lot of records returned (in this
  case supermodels), we would have to use a lot of
  these statements.
• The next 2 slides illustrate a more effective way
  of grabbing that information from the result
  object.

23
mysql_fetch_row()

• This function gets a result row as an enumerated
  array.
• subsequent calls to mysql_fetch_row() would
  return the next row in the result set, or FALSE
  if there are no more rows
• lt?
• mysql_connect(mysql_address", "mysql_user",
  "mysql_pass")
• mysql_select_db(dbname")
• result mysql_query("SELECT name, age FROM
  supermodels")
• while (supermodel mysql_fetch_row(result))
  
• print Supermodel supermodelname is
• print supermodelage years old
• ?gt

24
mysql_fetch_array()

• This function fetches a result row as an
  associative array, a numeric array, or both, from
  the result of a mysqw query
• mysql_fetch_array() is an extended version of
  mysql_fetch_row(). In addition to storing the
  data in the numeric indices of the result array,
  it also stores the data in associative indices,
  using the field names as keys.
• Which you use is up to you. Both functions are
  pretty similar.

25
mysql_num_rows()

• mysql_num_rows() returns the number of rows in a
  result set. This command is only valid for SELECT
  statements.
• mysql_query(SELECT FROM supermodels WHERE
  agelt35)
• print mysql_num_rows().models are younger than
  35"
• Its a great function for when you need to loop
  round all the results in your query, or just to
  know how many matches you got

26
mysql_rows_affected()

• mysql_affected_rows() returns the number of rows
  affected by the last INSERT, UPDATE or DELETE
  query associated with. For example
• mysql_query("DELETE FROM mytable WHERE id lt 10")
  
• print "Records deleted ".mysql_affected_rows()."lt
  BRgt"
• N.b. this function does not work with SELECT
  statements - only on statements which modify
  records.

27
Mysql Summary

• There are a whole host of PHP mySQL functions but
  you can easily get by using only the ones that
  weve mentioned. View them at www.php.net.
• It is incredibly useful.

28
Thats All Folks