Help:COAP-3180/week4

From EduTech Wiki
Jump to: navigation, search

1 Week 4 - COAP 3180

1.1 Topics Covered

HTML principles recalled

  • HTML Forms
  • (XMLhttprequest)

Introduction to PHP

  • Principles of PHP programming
  • HTML forms tutorial
  • Simple PHP/MySQL interaction

Working with Apache/PHP/MySQL

  • Web server organization (a look at Mowes)

Using PHP/MySQL webform generators:

1.2 Classroom activities

1.2.1 Classroom activities Monday

HTML
  • Recall of HTML principles (lecture)
PHP
  • Exploration of the PHP language (lecture)
HTML-PHP Example

File form.html

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html><head><title>Simple test with</title></head>
<body>
<h1>Simple test with PHP</h1><hr>
 <form action="process-it.php" method="post">

 What do you know about HTML ?
 <input type="radio" name="choice" value="1" checked>little
 <input type="radio" name="choice" value="2">some
 <input type="radio" name="choice" value="3">everything

 <br>
 What is your programming experience ?
 <input type="radio" name="choice2" value="1" checked>none
 <input type="radio" name="choice2" value="2">some
 <input type="radio" name="choice2" value="3">good
 <P>
 <input type="submit" value="See result!">
 </form>
</body>
</html>

File process-it.php:

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html><head><title>Simple test with</title></head>
<body>
 <h1>Simple test with PHP</h1><hr>
 <?php

  // Get variables from the form
  $choice = $_POST['choice'];
  $choice2 = $_POST['choice2'];

  // Compute the score
  $score = $choice + $choice2;
  // Display the score
  echo "<h3>Your score is " . $score . "</h3>";
  if ($score < 3) {
    echo "<p>You are a beginner</p>";
  } elseif ($score < 5) {
    echo "<p>You have some knowledge</p>";
  } else {
    echo "<p>You are an expert !</p>";
  }
  ?>
  <hr>
  // phpinfo();
</body>
</html>

1.2.2 Classroom activities Wednesday

PHP-MySQL (homework preparation)
  • PHP MySQL functions (exploration of the online PHP manual / Slides) if time left only
  • Use of the PHP Generator for MySQL (Maestro Group)

In order to profit from this generator, you should declare foreign keys and use the InnoDB engine as in the example below.

-- MySQL example with foreign keys.
-- Needs the InnoDB engine (see the table definitions) 

--
-- Table 'student'
--

