PHP with SQL and HTML

1
PHP with SQL and HTML

• Or connecting a databizzle to a web sizzle.

2
Goals for the Day

• Learn how to connect to a database via PHP
• Learn about HTML forms
• Put the two together

3
Reminder

• All of the code I give here should be embedded in
  HTML
• My first PHP script
• //some PHP Code
• ?

4
PHP Connecting

• user "parker"
• pwd Yeah, right"
• serv 'mysql.cs.orst.edu'
• dbname 'parker'
• link mysql_connect(serv, user, pwd)
• //test the connection
• if (!link) die ("You are bad at life.)
• //select db
• mysql_select_db(dbname)
• or die ("Can't select database dbname!")

5
PHP Querying

• //formulate query
• query "SELECT from album"
• //execute
• result mysql_query(query) or die ("Error
  query. . mysql_error())

6
PHP Getting the result

• The result of an SQL query in PHP is an
  enumeration
• Repeatedly call a function to extract the next
  row in the result
• Form of the returned row depends on the function
  called
• mysql_fetch_row returns array with numeric
  indices
• mysql_fetch_assoc return array with hash indices
• mysql_fetch_object returns object with properties

7
PHP Three ways to get the result

• //check to see if the query returned something
• if (mysql_num_rows(result) 0)
• echo
  • //this gets rows until row false
  • while(row mysql_fetch_row(result))
  • //indices are columns!
  • echo
  • row0, row1
  • echo
  "

8
PHP Three ways to get the result

• if (mysql_num_rows(result) 0)
• echo
  • //this gets rows until row false
  • while(row mysql_fetch_assoc(result))
  • echo
  • rowtitle, rowartist
  • echo
  "

9
PHP Three ways to get the result

• if (mysql_num_rows(result) 0)
• echo
  • //this gets rows until row false
  • while(row mysql_fetch_object(result))
  • echo
  • row-title, row-artist
  • echo
  "

10
Switching GearsHTML Forms

• Forms in HTML are fun and easy
• Data from an HTML form will be passed to a
  specified PHP page
• In the PHP page, there will be an array
  containing the form data
• Pay attention to the name attribute in the form
  tags this will be an index into the form data
  array

11
Forms Text Fields

• Title
• 
  
• Artist
• 
  
• Notes
• 
  

12
Forms Radio Buttons

• Title
• 
  
• valueHanson Hanson
  
• valuePaula Abdul Paula Abdul
  

13
Forms Drop down Box

• Title
• 
  
• Hanson
• Paula Abdul

14
Forms Checkbox

• Title
• 
  
• valueHanson Hanson?
  

15
Forms Text Area

• Title
• 
  
• cols80 wrapvirtual
• Some initial text.
• Maybe something about how much I love Hanson.
• 
  

16
PHP Processing

• if (empty(_POSTtitle)
• die (Enter a title)
• if (empty(_POSTartist)
• die (Enter an artist)
• title
• mysql_escape_string(_POSTtitle
• artist
• mysql_escape_string(_POSTartist
• query
• INSERT INTO album VALUES(title, artist)
• result mysql_query(query)

17
Dynamic Form Generation

• query SELECT FROM album
• result mysql_query(query)
• if (mysql_num_rows(result) 0)
• echo "method\"post\""
• echo ""
• while(row mysql_fetch_object(result))
• echo "title\"
• echo row-title"
• echo "
  "
• echo ""
• echo ""

18
Processing Dynamic Form

• selection _POSTalbums
• query SELECT FROM album WHERE
  titleselection
• result mysql_query(query)
• record mysql_fetch_object(result)
• echo The title was record-title
  
• echo The artist was record-artist
  

19
And finally . . .

• E-mail me with questions lots more is possible
  in PHP