Latest revision as of 13:26, 9 June 2017

Intro

This course is 5 ECTS and it's mandatory for CSE students. Other ITC students may also attend and CSE students who comprehend Estonian can follow the I244 lectures.

The main point of this course is to get to know the software stack that is used to build modern web applications and by the end of the course being able to write a web application and if we'll have enough time - to deploy it on a (virtual) server and defend it. Deduplicate your work and combine the work of this course with Python and Research Project courses.

Progress visible in Git from day one
Possible scenarios to pass the course:
- classic: Build a simple mobile-frindly webshop with shopping cart using PHP, MySQL, Apache, Ubuntu.
- Substitute a component (see below) you don't like and do the same
- Scratch your own itch, develop something that largely makes use of following technologies and it is relevant to you
- Extend WordPress, Joomla etc to build a website for your customer, eg. when you're working already
- Pick a project idea from Python course page, there are several ideas which more or less constitute as web apps.
- Find an interesting web application that's participating on Google Summer of Code, get to know the community, prepare for participation on GSoC and successfully finish the GSoC.
If this is your first experience with this sort of stuff make sure you go HTML & CSS, JavaScript, jQuery and SQL tracks on CodeAcademy and start with simply creating your homepage :)
For page layouts check Twitter Bootstrap

Class News

1. The scheduled classes for 14/02/2017 and 21/02/2017 has been rescheduled for Thursday 23rd, February and Wednesday 1st, March. Kindly update you timetables. Sorry for the inconveniences this might cause.

2. Kindly send your lab github link to me on ladokunbolaji@gmail.com or bolaji.ladokun@itcollege.ee if you have not.

3. Progress Sheet Progress

4. Exam Date: 09.06.2017 14:00 - 15:30 Room: 319 Remake Exam: 15.06.2017 14:00 - 15:30 Room: 319

5. Exam 09.06.2017 https://drive.google.com/file/d/0B3D8AA6scdlEN2FTYWtIR296czA/view?usp=sharing

Web shop

Lecture/lab #1: Structuring HTML and making use of relational databases

Lecture recording #1, lecture recording #2

Use following as barebone for your PHP application, check here for semantic tags of HTML5:

<!DOCTYPE html>
<html>
  <head>
    <meta charset="utf-8"/>
    <meta name="description" content="Introduction to this guy's website">
    <title>This goes into the titlebar</title>
    <link rel="css/style.css" type="text/css"/>
    <script type="text/javascript" src="js/main.js"></script>
    <meta name="viewport" content="width=device-width, user-scalable=no"/><!-- Disable zoom on smartphone -->
  </head>
  <body>
    <header>
      Your shop name goes here
    </header>
    <nav>
      Navigation links go here
    </nav>
    <section>
      Product items go here
    </section>
    <article>
      The actual content goes here
      <?php echo "This, is hellõu from PHP!"; ?>
    </article>
    <aside>
      Context specific links go here
    </aside>
    <footer>
      Footer goes here
    </footer>
  </body>

</html>

Use phpMyAdmin to create a table for your web shop's products:

CREATE TABLE IF NOT EXISTS `lauri_shop_product` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(30) NOT NULL,
  `description` text NOT NULL,
  `price` decimal(10,2) NOT NULL,
  PRIMARY KEY (`id`)
);

Listing items from MySQL database can be implemented with mysqli:

<ul>
  <?php

    $conn = new mysqli("localhost", "test", "t3st3r123", "test");
    $results = $conn->query(
      "SELECT id,name,price FROM lauri_shop_product;");

    while ($row = $results->fetch_assoc()) {
      ?>
        <li>
          <a href="description.php?id=<?=$row['id']?>">
            <?=$row["name"]?></a>
            <?=$row["price"]?>EUR
        </li>
      <?php
    }

    $conn->close();

  ?>
</ul>

Description page:

<a href="index.php">Back to product listing</a>

<?php
$conn = new mysqli("localhost", "test", "t3st3r123", "test");
$statement = $conn->prepare(
  "SELECT `name`, `description`, `price` FROM" .
  " `lauri_shop_product` WHERE `id` = ?");
$statement->bind_param("i", $_GET["id"]);
$statement->execute();
$results = $statement->get_result();
$row = $results->fetch_assoc();
?>

<span style="float:right;"><?=$row["price"];?>EUR</span>
<h1><?=$row["name"];?></h1>

<p>
  <?=$row["description"];?>
</p>

Occasionally you might want to get all rows from a table, this can be achieved with fetch_all method:

<ul>
  <?php
 
    $conn = new mysqli("localhost", "test", "t3st3r123", "test");
    $results = $conn->query(
      "SELECT id,name,price FROM lauri_shop_product;");
    $rows = $results->fetch_all(MYSQLI_ASSOC); // Pull ALL results
    $conn->close();
     
    foreach ($rows as $row) {
      ?>
        <li>
          <a href="description.php?id=<?=$row['id']?>">
            <?=$row["name"]?></a>
            <?=$row["price"]?>EUR
        </li>
      <?php
    }
  ?>
</ul>

Lecture/lab #2: Structuring PHP files and managing sessions

Place the header and footer in different files header.php and footer.php and include them like this:

<?php
require_once "config.php";
include "header.php";
$conn = new mysqli(DB_SERVER, DB_USER, DB_PASS, DB_NAME);
if ($conn->connect_error)
  die("Connection to database failed:" .
    $conn->connect_error);
$conn->query("set names utf8"); // Support umlaut characters
?>
<!-- Page specific stuff goes here -->
<? include "footer.php" ?>

Make sure session is started in header.php:

<?php
session_start();
if (!array_key_exists("cart", $_SESSION)) {
    $_SESSION["cart"] = array();
}
?>

Also create config.php, DO NOT commit this to the Git repository below:

<?php
// This is site specific configuration! Do not commit this to Git!
define("DB_SERVER", "localhost");
define("DB_USER",   "test");
define("DB_PASS",   "t3st3r123");
define("DB_NAME",   "test");
?>

In the product description page place "Add to cart" button:

<form method="post" action="cart.php">
  <input type="hidden" name="id" value="<?=$_GET["id"];?>"/>
  <input type="submit" value="Add to cart"/>
</form>

Create cart.php for adding items to the cart and displaying the shopping cart contents:

$product_id = intval($_POST["id"]);
if (array_key_exists($product_id, $_SESSION["cart"])) {
    $_SESSION["cart"][$product_id] += 1;
} else {
    $_SESSION["cart"][$product_id] = 1;
}
var_dump($_SESSION["cart"]);

Set up your Git, you'll have to do this again if you change computer:

git config --global user.name "$(getent passwd $USER | cut -d ":" -f 5)"
git config --global user.email $USER@itcollege.ee
git config --global core.editor "gedit -w -s"

Create a repository at Github and in your source code tree:

