PHP%20:%20Working%20with%20Resultsets

1
PHP Working with Resultsets
2
Last class

• Open a connection to the MySQL server.
• Specify the database
• Issue queries (no updates at this stage)
• Display the results in the table form.
• Flush memory
• Close the connection.

3
Todays class

• Learn to create database, tables, attributes
  using mySQL
• Embed PHP type code with HTML type code
• Help and kick start your project in your SAD
  class
• Help and kick start your project in this ? class
• Good Luck coz you need LOTS OF IT!

4
Create Table

• If you need help on data type definition
• http//dev.mysql.com/doc/refman/5.0/en/data-types.
  html

5
Obtaining the number of columns/attributes

• In MySQL you would do the following
• query "SELECT FROM employee"
• result mysql_query(query) // need this to
  get resultSET
• if (mysql_error())
• die("cannot processed select query")
• num mysql_num_rows(result) // obtain the
  number of rows/rec
• numFields mysql_num_fields(result) //obtain
  the number of cols
• To obtain the column/attribute names
• name mysql_field_name(result, i) where i
  is the column number .. going from 0 to
  (numFields 1)

6
Obtaining the number of columns/attributes

• Obtaining the length of an attribute
• attrLen mysql_field_len(result, i) where i
  is the column number .. going from 0 to
  (numFields 1)
• Obtaining the attribute type
• attrType mysql_field_type(result, i) where
  i is the column number .. going from 0 to
  (numFields 1)

7
More function

• Obtain the tables within the database
• tables mysql_list_tables("test_alum")
• numTables mysql_num_rows(tables)
• tableName mysql_tablename(tables, k) will
  give you the table names where k goes from 0 to
  (numTables 1)

8
More function field names

• fields mysql_list_fields("test_alum",
  tableName) // field result
• numfields mysql_num_fields(fields) //get the
  number of fields
• // Do a for loop here to get the attribute name
  for index 0 to (numfields 1)
• for (. )
• fieldInfo mysql_fetch_field(fields)
• echo fieldInfo-gtname
• Note The mysql_fetch_field() command will get
  you the NEXT attribute property.

9
Other characteristics

• echo fieldInfo-gtblob
• echo fieldInfo-gtmax_length
• echo fieldInfo-gtnot_null
• echo fieldInfo-gtnumeric
• echo fieldInfo-gtprimary_key
• echo fieldInfo-gttable
• echo fieldInfo-gttype
• echo fieldInfo-gtunique_key
• echo fieldInfo-gtunsigned
• echo fieldInfo-gtzerofill
• echo fieldInfo-gtmultiple_key

10
Exercises jex7.php, jex8.php combine with HTML
coding. Use skeleton code

• jex7.php You are to show all the tables and for
  the test_alum database
• jex8.php You are to show the attributes for the
  users table. In particular, you have to show
• the name of attribute
• the type of attribute
• whether it is numeric (show T is it is numeric
  and F if it is not)

11
Skeleton code

• ltHTMLgt
• ltHEADgt
• ltTITLEgtPHP CODE with HTML commandslt/TITLEgt
• lt/HEADgt
• ltBODYgt
• ltIMG src"mu.gif"gt
• ltBRgt
• ltBRgt
• lt?php
• //insert your PHP code inside here
• ?gt
• lt/BODYgt
• lt/HTMLgt