Week 18 Arrays, Databases, and PHP

1
Week 18 Arrays, Databases, and PHP
2
Objectives

• By the end of this session, you should have a
  basic understanding of
• Arrays, what they are and how they work
• Databases what they are and how they work
• SQL what it is, and how it works
• How to access and manipulate data in a MySQL
  database using PHP

3
Arrays 1

• A variable is a box in the computers memory
• It has a name, and it holds a value
• The value held is of a specific data type
• String, Integer, Date, etc.
• In this example
• animal is the variables name
• CAT is the value in the variable
• As the variable is prefixed with a symbol, we
  infer that the variables type is string

4
Arrays 2

• An array is like a cabinet of boxes
• It also has a name
• It has a number of elements
• Each element can contain a separate value
• All values held must be of the same data type

5
Arrays 3

• Each element has a reference so that it can be
  accessed independently
• Example - animal is NOW an array of string data
  type
• animal0Cat
• animal1Dog
• animal2Fish
• print animal0

6
Arrays 4

• One of the key usage methods with arrays is the
  loop
• Using our example of animal array
• animal0Cat animal1Dog
  animal2Fish
• While (animalposition)
• print (The animal in position position is
  .animalposition. ltBRgt)
• position

7
Arrays 5

• Or a For Loop
• animal0Cat animal1Dog
  animal2Fish
• For (position0 positionlt3 position)
• print (The animal is position position is
  .animalposition. ltBRgt)

8
Arrays 6

• Arrays can
• be of
• multiple
• dimensions

9
Arrays 6

• Arrays can be of multiple dimensions
• chess00Rook chess01Knight

10
Databases 1

• What is a Database?
• Database components
• Server
• Databases
• Tables
• Records / Rows
• Fields

11
Databases 2

• One Server Many Databases
• One Database for One Purpose
• For a specific purpose, or for a specific person
• webstuff, in6480
• One Database Many Tables
• One Table for One Topic
• Holding a specific type of information
• i.e. student records, orders, etc

12
Databases 3

• One Table Many Records
• Each Record/Row is about one specific unit
• A student, an order, a module
• One Record Many Fields
• One field is one variable
• Holding a specific data type string, integer,
  date, etc.
• i.e. name, order no., module code, etc.

13
Databases 4

• Recap
• One Database Server Many Databases
• One Database Many Tables
• One Table Many Records/Rows
• One Record Many Fields

14
Databases 5
15
SQL 1

• In order to retrieve and manipulate data from a
  database, instructions must be sent to the
  database server
• Each command is called a query
• The queries are written in a language that the
  server can understand SQL
• SQL statements can take many forms
• Example
• Db in6480
• Table animal
• Fields name, species, age, owner

16
SQL 2

• To retrieve, or read, information from a database
  we use a SELECT statement
• To read just the name of every animal
• SELECT name FROM animal
• To read all the information of every animal
• SELECT name, species, age, owner FROM animal
• or
• SELECT FROM animal

17
SQL 3

• To add information to a database is known as
  INSERT ing
• To INSERT a record/row into our animal table,
  we use the following statement
• INSERT INTO animal VALUES
• (Flopsy, Rabbit, 2, Miss. H. Riordan)

18
SQL 4

• To amend a record, we use the UPDATE statement
• The UPDATE statement must be used very careful,
  as it is possible to amend many records at once
• UPDATE animal SET age3
• To specify a particular record, we use a WHERE
  clause
• UPDATE animal SET age3 WHERE nameFlopsy

19
SQL 5

• To remove a record, we use the DELETE statement
• The DELETE statement must be used very, very
  careful, as it is possible to delete many records
  at once, and there is no UNDO
• DELETE FROM animal
• To specify a particular record, or records, we
  use a WHERE clause
• DELETE FROM animal WHERE nameFlopsy

20
PHP Databases 1

• To execute queries on a database is made up of 2
  parts
• Send the Query to the Database
• Handle the response
• Firstly, to connect to a database we use the
  following
• servermysql_connect (clun.scit.wlv.ac.uk,demo
  ,)