git init
git remote add origin git@github.com:user-name/repo-name.git
git add *.php js/*.js css/*.css
git commit -m "Initial commit"
git push -u origin master

Add .gitignore, README.md files. Check out if it looks more or less like this.

Lecture/lab #3

This time we improved our shopping cart code and applied some styling to the website.

Exercises:

When adding items to cart, make it possible to select count of items using a combobox.
Add grand total calculation for the shopping cart.
Split your CSS files to three: common design, design for displays and design for printers, check out page source of this

Lecture/lab #4: User registration

Set up database table for user accounts:

CREATE TABLE IF NOT EXISTS `lauri_shop_user` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `email` varchar(64) NOT NULL,
  `password` varchar(256) NOT NULL,
  `first_name` varchar(64) NOT NULL,
  `last_name` varchar(64) NOT NULL,
  `phone` varchar(20) DEFAULT NULL,
  `dob` date NOT NULL,
  `salutation` varchar(5) DEFAULT NULL,
  `vatin` varchar(12) DEFAULT NULL,
  `company` varchar(64) DEFAULT NULL,
  `country` char(2) NOT NULL,
  `address` varchar(256) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `email` (`email`)
);

Form for entering the data:

<form method="post" action="regsubmit.php">
  <div>
    <label for="email">E-mail</label>
    <input type="email" name="email" required/>
  </div>
  <div>
    <label for="password">Password</label>
    <input type="password" name="password" required/>
  </div>
  <div>
    <select name="country">
      <option value="ee">Estonia</option>
      <option value="lt">Latvia</option>
      <option value="lv">Lithuania</option>
    </select>
  </div>
  <div>
    <label for="phone">Telephone number</label>
    <input type="tel"/>
  </div>
  <div>
    <label for="vatin">VAT indication number</label>
    <input type="text" pattern="([A-Z0-9]{4,14})?$"/>
  </div>
  <div>
    <label for="dob">Date of birth</label>
    <input type="date" name="dob" placeholder="dd/mm/yyyy" required/>
  </div>
  <div>
    <label for="first_name">First name</label>
    <input type="text" name="first_name" required/>
  </div>
  <div>
    <label for="last_name">Last name</label>
    <input type="text" name="last_name" required/>
  </div>
  <div>
    <input type="submit"/>
  </div>
</form>

PHP code for processing submission:

require_once "config.php";
include "header.php";
$conn = new mysqli(DB_SERVER, DB_USER, DB_PASS, DB_NAME);
if ($conn->connect_error)
  die("Connection to database failed:" .
    $conn->connect_error);
$conn->query("set names utf8");

$statement = $conn->prepare(
"INSERT INTO `lauri_shop_user` (
    `email`,
    `password`,
    `first_name`,
    `last_name`,
    `phone`,
    `dob`,
    `salutation`,
    `vatin`,
    `company`,
    `country`,
    `address`)
VALUES (?, PASSWORD(?), ?, ?, ?, ?, ?, ?, ?, ?, ?)");

# whenever you get "call to a member function ... on a non-object" this means something
# is failing **before** that line so you have to manually check for errors like this:
if (!$statement) die("Prepare failed: (" . $conn->errno . ") " . $conn->error);

$statement->bind_param("sssssssssss",
    $_POST["email"],
    $_POST["password"],
    $_POST["first_name"],
    $_POST["last_name"],
    $_POST["phone"],
    $_POST["dob"],
    $_POST["salutation"],
    $_POST["vatin"],
    $_POST["company"],
    $_POST["country"],
    $_POST["address"]);

if ($statement->execute()) {
    echo "Registration was successful! <a href=\"index.php\">Back to main page</a>";
} else {
    if ($statement->errno == 1062) {
       // This will result in 200 OK
       echo "This e-mail is already registered";
    } else {
       // This will result in 500 Internal server error
       die("Execute failed: (" .
           $statement->errno . ") " . $statement->error);
    }
}

Lecture/lab #5: Logging in

Login form for our web shop:

<form action="login.php">
  <input type="text" name="user"/>
  <input type="password" name="password"/>
  <input type="submit" value="Log in!"/>
</form>

Create login.php:

// This is login.php, here we check if user provided proper credentials
var_dump($_POST); // This is just to check that the data gets to server
include "config.php";

// This is copy-paste from description.php!
$conn = new mysqli(DB_SERVER, DB_USER, DB_PASS, DB_NAME);
if ($conn->connect_error)
  die("Connection to database failed:" .
    $conn->connect_error);

$conn->query("set names utf8");

$statement = $conn->prepare(
"SELECT * FROM lauri_shop_user
WHERE email = ? AND password = PASSWORD(?)"); // I PROMISE I WILL NEVER USE THIS IN PRODUCTION

$statement->bind_param("ss", $_POST["user"], $_POST["password"]);
$statement->execute();
$results = $statement->get_result();
$row = $results->fetch_assoc();

if($row) {
    echo "Login successful, hello " . $row["first_name"];
    $_SESSION["user"] = $row["id"]; // This just stores user row number!
} else {
    echo "Login failed";
}

Note that in production you should follow guidelines on storing passwords in safe manner, such as PBKDF2!

In index.php navigation bar:

if (array_key_exists("user", $_SESSION)) {
   echo "Hello" . $_SESSION["user"];
   // Put link to logout.php here
} else {
   // Show the login form above
}

For logging out create another file logout.php:

session_start();
unset($_SESSION["user"]);
header("Location: index.php");

Exercises:

Create changepassword.php where use could change their password

Lecture/lab #6: SQL foreign keys and join queries

This time we added two database tables for storing orders.

Database table for orders, again use phpMyAdmin to import it:

CREATE TABLE IF NOT EXISTS `lauri_shop_order` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `created` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `shipped` timestamp NULL DEFAULT NULL,
  `paid` timestamp NULL DEFAULT NULL,
  `shipping_address` text,
  `user_id` int(11) NOT NULL,
  PRIMARY KEY (`id`)
);

Also add many to many relationship between order and products belonging to a particular order:

CREATE TABLE IF NOT EXISTS `lauri_shop_order_item` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `order_id` int(11) NOT NULL,
  `product_id` int(11) NOT NULL,
  `count` int(11) NOT NULL,
  `unit_price` decimal(10,2) NOT NULL,
  PRIMARY KEY (`id`)
);

Populate both tables with some arbitrary data and verify you get sensible output with SQL snippets below.

Following SQL query lists orders belongin to a certain user:

select
  `lauri_shop_order`.`id` as `order_id`,
  `lauri_shop_order`.`shipping_address` as `shipping_address`,
  `lauri_shop_order`.`created` as `created`
from
  `lauri_shop_order`
where
  `lauri_shop_order`.`user_id` = 2

Following SQL query joins data from two tables to generate list of items belonging to a certain order:

select
  -- The format here is `table_name`.`column_name` as `Whatever you want to call it`
  `lauri_shop_order_item`.`id` as `order_item_id`,
  `lauri_shop_order_item`.`product_id` as `product_id`,
  `lauri_shop_product`.`name` as `product_name`,
  `lauri_shop_order_item`.`unit_price` as `order_item_unit_price`,
  `lauri_shop_order_item`.`count` as `order_item_count`,
  `lauri_shop_order_item`.`unit_price` * `lauri_shop_order_item`.`count` as `subtotal`
from
  `lauri_shop_order_item`
join
  `lauri_shop_product`
on
  -- We select all rows from lauri_shop_order_item table and then
  -- try to find a matching row based on following condition
  `lauri_shop_order_item`.`product_id` = `lauri_shop_product`.`id`
where
  `lauri_shop_order_item`.`order_id` = 1

Add database indexes so we could rapidly search by these columns:

ALTER TABLE `lauri_shop_order` ADD INDEX (`user_id`);
ALTER TABLE `lauri_shop_order_item` ADD INDEX (`product_id`);
ALTER TABLE `lauri_shop_order_item` ADD INDEX (`order_id`);

Add foreign key constraints to bind one table to another:
Important note: the foreign keys fields must be indexed and the constraint name must be unique throughout the whole database (otherwise a "check data type" error occurs).

ALTER TABLE `lauri_shop_order`
  ADD CONSTRAINT `user_fk` FOREIGN KEY (`user_id`) REFERENCES `lauri_shop_user` (`id`) ON DELETE CASCADE ON UPDATE NO ACTION;
ALTER TABLE `lauri_shop_order_item`
  ADD CONSTRAINT `product_fk` FOREIGN KEY (`product_id`) REFERENCES `lauri_shop_product` (`id`) ON DELETE CASCADE ON UPDATE NO ACTION,
  ADD CONSTRAINT `order_fk` FOREIGN KEY (`order_id`) REFERENCES `lauri_shop_order` (`id`) ON DELETE CASCADE ON UPDATE NO ACTION;

You should end up with a database schema like this:

Exercises:

Add orders.php for showing a list of orders belonging to a certain user. Copy boilerplate code from description.php.
Add orderdetail.php for listing items in a particular order. Use GET parameter 'id' to specify order id. Copy boilerplate code from description.php.
Split your CSS files to three: common design, design for displays and design for printers, check out page source of this.

Lecture/lab #7: Uploading files

We basically have two options for uploading files to server:

Use BLOB datatype for the column
Store filename in varchar field and move the file to an uploads directory

This is an example implementation for the latter. It calculates the SHA1 checksum of the file and appends the original file name extension. This way the files are deduplicated as well :)

<html>
  <body>
    <form method="post" enctype="multipart/form-data">
      <input type="text" name="product_title"/>
      <input type="text" name="product_description"/>
      <input type="file" name="product_image" required/>
      <input type="file" name="product_thumbnail"/>
      <input type="submit"/>
    </form>
    <!-- You also need:
    mkdir uploads
    chmod 777
    -->
    <?php
    if (array_key_exists("product_image", $_FILES)) {
      $finfo = finfo_open(FILEINFO_MIME_TYPE);
      $mimetype = finfo_file($finfo, $_FILES["product_image"]["tmp_name"]);
      if ($mimetype != "application/pdf") die("Go away!");

      $checksum = sha1(file_get_contents(
        $_FILES["product_image"]["tmp_name"])) . "." .
        pathinfo($_FILES["product_image"]["name"], PATHINFO_EXTENSION);
      if (!file_exists("uploads/" . $checksum)) {
        copy(
          $_FILES["product_image"]["tmp_name"],
          "uploads/" . $checksum);
      }
    }
    ?>
    <p>Mimetype was: <?= $mimetype; ?></p>
    <p>Checksum was: <a href="uploads/<?=$checksum;?>"><?=$checksum;?></a>
    <p>Filename was: <?=$_FILES["product_image"]["name"];?></p>
    <p>File stored at: <?=$_FILES["product_image"]["tmp_name"];?></p>
  </body>
</html>

Lecture/lab #8: JavaScript and CSS selectors

This will simply load the file from given URL and place the response into #content

// In HTML we only have <button id="update_cart">Update cart</button> to trigger update
// and <div id="shopping_cart">Initially empty</div> for placing the shopping cart in the webpage

// Wait page to be loaded and then associate click event
document.addEventListener("DOMContentLoaded", function() {
  document.querySelector("#update_cart").addEventListener(
   "click", updateCart
  );
});

// This only defines updateCart function, but it does not run it!
function updateCart() {
  var request = new XMLHttpRequest();
  request.open('GET', 'cart.php', true);

  // This is an example of callback
  request.onload = function() {
    // This function runs once response has been received
    if (request.status >= 200 && request.status < 400) {
      document.querySelector("#shopping_cart").innerHTML =
        request.responseText;
    }
  };

  // This will only start the request
  request.send();
}

Form submission is fairly easy with vanilla JavaScript as well, of course jQuery's $.post is a bit simpler:

function removeItem() {
  console.info("Going to remove product with id from cart:", this.dataset.product_id);

  var formData = new FormData();
  formData.append("id", this.dataset.product_id);
  formData.append("count", -1);

  var request = new XMLHttpRequest();
  request.open('POST', 'cart.php', true);
  request.send(formData);
}

The dataset.product_id comes from the data- attributes, you can automatically generate these in cart.php:

<button
  data-product_id="1"
  onClick="removeItem">Remove item</button>

Look here for more information about what kind of API-s you can use from JavaScript.

Image gallery

This is another example which we are going to implement for practice. It's a web application for uploading images. The images are grouped into albums and users can like each other's uploads.

Database schema:

Views:

Main page highlighting latest 50 uploads (index.php)
User registration (reg.php)
Upload view for uploading images once user has logged in (upload.php)
User view listing their albums with thumbnails (user.php)
Album view listing thumbnails only of this album (album.php)
Image view with list of people who liked it (image.php)
Ajax loaded like section (like.php)

You also need:

js/main.js
css/style.css
config.php for database settings, don't commit it to Git
header.php
footer.php

Use HTML5 input widget for uploading images, this way you can easily take a photo with your smartphone:

<input id="file" type="file" accept="image/*">

Use ImageMagick's PHP bindings to create a thumbnail of the uploaded picture:

$im = new Imagick("uploads/original.jpg");
$im->thumbnailImage(50, 50);
$im->writeImage("thumbnails/smaller.jpg");

If you want to use ImageMagick on your own webserver, you might have to install the package yourself:

apt-get install php5-imagick

Use something like following to insert salted password hashes to the database:

$statement = $conn->prepare(
"insert into `lauri_gallery_user` (
    `email`,
    `password_salt`,
    `password_hash`)
values (?, ?, ?)");

// This will be random for every user registered
$salt = substr(str_shuffle(
    "0123456789abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ"), 0, 20);

$statement->bind_param("sss",
    $_POST["email"],
    $salt,
    sha1($salt . $_POST["password"]));

And to check credentials:

$statement = $conn->prepare("select * from lauri_gallery_user where email = ? and password_hash = SHA1(CONCAT(password_salt, ?))");

$statement->bind_param("ss", $_POST["email"], $_POST["password"]);

To force a "save as ..." dialog to appear for images you need to create a PHP file which spits out correct headers and then dumps the image file bytes:

header('Content-Disposition: attachment; filename="' . original_filename . '.jpg"');
readfile('uploads/' . original_file_hash . ".jpg");

Use following SQL join query to get two last uploaded images of a certain user:

select
    lauri_gallery_image.hash,
    lauri_gallery_image.created
from
    lauri_gallery_image
join
    lauri_gallery_album
on
    lauri_gallery_album.id = lauri_gallery_image.album_id
where
    lauri_gallery_album.owner_id = ?
order by
    lauri_gallery_image.created desc
limit 2

Extras

Adding foreign keys

First add index to the referring row (owner_id of the table album) and referred row (id of the user table) by clicking on the Index button in the table structure view:

Once you have enabled indexing on the referring column and also referred column you can associate them like this, click on Relation view in the table structure page:

Select referred table/column and set on delete trigger to cascade and on update trigger to none:

Web server configuration and SSL

This time we discussed a bit devops - how to set up web server on your machine to serve your web application.

You can use any Ubuntu/Debian based machine and simply do:

sudo apt-get install apache2 libapache2-mod-php5 php5-mysqlnd

After that you can simply place files under /var/www/html and point your web browser to the IP address of the machine, which in case of your laptop is http://localhost

The easiest way to set up and manage a MySQL database is by installing phpMyAdmin:

sudo apt-get install phpmyadmin mysql-server

Password for the root database user is asked, remember to save this for later! Open up http://localhost/phpmyadmin and log in with username root and the same password. Remember that MySQL root user is distinct from the operating system user root!

Katrin will show you how to install and manage Ubuntu in Operating systems course.

To tweak Apache configuration file following directories:

/etc/apache2/sites-enabled/
/etc/apache2/conf-enabled/
/etc/apache2/mods-enabled/

After tweaking you need to restart the server of course:

service apache2 restart

To fake a domain name on your personal machine you can modify the hosts file and then point your web browser to that file:

127.0.0.1 mycoolwebsite.com

Exercises, these you can do only on your personal machine at the moment:

Set up Apache 2 web server with two virtual hosts
Set up ID-card authentication for your website and attempt to grab the isikukood (national identification number) in the PHP code

real-time communications with nchan

This time we did some connection juggling with nchan, see demo here. Feel free to integrate it to your web shop customer support :)

Chatroom HTML:

<!DOCTYPE html>
<html>
  <head>
    <meta charset="utf-8"/>
    <title>This goes into the titlebar</title>
    <script type="text/javascript" src="js/main.js"></script>
  </head>
  <body>
    <h1>Chatroom</h1>
    <div>Messages go here</div>
    <input id="name" type="text" placeholder="Nick goes here"/>
    <input id="msg" type="text" placeholder="Your msg goes here"/>
    <button onClick="sendMessage();">Go!</button>
  </body>
</html>

Chatroom JavaScript:

// EventSource is sort of like a class
var source = new EventSource("http://push.koodur.com/ev/chatroom");

// Here we associate a function with the event of message coming in
source.onmessage = function(event) {
  console.log("Received server-sent event:", event.data);
  document.querySelector("div").innerHTML +=
      "<br/>" + event.data;
 
}

function sendMessage() {
  var request = new XMLHttpRequest();
  request.open('POST', 'http://push.koodur.com/pub?id=chatroom', true);
  var data = document.querySelector("#name").value + ": " +
	document.querySelector("#msg").value;
  console.info("About to send:", data);
  request.send(data);
}

Python client example:

import requests
# requests is performing HTTP request
r = requests.get("http://push.koodur.com/ev/chatroom",
	headers={"Accept": "text/event-stream"}, stream=True)

for line in r.iter_lines():
    print "Got line:", line

If you want to set up nchan server yourself the configuration in /etc/nginx/sites-enabled/blah

server {
    listen 80;
    listen 443 ssl;
    server_name push.koodur.com;

    location /pub {
        nchan_publisher http;
        #allow 185.94.112.76; # Whitelist this IP address for publishing
        nchan_channel_id $arg_id;
        add_header Access-Control-Allow-Origin http://enos.itcollege.ee;
    }

    location ~ "^/lp/(.*)" {
        nchan_subscriber longpoll;
        nchan_channel_id $1;
    }

    location ~ "^/ev/(.*)" {
        nchan_subscriber eventsource;
        nchan_channel_id $1;
    }
}

Lecture

Week 1 -- Introduction to HTML, MYSQL and PHP[1]

Week 2 -- Php Forms, Cookie and Session [2]

Week 3 -- CSS and Bootstrap Framework [3] [4]

Week 4 -- Object Oriented PHP [5]

Week 5 -- Logging in

Week 6 - SQL [6]

OOP Php Practise: [7]

Week 7 -- File Handling [8]

Lecture Video Here: [9]

Week 8 -- Javascript Javascript

Week 9 -- Web Configuration Apache Configuration

Week 10 -- Web Configuration Nginx Configuration Nchan [10]

Week 11 -- MVC (CakePHP) [11]

Requirements

Following are the requirements for the traditional+modern web application:

It doesn't look terrible a'la jurandi kodukas.
Makes use of WebFonts
Makes use of HTML5 input types
Makes use of CSS3 styling
Makes use of JavaScript additional user input validation.
Makes use of server side programming language (eg PHP, node.js, Python, Ruby)
Makes use of a database eg (MySQL, Postgres or NoSQL) in a safe manner, avoids SQL injections.
Mobile friendly, same web application has to work comfortably on desktop, phones and tablets as well.
Makes use of cookies for sessions.
Conforms to standards, use W3C Validation Service to check.
Can be used by visually impaired people, this usually means the web application has to be usable from a text-based web browser such as links, lynx or w3m.

Optional features

Estonian ID-card login
Social network login buttons
Use nchan or node.js etc for implementing real-time communication between the server and web browser using EventSource or WebSockets.

Deployment on school infrastructure

Traditional PHP+MySQL can be deployed on enos.itcollege.ee:

if you're using Windows computers at school simply place the PHP files under H:\public_html
if you're using Ubuntu computers at school simply place the PHP files under ~/Documents/public_html
if you're using Windows remotely use PuTTY, WinSCP or Swish to connect to enos.itcollege.ee with your school credentials
if you're using Ubuntu remotely, press Ctrl-L in file browser and enter sftp://user@enos.itcollege.ee/home/user/public_html

Use phpMyAdmin on http://enos.itcollege.ee/phpmyadmin to administer the database, the username is test and password is t3st3r123

This category currently contains no pages or media.

Category:I702 Web Application Programming: Difference between revisions

Latest revision as of 13:26, 9 June 2017

Contents

Intro

Class News

Web shop

Lecture/lab #1: Structuring HTML and making use of relational databases

Lecture/lab #2: Structuring PHP files and managing sessions

Lecture/lab #3

Lecture/lab #4: User registration

Lecture/lab #5: Logging in

Lecture/lab #6: SQL foreign keys and join queries

Lecture/lab #7: Uploading files

Lecture/lab #8: JavaScript and CSS selectors

Image gallery

Extras

Adding foreign keys

Web server configuration and SSL

real-time communications with nchan

Lecture

Requirements

Navigation menu

Page actions

Page actions

Personal tools

Navigation

Search

Tools

@@ Line 16: / Line 16: @@
 ** Pick a project idea from [https://wiki.itcollege.ee/index.php?title=Category:I703_Python Python] course page, there are several ideas which more or less constitute as web apps.
 ** Find an interesting web application that's participating on Google Summer of Code, get to know the community, prepare for participation on GSoC and successfully finish the GSoC.
-* If this is your first experience with this sort of stuff make sure you go [https://www.codecademy.com/learn/web HTML & CSS], [https://www.codecademy.com/learn/javascript JavaScript] and [https://www.codecademy.com/learn/jquery jQuery] tracks on CodeAcademy and start with simply creating your homepage :)
+* If this is your first experience with this sort of stuff make sure you go [https://www.codecademy.com/learn/web HTML & CSS], [https://www.codecademy.com/learn/javascript JavaScript], [https://www.codecademy.com/learn/jquery jQuery] and [https://www.codecademy.com/learn/learn-sql SQL] tracks on CodeAcademy and start with simply creating your homepage :)
 * For page layouts check [http://getbootstrap.com/getting-started/ Twitter Bootstrap]
+=Class News=
-==Lecture/lab #1==
+. The scheduled classes for 14/02/2017 and 21/02/2017 has been rescheduled for Thursday 23rd, February and Wednesday 1st, March. Kindly update you timetables. Sorry for the inconveniences this might cause.
+. Kindly send your lab github link to me on ladokunbolaji@gmail.com or bolaji.ladokun@itcollege.ee if you have not.
+. Progress Sheet [https://docs.google.com/spreadsheets/d/1V9lB2i_AoYFH3B-NKzq_vdlDbKe0o-lM9zx-XXy_WCE/edit?usp=sharing Progress]
+. Exam Date: 09.06.2017 14:00 - 15:30 Room: 319  Remake Exam: 15.06.2017 14:00 - 15:30  Room: 319
+. Exam 09.06.2017 https://drive.google.com/file/d/0B3D8AA6scdlEN2FTYWtIR296czA/view?usp=sharing
+=Web shop=
+==Lecture/lab #1: Structuring HTML and making use of relational databases==
 [https://echo360.e-ope.ee/ess/echo/presentation/ff4bec27-7c9e-4fc0-aa72-8d6d7a02999e Lecture recording #1], [https://echo360.e-ope.ee/ess/echo/presentation/661fcacf-9c90-490a-a582-f4a94c64f0b5 lecture recording #2]
-Use following as barebone for your PHP application:
+Use following as barebone for your PHP application, check [http://www.w3schools.com/html/html5_semantic_elements.asp here] for semantic tags of HTML5:
 <source lang="html5">
@@ Line 35: / Line 48: @@
      <link rel="css/style.css" type="text/css"/>
      <script type="text/javascript" src="js/main.js"></script>
+    <meta name="viewport" content="width=device-width, user-scalable=no"/><!-- Disable zoom on smartphone -->
    </head>
    <body>
-     <p>
+     <header>
+      Your shop name goes here
+    </header>
+    <nav>
+      Navigation links go here
+    </nav>
+    <section>
+      Product items go here
+    </section>
+    <article>
+      The actual content goes here
        <?php echo "This, is hellõu from PHP!"; ?>
-     </p>
+     </article>
+    <aside>
+      Context specific links go here
+    </aside>
+    <footer>
+      Footer goes here
+    </footer>
    </body>
 </html>
+</source>
+Use phpMyAdmin to create a table for your web shop's products:
+<source lang="php">
+CREATE TABLE IF NOT EXISTS `lauri_shop_product` (
+  `id` int(11) NOT NULL AUTO_INCREMENT,
+  `name` varchar(30) NOT NULL,
+  `description` text NOT NULL,
+  `price` decimal(10,2) NOT NULL,
+  PRIMARY KEY (`id`)
+);
 </source>
@@ Line 52: / Line 95: @@
      $conn = new mysqli("localhost", "test", "t3st3r123", "test");
      $results = $conn->query(
-       "SELECT id,name,price FROM lauri_products;");
+       "SELECT id,name,price FROM lauri_shop_product;");
      while ($row = $results->fetch_assoc()) {
@@ Line 79: / Line 122: @@
 $statement = $conn->prepare(
    "SELECT `name`, `description`, `price` FROM" .
-   " `lauri_products` WHERE `id` = ?");
+   " `lauri_shop_product` WHERE `id` = ?");
 $statement->bind_param("i", $_GET["id"]);
 $statement->execute();
@@ Line 102: / Line 145: @@
      $conn = new mysqli("localhost", "test", "t3st3r123", "test");
      $results = $conn->query(
-       "SELECT id,name,price FROM lauri_products;");
+       "SELECT id,name,price FROM lauri_shop_product;");
      $rows = $results->fetch_all(MYSQLI_ASSOC); // Pull ALL results
      $conn->close();
@@ Line 119: / Line 162: @@
 </source>
+==Lecture/lab #2: Structuring PHP files and managing sessions==
-==Lecture/lab #2==
 Place the header and footer in different files header.php and footer.php and include them like this:
@@ Line 202: / Line 243: @@
 Add .gitignore, README.md files. Check out if it looks more or less like [https://github.com/laurivosandi/yet-another-webshop/ this].
 ==Lecture/lab #3==
@@ Line 215: / Line 255: @@
-==Lecture/lab #4==
+==Lecture/lab #4: User registration==
 Set up database table for user accounts:
 <source lang="sql">
-CREATE TABLE IF NOT EXISTS `lauri_users` (
+CREATE TABLE IF NOT EXISTS `lauri_shop_user` (
    `id` int(11) NOT NULL AUTO_INCREMENT,
    `email` varchar(64) NOT NULL,
@@ Line 235: / Line 275: @@
    PRIMARY KEY (`id`),
    UNIQUE KEY `email` (`email`)
-)
+);
+</source>
+Form for entering the data:
+<source lang="html5">
+<form method="post" action="regsubmit.php">
+  <div>
+    <label for="email">E-mail</label>
+    <input type="email" name="email" required/>
+  </div>
+  <div>
+    <label for="password">Password</label>
+    <input type="password" name="password" required/>
+  </div>
+  <div>
+    <select name="country">
+      <option value="ee">Estonia</option>
+      <option value="lt">Latvia</option>
+      <option value="lv">Lithuania</option>
+    </select>
+  </div>
+  <div>
+    <label for="phone">Telephone number</label>
+    <input type="tel"/>
+  </div>
+  <div>
+    <label for="vatin">VAT indication number</label>
+    <input type="text" pattern="([A-Z0-9]{4,14})?$"/>
+  </div>
+  <div>
+    <label for="dob">Date of birth</label>
+    <input type="date" name="dob" placeholder="dd/mm/yyyy" required/>
+  </div>
+  <div>
+    <label for="first_name">First name</label>
+    <input type="text" name="first_name" required/>
+  </div>
+  <div>
+    <label for="last_name">Last name</label>
+    <input type="text" name="last_name" required/>
+  </div>
+  <div>
+    <input type="submit"/>
+  </div>
+</form>
+</source>
+PHP code for processing submission:
+<source lang="php">
+require_once "config.php";
+include "header.php";
+$conn = new mysqli(DB_SERVER, DB_USER, DB_PASS, DB_NAME);
+if ($conn->connect_error)
+  die("Connection to database failed:" .
+    $conn->connect_error);
+$conn->query("set names utf8");
+$statement = $conn->prepare(
+"INSERT INTO `lauri_shop_user` (
+    `email`,
+    `password`,
+    `first_name`,
+    `last_name`,
+    `phone`,
+    `dob`,
+    `salutation`,
+    `vatin`,
+    `company`,
+    `country`,
+    `address`)
+VALUES (?, PASSWORD(?), ?, ?, ?, ?, ?, ?, ?, ?, ?)");
+# whenever you get "call to a member function ... on a non-object" this means something
+# is failing **before** that line so you have to manually check for errors like this:
+if (!$statement) die("Prepare failed: (" . $conn->errno . ") " . $conn->error);
+$statement->bind_param("sssssssssss",
+    $_POST["email"],
+    $_POST["password"],
+    $_POST["first_name"],
+    $_POST["last_name"],
+    $_POST["phone"],
+    $_POST["dob"],
+    $_POST["salutation"],
+    $_POST["vatin"],
+    $_POST["company"],
+    $_POST["country"],
+    $_POST["address"]);
+if ($statement->execute()) {
+    echo "Registration was successful! <a href=\"index.php\">Back to main page</a>";
+} else {
+    if ($statement->errno == 1062) {
+       // This will result in 200 OK
+       echo "This e-mail is already registered";
+    } else {
+       // This will result in 500 Internal server error
+       die("Execute failed: (" .
+           $statement->errno . ") " . $statement->error);
+    }
+}
+</source>
+==Lecture/lab #5: Logging in==
+Login form for our web shop:
+<source lang="html5">
+<form action="login.php">
+  <input type="text" name="user"/>
+  <input type="password" name="password"/>
+  <input type="submit" value="Log in!"/>
+</form>
+</source>
+Create login.php:
+<source lang="php">
+// This is login.php, here we check if user provided proper credentials
+var_dump($_POST); // This is just to check that the data gets to server
+include "config.php";
+// This is copy-paste from description.php!
+$conn = new mysqli(DB_SERVER, DB_USER, DB_PASS, DB_NAME);
+if ($conn->connect_error)
+  die("Connection to database failed:" .
+    $conn->connect_error);
+$conn->query("set names utf8");
+$statement = $conn->prepare(
+"SELECT * FROM lauri_shop_user
+WHERE email = ? AND password = PASSWORD(?)"); // I PROMISE I WILL NEVER USE THIS IN PRODUCTION
+$statement->bind_param("ss", $_POST["user"], $_POST["password"]);
+$statement->execute();
+$results = $statement->get_result();
+$row = $results->fetch_assoc();
+if($row) {
+    echo "Login successful, hello " . $row["first_name"];
+    $_SESSION["user"] = $row["id"]; // This just stores user row number!
+} else {
+    echo "Login failed";
+}
+</source>
+Note that in production you should follow guidelines on storing passwords in safe manner, such as [https://en.wikipedia.org/wiki/PBKDF2 PBKDF2]!
+In index.php navigation bar:
+<source lang="php">
+if (array_key_exists("user", $_SESSION)) {
+   echo "Hello" . $_SESSION["user"];
+   // Put link to logout.php here
+} else {
+   // Show the login form above
+}
+</source>
+For logging out create another file logout.php:
+<source lang="php">
+session_start();
+unset($_SESSION["user"]);
+header("Location: index.php");
+</source>
+Exercises:
+* Create changepassword.php where use could change their password
+==Lecture/lab #6: SQL foreign keys and join queries==
+This time we added two database tables for storing orders.
+Database table for orders, again use phpMyAdmin to import it:
+<source lang="sql">
+CREATE TABLE IF NOT EXISTS `lauri_shop_order` (
+  `id` int(11) NOT NULL AUTO_INCREMENT,
+  `created` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
+  `shipped` timestamp NULL DEFAULT NULL,
+  `paid` timestamp NULL DEFAULT NULL,
+  `shipping_address` text,
+  `user_id` int(11) NOT NULL,
+  PRIMARY KEY (`id`)
+);
+</source>
+Also add many to many relationship between order and products belonging to a particular order:
+<source lang="sql">
+CREATE TABLE IF NOT EXISTS `lauri_shop_order_item` (
+  `id` int(11) NOT NULL AUTO_INCREMENT,
+  `order_id` int(11) NOT NULL,
+  `product_id` int(11) NOT NULL,
+  `count` int(11) NOT NULL,
+  `unit_price` decimal(10,2) NOT NULL,
+  PRIMARY KEY (`id`)
+);
 </source>
+Populate both tables with some arbitrary data and verify you get sensible output with SQL snippets below.
-==Lecture/lab #5==
+Following SQL query lists orders belongin to a certain user:
-This time we did some connection juggling with nchan
+<source lang="sql">
+select
+  `lauri_shop_order`.`id` as `order_id`,
+  `lauri_shop_order`.`shipping_address` as `shipping_address`,
+  `lauri_shop_order`.`created` as `created`
+from
+  `lauri_shop_order`
+where
+  `lauri_shop_order`.`user_id` = 2
+</source>
+Following SQL query  [https://en.wikipedia.org/wiki/Join_(SQL) joins] data from two tables to generate list of items belonging to a certain order:
+<source lang="sql">
+select
+  -- The format here is `table_name`.`column_name` as `Whatever you want to call it`
+  `lauri_shop_order_item`.`id` as `order_item_id`,
+  `lauri_shop_order_item`.`product_id` as `product_id`,
+  `lauri_shop_product`.`name` as `product_name`,
+  `lauri_shop_order_item`.`unit_price` as `order_item_unit_price`,
+  `lauri_shop_order_item`.`count` as `order_item_count`,
+  `lauri_shop_order_item`.`unit_price` * `lauri_shop_order_item`.`count` as `subtotal`
+from
+  `lauri_shop_order_item`
+join
+  `lauri_shop_product`
+on
+  -- We select all rows from lauri_shop_order_item table and then
+  -- try to find a matching row based on following condition
+  `lauri_shop_order_item`.`product_id` = `lauri_shop_product`.`id`
+where
+  `lauri_shop_order_item`.`order_id` = 1
+</source>
+Add [https://en.wikipedia.org/wiki/Database_index database indexes] so we could rapidly search by these columns:
+<source lang="sql">
+ALTER TABLE `lauri_shop_order` ADD INDEX (`user_id`);
+ALTER TABLE `lauri_shop_order_item` ADD INDEX (`product_id`);
+ALTER TABLE `lauri_shop_order_item` ADD INDEX (`order_id`);
+</source>
+Add [https://en.wikipedia.org/wiki/Foreign_key foreign key] constraints to bind one table to another:
+<br>''Important note: the foreign keys fields must be indexed and the constraint name must be unique throughout the whole database (otherwise a "check data type" error occurs).''
+<source lang="sql">
+ALTER TABLE `lauri_shop_order`
+  ADD CONSTRAINT `user_fk` FOREIGN KEY (`user_id`) REFERENCES `lauri_shop_user` (`id`) ON DELETE CASCADE ON UPDATE NO ACTION;
+ALTER TABLE `lauri_shop_order_item`
+  ADD CONSTRAINT `product_fk` FOREIGN KEY (`product_id`) REFERENCES `lauri_shop_product` (`id`) ON DELETE CASCADE ON UPDATE NO ACTION,
+  ADD CONSTRAINT `order_fk` FOREIGN KEY (`order_id`) REFERENCES `lauri_shop_order` (`id`) ON DELETE CASCADE ON UPDATE NO ACTION;
+</source>
+You should end up with a database schema like this:
+[[File:Webshop-database-schema-1.png]]
+Exercises:
+* Add orders.php for showing a list of orders belonging to a certain user. Copy boilerplate code from description.php.
+* Add orderdetail.php for listing items in a particular order. Use GET parameter 'id' to specify order id. Copy boilerplate code from description.php.
+* Split your CSS files to three: common design, design for displays and design for printers, check out page source of [https://www.koodur.com/invoice/ this].
+==Lecture/lab #7: Uploading files==
+We basically have two options for uploading files to server:
+* Use BLOB datatype for the column
+* Store filename in varchar field and move the file to an uploads directory
+This is an example implementation for the latter. It calculates the SHA1 checksum of the file and appends the original file name extension. This way the files are deduplicated as well :)
+<source lang="html5">
+<html>
+  <body>
+    <form method="post" enctype="multipart/form-data">
+      <input type="text" name="product_title"/>
+      <input type="text" name="product_description"/>
+      <input type="file" name="product_image" required/>
+      <input type="file" name="product_thumbnail"/>
+      <input type="submit"/>
+    </form>
+    <!-- You also need:
+    mkdir uploads
+    chmod 777
+    -->
+    <?php
+    if (array_key_exists("product_image", $_FILES)) {
+      $finfo = finfo_open(FILEINFO_MIME_TYPE);
+      $mimetype = finfo_file($finfo, $_FILES["product_image"]["tmp_name"]);
+      if ($mimetype != "application/pdf") die("Go away!");
+      $checksum = sha1(file_get_contents(
+        $_FILES["product_image"]["tmp_name"])) . "." .
+        pathinfo($_FILES["product_image"]["name"], PATHINFO_EXTENSION);
+      if (!file_exists("uploads/" . $checksum)) {
+        copy(
+          $_FILES["product_image"]["tmp_name"],
+          "uploads/" . $checksum);
+      }
+    }
+    ?>
+    <p>Mimetype was: <?= $mimetype; ?></p>
+    <p>Checksum was: <a href="uploads/<?=$checksum;?>"><?=$checksum;?></a>
+    <p>Filename was: <?=$_FILES["product_image"]["name"];?></p>
+    <p>File stored at: <?=$_FILES["product_image"]["tmp_name"];?></p>
+  </body>
+</html>
+</source>
+==Lecture/lab #8: JavaScript and CSS selectors==
+This will simply load the file from given URL and place the response into #content
+<source lang="javascript">
+// In HTML we only have <button id="update_cart">Update cart</button> to trigger update
+// and <div id="shopping_cart">Initially empty</div> for placing the shopping cart in the webpage
+// Wait page to be loaded and then associate click event
+document.addEventListener("DOMContentLoaded", function() {
+  document.querySelector("#update_cart").addEventListener(
+   "click", updateCart
+  );
+});
+// This only defines updateCart function, but it does not run it!
+function updateCart() {
+  var request = new XMLHttpRequest();
+  request.open('GET', 'cart.php', true);
+  // This is an example of callback
+  request.onload = function() {
+    // This function runs once response has been received
+    if (request.status >= 200 && request.status < 400) {
+      document.querySelector("#shopping_cart").innerHTML =
+        request.responseText;
+    }
+  };
+  // This will only start the request
+  request.send();
+}
+</source>
+Form submission is fairly easy with vanilla JavaScript as well, of course jQuery's $.post is a bit simpler:
+<source lang="javascript">
+function removeItem() {
+  console.info("Going to remove product with id from cart:", this.dataset.product_id);
+  var formData = new FormData();
+  formData.append("id", this.dataset.product_id);
+  formData.append("count", -1);
+  var request = new XMLHttpRequest();
+  request.open('POST', 'cart.php', true);
+  request.send(formData);
+}
+</source>
+The dataset.product_id comes from the data- attributes, you can automatically generate these in cart.php:
+<source lang="html5">
+<button
+  data-product_id="1"
+  onClick="removeItem">Remove item</button>
+</source>
+Look [https://www.w3.org/standards/techs/js here] for more information about what kind of API-s you can use from JavaScript.
+=Image gallery=
+This is another example which we are going to implement for practice. It's a web application for uploading images. The images are grouped into albums and users can like each other's uploads.
+Database schema:
+[[File:image-gallery-schema.png]]
+Views:
+* Main page highlighting latest 50 uploads (index.php)
+* User registration (reg.php)
+* Upload view for uploading images once user has logged in (upload.php)
+* User view listing their albums with thumbnails (user.php)
+* Album view listing thumbnails only of this album (album.php)
+* Image view with list of people who liked it (image.php)
+* Ajax loaded like section (like.php)
+You also need:
+* js/main.js
+* css/style.css
+* config.php for database settings, don't commit it to Git
+* header.php
+* footer.php
+Use [http://code.hootsuite.com/html5/ HTML5 input widget] for uploading images, this way you can easily take a photo with your smartphone:
+<source lang="html5">
+<input id="file" type="file" accept="image/*">
+</source>
+Use [http://php.net/manual/en/imagick.examples-1.php ImageMagick's PHP bindings] to create a thumbnail of the uploaded picture:
+<source lang="php">
+$im = new Imagick("uploads/original.jpg");
+$im->thumbnailImage(50, 50);
+$im->writeImage("thumbnails/smaller.jpg");
+</source>
+If you want to use ImageMagick on your own webserver, you might have to install the package yourself:
+<source lang="bash">
+apt-get install php5-imagick
+</source>
+Use something like following to insert salted password hashes to the database:
+<source lang="php">
+$statement = $conn->prepare(
+"insert into `lauri_gallery_user` (
+    `email`,
+    `password_salt`,
+    `password_hash`)
+values (?, ?, ?)");
+// This will be random for every user registered
+$salt = substr(str_shuffle(
+    "0123456789abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ"), 0, 20);
+$statement->bind_param("sss",
+    $_POST["email"],
+    $salt,
+    sha1($salt . $_POST["password"]));
+</source>
+And to check credentials:
+<source lang="php">
+$statement = $conn->prepare("select * from lauri_gallery_user where email = ? and password_hash = SHA1(CONCAT(password_salt, ?))");
+$statement->bind_param("ss", $_POST["email"], $_POST["password"]);
+</source>
+To force a "save as ..." dialog to appear for images you need to create a PHP file which spits out correct headers and then dumps the image file bytes:
+<source lang="php">
+header('Content-Disposition: attachment; filename="' . original_filename . '.jpg"');
+readfile('uploads/' . original_file_hash . ".jpg");
+</source>
+Use following SQL join query to get two last uploaded images of a certain user:
+<source lang="sql">
+select
+    lauri_gallery_image.hash,
+    lauri_gallery_image.created
+from
+    lauri_gallery_image
+join
+    lauri_gallery_album
+on
+    lauri_gallery_album.id = lauri_gallery_image.album_id
+where
+    lauri_gallery_album.owner_id = ?
+order by
+    lauri_gallery_image.created desc
+limit 2
+</source>
+=Extras=
+==Adding foreign keys==
+First add index to the referring row (owner_id of the table album) and referred row (id of the user table) by clicking on the Index button in the table structure view:
+[[File:Phpmyadmin-add-index.png]]
+Once you have enabled indexing on the referring column and also referred column you can associate them like this, click on Relation view in the table structure page:
+[[File:Phpmyadmin-relation-view.png]]
+Select referred table/column and set on delete trigger to cascade and on update trigger to none:
+[[File:Phpmyadmin-adding-foreign-key-constraints.png]]
+==Web server configuration and SSL==
+This time we discussed a bit devops - how to set up web server on your machine to serve your web application.
+You can use any Ubuntu/Debian based machine and simply do:
+<source lang="bash">
+sudo apt-get install apache2 libapache2-mod-php5 php5-mysqlnd
+</source>
+After that you can simply place files under /var/www/html and point your web browser to the IP address of the machine, which in case of your laptop is http://localhost
+The easiest way to set up and manage a MySQL database is by installing phpMyAdmin:
+<source lang="bash">
+sudo apt-get install phpmyadmin mysql-server
+</source>
+Password for the root database user is asked, remember to save this for later! Open up http://localhost/phpmyadmin and log in with username root and the same password. Remember that MySQL root user is distinct from the operating system user root!
+Katrin will show you how to install and manage Ubuntu in [https://wiki.itcollege.ee/index.php/Operating_systems Operating systems] course.
+To tweak Apache configuration file following directories:
+<source lang="bash">
+/etc/apache2/sites-enabled/
+/etc/apache2/conf-enabled/
+/etc/apache2/mods-enabled/
+</source>
+After tweaking you need to restart the server of course:
+<source lang="bash">
+service apache2 restart
+</source>
+To fake a domain name on your personal machine you can modify the hosts file and then point your web browser to that file:
+<source lang="bash">
+.0.0.1 mycoolwebsite.com
+</source>
+Exercises, these you can do only on your personal machine at the moment:
+* Set up Apache 2 web server with two virtual hosts
+* Set up [https://eid.eesti.ee/index.php/Authenticating_in_web_applications ID-card authentication] for your website and attempt to grab the isikukood (national identification number) in the PHP code
+==real-time communications with nchan==
+This time we did some connection juggling with nchan, see demo [http://enos.itcollege.ee/~lvosandi/chat/ here]. Feel free to integrate it to your web shop customer support :)
 Chatroom HTML:
@@ Line 301: / Line 895: @@
 </source>
-Login form for our web shop:
+If you want to set up nchan server yourself the configuration in /etc/nginx/sites-enabled/blah
+<source lang="nginx">
+server {
+    listen 80;
+    listen 443 ssl;
+    server_name push.koodur.com;
+    location /pub {
+        nchan_publisher http;
+        #allow 185.94.112.76; # Whitelist this IP address for publishing
+        nchan_channel_id $arg_id;
+        add_header Access-Control-Allow-Origin http://enos.itcollege.ee;
+    }
+    location ~ "^/lp/(.*)" {
+        nchan_subscriber longpoll;
+        nchan_channel_id $1;
+    }
-<source lang="html5">
+    location ~ "^/ev/(.*)" {
-<form action="login.php">
+        nchan_subscriber eventsource;
-  <input type="text" name="user"/>
+        nchan_channel_id $1;
-  <input type="password" name="password"/>
+    }
-  <input type="submit" value="Log in!"/>
+}
-</form>
 </source>
+=Lecture=
+Week 1 -- Introduction to HTML, MYSQL and PHP[https://docs.google.com/presentation/d/1G8bmmPxPAuwjJXNUeBID3NTSM9JOaMQkRci0s-VR6pY/edit?usp=sharing]
+Week 2 -- Php Forms, Cookie and Session [https://docs.google.com/presentation/d/1ImwUXQK29lVy0cpiIECS3Krsa7FxgXptbzFkaHRKJgw/edit?usp=sharing]
+Week 3 -- CSS and Bootstrap Framework [https://drive.google.com/file/d/0B3D8AA6scdlEMXd3enE4REhPbEk/view?usp=sharing] [https://drive.google.com/open?id=0B3D8AA6scdlEbGhzYlViaEJjMDQ]
+Week 4 -- Object Oriented PHP [http://www.killerphp.com/downloads/object-oriented-php/downloads/oop_in_php_tutorial.pdf]
+Week 5 -- Logging in
+Week 6 - SQL [http://www.tutorialspoint.com/sql/sql_tutorial.pdf]
+OOP Php Practise: [http://www.w3resource.com/php-exercises/php-class-exercises.php]
+Week 7 -- File Handling [https://docs.google.com/presentation/d/1J601PIPXFj7dDZNMUMiEt9KJY7NWVGXyMQIXCqt8Dps/edit?usp=sharing]
+Lecture Video Here: [https://echo360.e-ope.ee/ess/portal/section/4f2a56ad-86d2-4124-a12a-a123e9bf6222]
+Week 8 -- Javascript [https://docs.google.com/presentation/d/1ZZGFv-39bxQuTeqg8rz30IIHnLuXuLaP_HyxRsXE_j4/edit?usp=sharing Javascript]
+Week 9 -- Web Configuration [https://drive.google.com/open?id=0B3D8AA6scdlEOUFWWk9JamVRd1U Apache Configuration]
+Week 10 -- Web Configuration [https://drive.google.com/open?id=0B3D8AA6scdlESmVudDVqM0o5M3M Nginx Configuration] Nchan [https://nchan.io/]
+Week 11 -- MVC (CakePHP) [https://drive.google.com/open?id=0B3D8AA6scdlEZjYxM3B1cVNmMDg]
 =Requirements=
@@ Line 350: / Line 988: @@
 Use phpMyAdmin on http://enos.itcollege.ee/phpmyadmin to administer the database, the username is test and password is t3st3r123
-Deployment on personal Ubuntu machine
--------------------------------------
-You can use any Ubuntu/Debian based machine and simply do:
-<source lang="bash">
-sudo apt-get install apache2 libapache2-mod-php5 php5-mysqlnd
-</source>
-After that you can simply place files under /var/www/html and point your web browser to the IP address of the machine, which in case of your laptop is http://localhost
-The easiest way to set up and manage a MySQL database is by installing phpMyAdmin:
-<source lang="bash">
-sudo apt-get install phpmyadmin mysql-server
-</source>
-Password for the root database user is asked, remember to save this for later! Open up http://localhost/phpmyadmin and log in with username root and the same password. Remember that MySQL root user is distinct from the operating system user root!
-Katrin will show you how to install and manage Ubuntu in [https://wiki.itcollege.ee/index.php/Operating_systems Operating systems] course.