Website Development

1
Website Development Management
CIT 3353 -- Fall 2006 www.clt.astate.edu/jseydel/m
is3353

• PHP Odds Ends

Instructor John Seydel, Ph.D.
2
Student Objectives

• Upon completion of this class meeting, you
  should be able to
• Summarize the steps involved in using PHP to
  accomplish basic database queries
• Use PHP to display database contents to a web
  page
• Create an onLoad event handler for a web page

3
Using PHP to Interface with MySQL

• Always starts with the creation of a connection
• Requires opening a database
• Generally involves generating a recordset (aka,
  result set)
• Processing the recordset typically involves some
  sort of looping construct
• One repetition per record
• Must be controlled by some sort of counter
  foreach(), while(), and for()
• Makes use of several important functions . . .

4
PHP Functions for Basic Queries

• Create a connection mysql_connect()
• Arguments (string) server, userID, password
• Returns a string (connection information)
• Open a database mysql_select_db()
• Arguments (string) database to be used,
  connection
• Returns a boolean value
• Create a recordset mysql_query()
• Arguments (string) SQL query, connection
• Returns a recordset (an array of data)
• Process a recordset
• mysql_num_rows()
• Argument (array) recordset
• Returns an integer (number of records)
• mysql_fetch_array()
• Arguments (string) recordset
• Returns a row from the recordset

5
Lets Complete the First Database Exercise

• Where we left off
• Initial values assigned
• Connection created
• Database opened
• Recordset created
• Note three line equivalent (poor coding)
• Whats left
• Process the records (a row at a time)
• Get data for each row in the database table
• Assign those data to variables
• Add to the output block
• Incorporate output block into the HTML section

6
A Bit More on Queries

• Not just getting information from a database
• Also involves putting information into a database
• Creating databases
• Creating tables
• Adding records to tables
• Modifying (updating) existing records
• Require the use of SQL (structured query
  language), a universal database language
• Reference material so far, mostly the class
  notes

7
SummaryWhat Weve Seen Recently

• New PHP functions
• mysql_connect()
• mysql_select_db()
• mysql_query()
• mysql_num_rows()
• mysql_fetch_array()
• A little SQL
• Used directly with MySQL
• Incorporated into PHP script
• The for() construct
• How to use PHP to connect to MySQL, open a
  database, create a recordset, and process that
  recordset
• How/where MySQL files are stored

8
The FKAuto Database Files

• For use on your own computer
• Download these
• Available in the Documents directory (not
  public_html) of the cit3353 account on SuSE1
• Recall the password?
• All three files for the UsedCars database are in
  a directory named fkauto
• For use on SuSE1 dont worry about where they
  are, as they are available to your scripts
• However, the owner is user cit3353
• Thus use the appropriate values for user and
  password

9
Some Other Things FKAuto Calculator Page
(calc.php)

• JavaScript used within the onLoad event handler
  for the element
• Note the arrays
• Use of the range() function
• Alternatively, but more work array()
• foreach() construct

10
Appendix
11
How MySQL Stores the Data

• Each table (i.e., relation) involves three files
• TableName.frm
• TableName.MYD (where the actual data are)
• TableName.MYI
• The files for all the tables belonging to a given
  database are stored in a directory with the same
  name as the database
• All database directories are stored in the data
  directory within c\mysql

12
Working with MySQL

• Get started
• mysql -u jojobeans p
• USE testDB
• SHOW TABLES
• Using tables
• CREATE TABLE tblTest
• (testID int, testNote text)
• EXPLAIN tblTest
• SELECT FROM tblTest
• INSERT INTO tblTest
• VALUES (123,First entry)
• INSERT INTO tblTest
• VALUES (456,Second entry)
• SELECT FROM tblTest
• QUIT