• server is a variable we use to hold the
  connection to the database server. Its use will
  become clearer in the next few slides.
• mysql_connect is the command to initiate a
  connection
• The parameters passed to the connect statement
  are
• 1. The Server Address, 2. A Username, 3. A
  Password
• (in this instance, there is no password for the
  user demo)

21
PHP Databases 2

• To check to make sure the connection is OK, you
  can use the following
• if (!server)
• print (failed to connect to server)
• exit
• endif
• Next, we pick the database we want to use
• mysql_select_db(in6480,server)
• (where in6480 is the name of the database)

22
PHP Databases 3

• To execute a statement, first we have to build
  the statement. This is best accomplish using a
  string variable.
• i.e.
• sql SELECT name, species, age FROM animal
• And as we said at the beginning we need to handle
  the response from the database, for that we will
  use the variable result

23
PHP Databases 4

• To execute the statement we use the following
• result mysql_query(sql)
• We can test for success by using the following
• if (mysql_error())
• print (The has been an errorltBRgt.mysql_error())
  
• exit
• endif
• We can check to see how many rows have been
  returned using
• print mysql_num_rows(result)
• (note this only works for SELECT queries)

24
PHP Databases 5

• We can now manipulate the contents of the
  result. To convert the first record into an
  array we use the following statement
• rowmysql_fetch_array(result)
• row is now an array containing the first record
  returned
• As we have SELECTed three fields (name, species,
  age) from the table, there will be three elements
  created in our array. The mysql_fetch_array
  statement does this.
• To print out the first element (name) we can use
• print row0
• To print out the second, print row1 and so on.

25
PHP Databases 6

• The mysql_fetch_array statement also names the
  elements in the array as well.
• row0 is the same as rowname
• row1 is the same as rowspecies
• row2 is the same as rowage
• It is easier to understand your code if you use
  the named elements, rather than the positions,
  but both work exactly the same way.

26
PHP Databases 7

• servermysql_connect(clun.scit.wlv.ac.uk,demo
  ,)
• if (!server)
• print (error connecting to server)
• exit
• endif
• mysql_select_db(in6480,server)
• sqlSELECT name, species, age FROM animal
• resultmysql_query(sql)
• if (mysql_error())
• print (there has been an errorltBRgt.mysql_error(
  ))
• exit
• endif
• print There have been .mysql_num_rows(result).
  records returnedltBRgt
• rowmysql_fetch_array(result)
• print Name is .rowname. and species is
  .rowspecies. and age is .rowage

27
PHP Databases 8

• The previous example only showed the first row.
• In order to show all rows, we need a loop
• The best loop to use with SELECT queries is as
  follows
• While (rowmysql_fetch_array(result))
• print rowname. - .rowspecies .
  .rowage.ltBRgt
• Notice the table formatting statements.
• Try to change the code and see the results.

28
PHP Databases 9

• Recap

29
PHP Databases 10
30
PHP Databases 11

• To insert some information into the database,
  first we have to collect it.
• We do this by firstly creating a HTML form to
  capture the users input.

31
PHP Databases 12

• ltHTMLgt
• ltFORM actioninsert.phpgt
• ltinput typetext nameaninamegt
• ltBRgt
• ltinput typetext nameanispeciesgt
• ltBRgt
• ltinput typetext nameaniagegt
• ltBRgt
• ltinput typetext nameaniownergt
• ltBRgt
• ltinput typesubmitgt
• lt/FORMgt
• lt/HTMLgt

32
PHP Databases 12

• Now, we create the PHP file that the HTML sends
  its information to
• servermysql_connect(clun.scit.wlv.ac.uk,demo
  ,)
• mysql_select_db(in6480, server)
• sqlINSERT INTO animal VALUES (\aniname\,
  \anispecies\, \aniage\, \aniowner\)
• mysql_query(sql)
• Print (inserted!)

33
PHP Databases 13
34
Summary

• By the end of this session, you should have a
  basic understanding of
• Arrays, what they are and how they work
• Databases what they are and how they work
• SQL what it is, and how it works
• How to access and manipulate data in a MySQL
  database using PHP

Questions?