DROP TABLE IF EXISTS student;
CREATE TABLE IF NOT EXISTS student (
  id int(10) NOT NULL AUTO_INCREMENT,
  name char(40) NOT NULL DEFAULT '',
  first_name char(40) NOT NULL DEFAULT '',
  PRIMARY KEY (id)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8;

--
-- Data for table 'student'
--

INSERT INTO student (id, name, first_name) VALUES
(1, 'Testeur', 'Bill'),
(2, 'Testeur', 'Joe'),
(3, 'Testeuse', 'Sophie'),
(4, 'Player', 'Jim');

--
-- Table 'exercise'
--

DROP TABLE IF EXISTS exercise;
CREATE TABLE IF NOT EXISTS exercise (
  id int(10) NOT NULL AUTO_INCREMENT,
  title char(40) NOT NULL DEFAULT '',
  student_id int(10) NOT NULL,
  comments varchar(128) DEFAULT NULL,
  url char(60) NOT NULL DEFAULT '',
  PRIMARY KEY (id),
  KEY student_id (student_id),
  FOREIGN KEY (student_id) REFERENCES student(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Table 'exercise'
--

INSERT INTO exercise (id, title, student_id, comments, url) VALUES
(1, 'Exercise 1', 1, 'small comment', 'http://tecfa.unige.ch/'),
(2, 'Exercise 2', 1, 'no comment', 'http://tecfa.unige.ch/'),
(3, 'exe four', 2, 'No comment', 'http://localhost/'),
(4, 'exe four', 2, 'No comment', 'http://localhost/');
Term project
  • Q/A

1.2.3 Midterm exam (Wednesday)

Main subjects will be SQL and a little bit of database design. "Minor" subject will be web application architecture (high level principles)

SQL:

  • SELECT
    • SELECT ... WHERE, INSERT ... ORDER
    • Joins (select from two tables)
  • CREATE
    • Data types: Integer, Float, VarChar, text
    • DEFAULT, NOT NULL, Auto increment
    • Keys
    • Primary keys
  • INSERT (both forms(
  • UPDATE / DELETE
  • DROP

Database design:

  • Simple 2-3 table architectures
  • 1 to N and N to N relations between tables

Exam will be "open book". I will include a set of various tasks (e.g. a simple design problem, repair problems, conceptual questions)

1.3 Homework 4

Generate and configure a web application based on a SQL table definitions.

  • Create between 1 and 4 SQL tables or reuse one or all tables from homework 3. Add foreign keys definitions if needed, because if you use foreign keys then the Generator will be able to generate pull-down menus in the edit interface.
  • Use the Maestro PHP Generator to create an interface that allows to view and to edit data.
  • Explore the features of this tool (most are actually disabled in the free version)

Submission: choose either one

  1. Migrate the files to a service provider and submit the URL to the instructor together with a short list of features you implemented. You may paste the this list in the submission field of the Worldclassroom interface. Migration may require a little bit of PHP tweaking (edit the settings.php file).
  2. Create a short report with a short feature list plus screen captures of the most important screens a user will see and submit either an URL with an online text or an HTML file to the instructor. If you submit an HTML file, make sure to create a zip file that includes all the pictures.

In other words: I need (a) a list of what you have done and (b) either a URL where I can see the application or a zipped HTML + screen captures zip file.

1.3.1 Submission Dates

  • This homework is due at start of Wednesday week 5
  • Students must provide either an URL for the application, an URL for documentation or an HTML file with documentation.
  • Each homework counts 10% in your global evaluation. The four best homeworks will be taken into account.

1.3.2 Evaluation

  • Same principle as homework 1. Make sure to get both the table definition(s) and the user interface/ergonomics right.
  • See WorldClasroom

1.3.3 Tips for the homework

Make sure to get the bulk of the homework done by Monday, Week 5. I will reserve some time for questions.

SQL
  • Use foreign keys and the InnoDB engine if your tables a related. The foreign keys syntax to use is
 FOREIGN KEY (field_in_this_table) REFERENCES other_table (primary_key_field)

Example: In a exercises table, each record has a student_id column that points to the id column of the student table.

 FOREIGN KEY (student_id) REFERENCES student(id) 
PHP Generator

Entry screen:

  • In the connection properties make sure to get the database name right

Objects screen:

  • Select all tables you want the user to edit later on

Queries screen:

  • Skip, unless you know SQL very well. In the latter case you may formulate a query that will sort data displayed (see the manual)

Pages screen:

  • for each table, you can define which columns will be visible and editable. Hit the Edit button (top right)

Color scheme:

  • Select a theme
  • You may customize the CSS

Last screen (important !)

  • Change default headers and footers (top). This allows you to add extra information in the generated pages.
  • Define the output directory (bottom). We suggest to create a mowes/www/subdirectory and to put the files there directly.

If you plan to migrate to a an other provider (totally optional)

  • Export the SQL from the database
  • Edit file settings.php and change the return array values of the first function in the file.
  • Import the SQL to the other MySQL
  • Move all the files to the other web server.

1.4 Software, teaching materials and links

Software (installed in the Lab)
Teaching materials
PHP with MySQL tutorials

Reading these is optional, since PHP coding will not be tested in an exam or in the term project. Below are a few PHP and PHP/MySQL tutorials that can be found on the net. There exist many others.

  • PHP Tutorial (W3C Schools, includes a section about PHP/MySQL).
  • Williams, Hugh, E. (2002). Tips for Building Web Database Applications with PHP and MySQL, onLamp article (O'Reilly Databases). HTML