Help:COAP-3180/week4: Difference between revisions

The educational technology and digital learning wiki
Jump to navigation Jump to search
m (Created page with '== Week 4 - COAP 3180 == === Topics Covered === * Principles of PHP programming * Simple PHP/MySQL interaction * Installation of Portalware === Classroom …')
 
 
(17 intermediate revisions by the same user not shown)
Line 3: Line 3:
=== Topics Covered ===
=== Topics Covered ===


'''HTML principles recalled'''
* HTML Forms
* (XMLhttprequest)
'''Introduction to PHP'''
* Principles of PHP programming
* Principles of PHP programming
* HTML forms tutorial
* Simple PHP/MySQL interaction
* Simple PHP/MySQL interaction
* Installation of Portalware
 
'''Working with Apache/PHP/MySQL'''
 
* Web server organization (a look at Mowes)
 
'''Using PHP/MySQL webform generators''':
 
* http://www.webformgenerator.com/ (Webform generator)
* http://www.sqlmaestro.com/products/mysql/phpgenerator/ (SQL Maestro PHP Generator)


=== Classroom activities ===
=== Classroom activities ===


* Use of PhP/MySQL generators
==== Classroom activities Monday ====
 
; HTML
* Recall of HTML principles (lecture)
 
; PHP
* Exploration of the [[PHP]] language (lecture)
 
; HTML-PHP Example
File '''form.html'''
<source lang="xml">
<!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>
</source>
 
File '''process-it.php''':
<source lang="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>
</source>
 
==== 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 ([http://www.sqlmaestro.com/products/mysql/phpgenerator/ Maestro Group])
 
In order to profit from this generator, you should '''declare foreign keys''' and use the '''InnoDB''' engine as
in the example below.
<source lang="sql">
-- 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/');
</source>
 
; Term project
* Q/A


=== Midterm exam ===
==== Midterm exam (Wednesday) ====


Main subjects will be SQL and a little bit of database design.
Main subjects will be SQL and a little bit of database design.
Line 37: Line 177:
=== Homework 4 ===
=== Homework 4 ===


Generate and configure an web application based on SQL table definitions.
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'''
 
# 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).
# 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.


Details TBA
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.


==== Submission Dates ====
==== Submission Dates ====


* This homework is due at start of wednesday week 5
* This homework is due at start of Wednesday week 5
* Students must provide the URL (where the instructor can see the widget) in the world classroom
* 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.
* Each homework counts 10% in your global evaluation. The four best homeworks will be taken into account.


==== Evaluation ====
==== Evaluation ====


TBA
* Same principle as homework 1. Make sure to get both the table definition(s) and the user interface/ergonomics right.
* See WorldClasroom
 
==== 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)


=== Tips for the homework===
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.


=== Links and teaching materials ===
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.


; Online software
=== Software, teaching materials and links===


; Software (installed in the Lab)
* http://www.sqlmaestro.com/products/mysql/phpgenerator/ (SQL Maestro PHP Generator)
* (optional) http://www.webformgenerator.com/ (Webform generator - this program may break your navigator)
* A text editor
* (optional) http://www.irfanview.com/ (IrfanView, free image manipulation program, including screen captures)
* (optional) https://addons.mozilla.org/en-US/firefox/addon/1146 (Firefox screen capture add-on, works fine enough)


; Teaching materials
; Teaching materials
* [[HTML forms tutorial]] (HTML reminder about forms)
* http://tecfa.unige.ch/guides/te/files/php-mysql.pdf (slides)
; 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.
* Weiss, Aaron (2000) [http://wdvl.internet.com/Authoring/Languages/PHP/Welcome/  Welcome to PHP], somewhat outdated ...
* [http://www.w3schools.com/php/default.asp PHP Tutorial] (W3C Schools, includes a section about PHP/MySQL).
* Gilfillan, Ian (2002). [http://www.databasejournal.com/features/mysql/article.php/1469211 Using a MySQL database with PHP], ''[http://www.databasejournal.com Database Journal]''. (shorter 3-page tutorial)
* Yank, Kevin (2002) wrote a five part series in the ''[http://www.databasejournal.com Database Journal]'' (Ok, but too much advertisement).
** [http://www.databasejournal.com/features/mysql/article.php/1383591 Build Your Own Database Driven Website Using PHP & MySQL: Pt. 1] (Overview)
** [http://www.databasejournal.com/features/mysql/article.php/1384211 Build Your Own Database Driven Website Using PHP & MySQL: Pt. 2]  (yet another MySQL tutorial) ** [http://www.databasejournal.com/features/mysql/article.php/1402121 Build Your Own Database Driven Website Using PHP & MySQL: Pt. 3] (PHP Introduction)
** [http://www.databasejournal.com/features/mysql/article.php/1402281 Build Your Own Database Driven Website Using PHP & MySQL: Pt. 4]. (Displaying information on a web page)
* [http://blazonry.com/scripting/linksdb/ MySQL PHP Web Database Tutorial] (1999). Simple table, by web.blazonry
* Williams, Hugh, E. (2002). Tips for Building Web Database Applications with PHP and MySQL, onLamp article (O'Reilly Databases). [http://www.onlamp.com/pub/a/onlamp/2002/04/04/webdb.html HTML]
* Sample chapter of [http://www.webdatabasebook.com/ Web Database Applications with PHP and MySQL] website.

Latest revision as of 19:27, 23 February 2010

Week 4 - COAP 3180

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:

Classroom activities

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>

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

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)

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.

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.

Evaluation

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

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.

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
Retrieved from "https://edutechwiki.unige.ch/mediawiki/index.php?title=Help:COAP-3180/week4&oldid=25535"