MySQL and PHP Integration

1
MySQL and PHP Integration

• Session 6
• INFM 718N
• Web-Enabled Databases

2
Agenda

• MySQL
• PHP-MySQL integration

3

• Relational normalization
• Structured programming
• Software patterns
• Object-oriented design
• Functional decomposition

Client Hardware
Web Browser
Database
Server Hardware
4
Getting started with MySQL

• root creates database, grants permissions
• By you on WAMP (mysql u root p)
• By Charles Goldman on OTAL
• CREATE DATABASE team1
• GRANT SELECT, INSERT, UPDATE, DELETE, INDEX,
  ALTER, CREATE, DROP ON team1. TO
  foo_at_localhost IDENTIFIED BY bar
• FLUSH PRIVILEGES
• Start mysql
• Start-gtRun-gtcmd for WAMP, ssh for OTAL
• mysql u foo p bar you can cd to your playspace
  first, but you dont need to
• Connect to your database
• USE team1

5
Some Useful MySQL Commands

• Looking around
• SHOW DATABASES
• SHOW TABLES
• DESCRIBE tablename
• SELECT FROM tablename
• Optimization
• SHOW TABLE STATUS \G
• OPTIMIZE TABLE tablename
• EXPLAIN ltSQLquerygt
• ALTER TABLE tablename ADD INDEX fieldname

6
Creating Tables

• CREATE TABLE contacts (
• ckey MEDIUMINT UNSIGNED NOT NULL
  AUTO_INCREMENT,
• id MEDIUMINT UNSIGNED NOT NULL,
• ctype SMALLINT UNSIGNED NOT NULL,
• cstring VARCHAR(40) NOT NULL,
• FOREIGN KEY (id) REFERENCES persons(id) ON
  DELETE CASCADE,
• FOREIGN KEY (ctype) REFERENCES ctlabels(ctype)
  ON DELETE RESTRICT,
• PRIMARY KEY (ckey)
• ) ENGINEINNODB
• To delete DROP TABLE contacts

7
Referential Integrity

• Foreign key values must exist in another table
• If not, those records cannot be joined
• Checked when data added to this table
• MySQL Error 150
• Triggers when data deleted/changed in other table
• Specify SET NULL, RESTRICT or CASCADE

8
Populating Tables

• INSERT INTO ctlabels
• (string) VALUES
• ('primary email'),
• ('alternate email'),
• ('home phone'),
• ('cell phone'),
• ('work phone'),
• ('AOL IM'),
• ('Yahoo Chat'),
• ('MSN Messenger'),
• (other)
• To empty a table DELETE FROM ctlabels

9
The SQL SELECT Command

• SELECT (projection) chooses columns
• Based on their label
• WHERE (restriction) chooses rows
• Based on their contents
• e.g. department ID HIST
• These can be specified together
• SELECT Student ID, Dept WHERE Dept History

10
WHERE Clause

• Each SELECT contains a single WHERE
• Numeric comparison
• lt, gt, , ltgt,
• e.g., gradelt80
• Boolean operations
• e.g., Name John AND Dept ltgt HIST

11
Connecting PHP to MySQL

• On WAMP
• dbcmysql_connect (localhost, userid,
  password)
• On OTAL
• dbcmysql_connect(/export/software/otal/mysql/r
  un/mysqld.sock,
• userid, password)

