PHP Odds & Ends

Download Report

Transcript PHP Odds & Ends 

CIT 3353 -- Fall 2006
www.clt.astate.edu/jseydel/mis3353
Website Development &
Management
PHP Odds & Ends
Instructor: John Seydel, Ph.D.
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
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 . . .
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
Let’s Complete the First Database
Exercise
Where we left off:





Initial values assigned
Connection created
Database opened
Recordset created
Note: three line equivalent (poor coding)
What’s 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
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
Summary:
What We’ve 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
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: don’t 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
Some Other Things: FKAuto
Calculator Page (calc.php)
JavaScript: used within the onLoad
event handler for the <body> element
Note the arrays


Use of the range() function
Alternatively, but more work: array()
foreach() construct
Appendix
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
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;