Help:COAP-3180/week4: Difference between revisions

The educational technology and digital learning wiki
Jump to navigation Jump to search
mNo edit summary
 
(5 intermediate revisions by the same user not shown)
Line 21: Line 21:
* http://www.sqlmaestro.com/products/mysql/phpgenerator/ (SQL Maestro PHP Generator)
* http://www.sqlmaestro.com/products/mysql/phpgenerator/ (SQL Maestro PHP Generator)


=== Classroom activities Monday ===
=== Classroom activities ===
 
==== Classroom activities Monday ====


; HTML
; HTML
Line 80: Line 82:
   ?>
   ?>
   <hr>
   <hr>
   phpinfo();
   // phpinfo();
</body>
</body>
</html>
</html>
</source>
</source>


 
==== Classroom activities Wednesday ====
=== Classroom activities Wednesday ===


; PHP-MySQL (homework preparation)
; PHP-MySQL (homework preparation)
Line 150: Line 151:
* Q/A
* Q/A


=== Midterm exam (Wednesday) ===
==== 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 182: Line 183:
* Explore the features of this tool (most are actually disabled in the free version)
* Explore the features of this tool (most are actually disabled in the free version)


'''Submission'''
'''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 in the interface. Migration may require a little bit of PHP tweaking.
# 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 the URL with an online text or an HTML file to the instructor.
# 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 screen captures.
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 ====


* See worldclasrrom
* 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 ====


=== 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
; SQL


* Use foreign keys and the InnoDB engine if your tables a related. The foreign keys syntax to use is
* 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 (primar_key_field)
   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.
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)  
   FOREIGN KEY (student_id) REFERENCES student(id)  
Line 213: Line 217:
* In the connection properties make sure to get the database name right
* In the connection properties make sure to get the database name right


Objects screen
Objects screen:
* Select all tables you want the user to edit later on
* Select all tables you want the user to edit later on


Queries screen
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)
* 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
Pages screen:
* for each table, you can define which columns will be visible and editable. Hit the Edit button (top right)
* for each table, you can define which columns will be visible and editable. Hit the Edit button (top right)


Color scheme
Color scheme:
* Select a theme
* Select a theme
* You may customize the CSS
* You may customize the CSS


Last screen (important !)
Last screen (important !)
* Change default headers and footers (top). This allows you add extra information in the generated pages.
* 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.
* 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, teaching materials and links===

Latest revision as of 20: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"