12
lt?php Script 8.1 - mysql_connect.php // Set the
database access information as constants. DEFINE
('DB_USER', 'tester') DEFINE ('DB_PASSWORD',
'tester') DEFINE ('DB_HOST', 'localhost') DEFINE
('DB_NAME', 'sitename') // Make the
connection. dbc _at_mysql_connect (DB_HOST,
DB_USER, DB_PASSWORD) OR die ('Could not connect
to MySQL ' . mysql_error() ) // Select the
database. _at_mysql_select_db (DB_NAME) OR die
('Could not select the database ' .
mysql_error() ) // Create a function for
escaping the data. function escape_data (data)
// Address Magic Quotes. if
(ini_get('magic_quotes_gpc')) data
stripslashes(data) // Check for
mysql_real_escape_string() support. if
(function_exists('mysql_real_escape_string'))
global dbc // Need the connection. data
mysql_real_escape_string (trim(data),
dbc) else data mysql_escape_string
(trim(data)) // Return the escaped
value. return data // End of function. ?gt
13
lt?php Script 9.15 - login.php (7th version
after Scripts 9.1, 9.3, 9.6, 9.10. 9.13
9.14) // Send NOTHING to the Web browser prior to
the session_start() line! // Check if the form
has been submitted. if (isset(_POST'submitted'
)) require_once ('../mysql_connect.php') //
Connect to the db. errors array() //
Initialize error array. // Check for an email
address. if (empty(_POST'email'))
errors 'You forgot to enter your email
address.' else e
escape_data(_POST'email') // Check
for a password. if (empty(_POST'password'))
errors 'You forgot to enter your
password.' else p
escape_data(_POST'password')
14
if (empty(errors)) // If everything's OK.
/ Retrieve the user_id and first_name for that
email/password combination. / query
"SELECT user_id, first_name FROM users WHERE
email'e' AND passwordSHA('p')"
result _at_mysql_query (query) // Run the
query. row mysql_fetch_array (result,
MYSQL_NUM) // Return a record, if applicable.
if (row) // A record was pulled from the
database. // Set the session data
redirect. session_name ('YourVisitID')
session_start()
_SESSION'user_id' row0
_SESSION'first_name' row1
_SESSION'agent' md5(_SERVER'HTTP_USER_AGENT
') // Redirect the user to the
loggedin.php page. // Start defining the
URL. url 'http//' .
_SERVER'HTTP_HOST' . dirname(_SERVER'PHP_SELF
') // Check for a trailing slash.
if ((substr(url, -1) '/') OR
(substr(url, -1) '\\') ) url
substr (url, 0, -1) // Chop off the slash.
// Add the page. url .
'/loggedin.php' header("Location
url") exit() // Quit the script.
else // No record matched the query.
errors 'The email address and password
entered do not match those on file.' // Public
message. errors mysql_error() .
'ltbr /gtltbr /gtQuery ' . query // Debugging
message. // End of if
(empty(errors)) IF. mysql_close() // Close
the database connection. else // Form has not
been submitted. errors NULL // End of
the main Submit conditional.
15
// Begin the page now. page_title
'Login' include ('./includes/header.html') if
(!empty(errors)) // Print any error messages.
echo 'lth1 id"mainhead"gtError!lt/h1gt ltp
class"error"gtThe following error(s) occurredltbr
/gt' foreach (errors as msg) // Print each
error. echo " - msgltbr /gt\n" echo
'lt/pgtltpgtPlease try again.lt/pgt' // Create the
form. ?gt lth2gtLoginlt/h2gt ltform action"login.php"
method"post"gt ltpgtEmail Address ltinput
type"text" name"email" size"20" maxlength"40"
/gt lt/pgt ltpgtPassword ltinput type"password"
name"password" size"20" maxlength"20" /gtlt/pgt
ltpgtltinput type"submit" name"submit"
value"Login" /gtlt/pgt ltinput type"hidden"
name"submitted" value"TRUE" /gt lt/formgt lt?php in
clude ('./includes/footer.html') ?gt
16
Arrays in PHP

• A set of key-element pairs
• days array(Jan-gt31, Febgt28, )
• months explode(/, Jan/Feb/Mar//Dec)
• _POST
• Each element is accessed by the key
• daysJan
• months0
• Arrays and loops work naturally together

17
Thinking about Arrays

• Naturally encodes an order among elements
• days rksort(days)
• Natural data structure to use with a loop
• Do the same thing to different data
• PHP unifies arrays and hashtables
• Elements may be different types

18
Functions in PHP

• Declaration
• function multiply(a, b3)return ab
• Invoking a method
• b multiply(b, 7)
• All variables in a function have only local scope
• Unless declared as global in the function

19
Why Modularity?

• Limit complexity
• Extent
• Interaction
• Abstraction
• Minimize duplication

20
Sources of Complexity

• Syntax
• Learn to read past the syntax to see the ideas
• Copy working examples to get the same effect
• Interaction of data and control structures
• Structured programming
• Modularity

21
Code Walkthrough

• Syntax
• How layout helps reading
• How variables are named
• How strings are used
• How forms are used
• How output is created
• MySQL Integration
• Opening a database
• Handling slashes
• Posing queries
• Using result sets

• Structured Programming
• How things are nested
• How arrays are used
• Modular Programming
• Functional decomposition
• How functions are invoked
• How arguments work
• How scope is managed
• How errors are handled
• How results are passed

22
Programming Skills Hierarchy

• Reusing code run the books programs
• Understanding patterns read the book
• Applying patterns modify programs
• Coding without patterns programming
• Recognizing new patterns

23
Best Practices

• Design before you build
• Focus your learning
• Program defensively
• Limit complexity
• Debug syntax from the top down

24
Rapid Prototyping Waterfall
Update Requirements
Write Specification
Choose Functionality
Initial Requirements
Create Software
Build Prototype
Write Test Plan
25
Focus Your Learning

• Find examples that work
• Tutorials, articles, examples
• Cut them down to focus on what you need
• Easiest to learn with throwaway programs
• Once it works, include it in your program
• If it fails, you have a working example to look at

26
Defensive Programming

• Goal of software is to create desired output
• Programs transform input into output
• Some inputs may yield undesired output
• Methods should enforce input assumptions
• Guards against the user and the programmer!
• Everything should be done inside methods

27
Limiting Complexity

• Single errors are usually easy to fix
• So avoid introducing multiple errors
• Start with something that works
• Start with an existing program if possible
• If starting from scratch, start small
• Add one new feature
• Preferably isolated in its own method

28
Types of Errors

• Syntax errors
• Detected at compile time
• Run time exceptions
• Cause system-detected failures at run time
• Logic errors
• Cause unanticipated behavior (detected by you!)
• Design errors
• Fail to meet the need (detected by stakeholders)

29
Debugging Syntax Errors

• Focus on the first error message
• Fix one thing at a time
• The line number is where it was detected
• It may have been caused much earlier
• Understand the cause of warnings
• They may give a clue about later errors
• If all else fails, comment out large code regions
• If it compiles, the error is in the commented part

30
Run Time Exceptions

• Occur when you try to do the impossible
• Use a null variable, divide by zero,
• The cause is almost never where the error is
• Why is the variable null?
• Exceptions often indicate a logic error
• Find why it happened, not just a quick fix!

31
Debugging Run-Time Exceptions

• Run the program to get a stack trace
• Where was this function called from?
• Print variable values before the failure
• Reason backwards to find the cause
• Why do they have these values?
• If necessary, print some values further back

32
Logic Errors

• Evidenced by inappropriate behavior
• Cant be automatically detected
• Inappropriate is subjective
• Sometimes very hard to detect
• Sometimes dependent on user behavior
• Sometimes (apparently) random
• Cause can be hard to pin down

33
Debugging Logic Errors

• First, look where the bad data was created
• If that fails, print variables at key locations
• if (DEBUG) echo \foobar foobar
• Examine output for unexpected patterns
• Once found, proceed as for run time errors
• define (DEBUG, FALSE) to clean the output

34
Three Big Ideas

• Functional decomposition
• Outside-in design
• High-level languages
• Structured programming, object-oriented design
• Patterns
• Design patterns, standard algorithms, code reuse

35
One-Minute Paper

• What was the muddiest point in todays class?
• Be brief!
• No names!