Web Database Programming Week 8

1
Web Database Programming Week 8

• DB Management One Table

2
Manage a DB Table

• Actions
• Add a record
• Delete record(s)
• Update a record
• Sort by field values
• The key is to design a good user interface also
  the hard part

3
Add and Update

• May share the same interface (I.e. use the same
  form)
• Consistent
• Reusable code
• Add empty form value
• Update display value from database

4
Update

• Display the table
• Select the record to be updated
• Link
• Button

5
Key for Update

• Must has a key value that matches a record from
  the database
• Must be unique
• Use primary key field
• This key value can be passed from HTML form to
  script via
• Hidden form field
• URL variable (GET method)

6
SQL for Update

• SQL
• UPDATE tablename
• SET columnname value, ...
• WHERE condition
• Notes
• value are set on all records matching the
  condition!

7
Deleting Data from a Table

• SQL part of the code
• DELETE FROM tablename
• WHERE condition
• Note
• all records matching the condition are deleted
• if there is no where clause deletes all records
  in table

8
Delete Form

• Display the table
• Select the item(s) to be deleted
• Have a checkbox before each record
• Delete action
• Link
• Button

9
Multiple Checkboxes are Checked

• Checkboxes should share the same array name
• ltINPUT type"checkbox" nameid"
  value1"gtApple
• ltINPUT type"checkbox" nameid"
  value2"gtOrange
• ltINPUT type"checkbox" nameid"
  value3"gtBanana
• In PHP code, use
• if(sizeof(_POSTid')) foreach(_POSTid'
  as recordId)   //delete record with this id   
               

10
Simple Example

• Based on the Pets table
• A full database query is used to generate the
  record list with a link for delete and update on
  each record
• And a link to add a record