COAP:COAP-3110/week2: Difference between revisions

Latest revision as of 17:55, 27 September 2016

Week 2 learning goals

Create SQL databases
Become familiar the phpmyadmin web-base client
Create tables with columns that include either numbers or text
Create 2 relational tables
Be able to insert and retrieve data from a simple table with PHP/mysli

Monday

A short introduction to SQL

The instructor will do a live demo with PHPMyAdmin using the tables from the example below....

In XAMPP Control Panel, start mysql, then click on Admin (You may have to fix the port, e.g. http://localhost:8888/phpmyadmin/
He also will use SQL and MySQL tutorial (may need some updating)

Example database and table

Please create a database and the following tables in your database server.

Open PHPMyAdmin
Click on the SQL TAB
Copy paste from below (you also could import a file)

-- Select the database

CREATE DATABASE IF NOT EXISTS demo;

USE demo;

-- Simple barebones table, using default settings of the database

DROP TABLE IF EXISTS demo1;
CREATE TABLE demo1 (
  id int(10) NOT NULL auto_increment,
  login varchar(10) NOT NULL default '',
  password varchar(100) default NULL,
  fullname varchar(40) NOT NULL default '',
  url varchar(60) NOT NULL default '',
  food int(11) NOT NULL default '0',
  work int(11) NOT NULL default '0',
  love int(11) NOT NULL default '0',
  leisure int(11) NOT NULL default '0',
  sports int(11) NOT NULL default '0',
  PRIMARY KEY  (id),
  KEY login (login)
);

INSERT INTO demo1 VALUES (NULL,'colin', 'b9hhhfa9347all893u483', 'Patrick Hero','http://tecfa.unige.ch/',1,2,1,3,4);
INSERT INTO demo1 VALUES (NULL,'colin2', 'b9hhhfa9347all893u483', 'Patrick AntiHero','http://tecfa.unige.ch/',5,2,1,3,4);
INSERT INTO demo1 VALUES (NULL,'user12','098f6bcd4621d373cade4e832627b4f6','Testuser','www.mysql.com',1,4,5,2,1);

-- 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 varchar(40) NOT NULL DEFAULT '',
  first_name varchar(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 varchar(40) NOT NULL DEFAULT '',
  student_id int(10) NOT NULL,
  comments varchar(128) DEFAULT NULL,
  url varchar(60) NOT NULL DEFAULT '',
  PRIMARY KEY (id),
  KEY student_id (student_id),
  FOREIGN KEY (student_id) REFERENCES student(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
 
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/');

Hands on PHPMyAdmin

Manipulate data

Insert one more entry in the demo1 table
Change the contents of some entry (that could be a dangerous thing to do in some portal applications...)

Change the structure of table

Change a datatype from VARCHAR to TEXT

Create a table

Create a new table called demo2
Add a column

Dump a table

Dump the SQL for the demo1 table

Add a user and password for data base demo

In PHPMyAdmin, select the demo database
Click on the Privileges tab
Create a user called demouser with a password that you remember.
- The user should be able to connect from localhost only and have all privileges for the demo database (this is the ticked default).
- Do not give out any permission for other databases !!

Monday Part II

Hands on: Create a table for your homework project

I suggest taking the quiz/test from hw1
Please, also add a text field where users can enter a name
Have a look at homework 2 below, before you start.

Wednesday

Create PHP code to insert and retrieve data from your database table

PHP Code to adapt to your needs

Also available as Zip in the world classroom (mysql-simple.zip)

File main.html

<!DOCTYPE html>
<html>
  <head>
    <title>PHP/MySQL Demo</title>
    <!-- Created by: D.K.S., 19-Apr-1998 -->
    <!-- Changed by: D.K.S., 19-Apr-1998 -->
    <!-- Changed by: D.K.S., Aug-2016, updated to PHP 7 -->
    <meta charset="UTF-8">
  </head>
  <body>
    <h1>PHP/MySQL Demo - Dump Database Contents</h1>

This directory contains a very simple database application. It will teach
you both PHP and MySql basics. We do not suggest to take up from here,
because code lacks abstraction and security !
<p>
  <ul>
    <li><a href="insert-entry.php">add a new entry</a></li>

    <li><a href="dump_results_demo.php">dump the full database</a></li>
  </ul>


<hr>
    <address>
      <a id="signature"
	 href="http://tecfa.unige.ch/tecfa-people/schneider.html">d.k.s.</a>
    </address>
  </body>
</html>

File dump_results_demo.php

<!DOCTYPE html>
<html>
  <head>
    <title>PHP/MySQL Demo - Dump Database Contents)</TITLE>
    <!-- Created by: D.K.S., 19-Apr-1998 -->
    <!-- Changed by: D.K.S., 19-Apr-1998 -->
    <!-- Changed by: D.K.S., Aug-2016, updated to PHP 7 -->
    <meta charset="UTF-8">
  </head>
  <body>
    <h1>PHP/MySQL Demo - Dump Database Contents</h1>

<?php 

error_reporting(E_ALL);

// Connection to the database

$db = new mysqli ( "localhost", "demouser", "good", "demo") ;

if ($db->connect_errno) {
     printf("Connect failed: %s\n", $db->connect_error);
     exit();						      
}

// Request

$SQL =  "SELECT * from demo1";
$result  =  $db->query( $SQL ); 
if (!$result) die($db->error);

echo '<table border="1"> <tr>';

// ---- print the header line, extract the name from each field

while  ($field = $result->fetch_field() )  { 
  echo  "<th>"; 
  echo  "$field->name"; 
  echo  "</th>"; 
}
echo  "</tr>";


// ----- print the table fields
// fetch row by row until there is none left
while ($row = $result->fetch_array(MYSQLI_NUM)) {
    echo  "<tr>"; 
    for  ($i=0;  $i<sizeof($row);  $i++)  { 
	echo  "<td>"; 
	echo  "$row[$i]"; 
	echo  "</td>"; 
    } 
    echo  "</tr>\n"; 
}
       
echo  "</table>"; 

// close the connection
$db->close();
?> 

<hr>
    <address>
      <a id="signature"
	 href="http://tecfa.unige.ch/tecfa-people/schneider.html">D.K.S.</a>
    </address>
  </body>
</html>

File insert-entry.php

<!DOCTYPE html>
<html>
  <head>
     <title>PHP/MySQL Demo - Insert Entry Feedback</title>
     <!-- Created by: D.K.S., 19-Apr-1998 -->
     <!-- Changed by: OC., 18-Jun-2002 (php4.2 compat) -->
     <!-- Changed by: DKS. August 2016 for mysqli/PHP7 -->
     <meta charset="UTF-8">
  </head>
  <body>
<?php
error_reporting(E_ALL);

// Check if should display the form or deal with POST data
if (!$_POST) {
?>
<h1>Php/mysql demo - new entry</h1>

// include the navigation bar
include("navigation.php");

Please fill in all the following fields

<form method="post" action="insert-entry.php">
<center>

<table>
<tr>
<td align=right>login:</td>
<td><input type="text" name="login" size="10" maxlength="10"></td>
</tr>

<tr>
<td align=right>password:</td>
<td><input type="text" name="password" size="10" maxlength="10"></td>
</tr>

<tr>
<td align=right>first name + last name:</td>
<td><input type="text" name="fullname" size="40" maxlength="40"></td>
</tr>

<tr>
<td align=right>url of home page:</td>
<td><input type="text" name="url" size="60" maxlength="60"></td>
</tr>

Generate the various scales ... just to show off some php
Change the list of scales below, elements need to be SIMPLE strings
 */
<?php
foreach (array("food", "work", "love", "leisure", "sports") as $name) {
   echo "<tr> <td align=right>importance of <strong>$name</strong>:</td>";
   echo "<td><select name=$name>";
   echo "<option value=1>1 - totally unimportant";
   echo "<option value=2>2 - not important";
   echo "<option value=3 selected>3 - rather not important";
   echo "<option value=4>4 - slightly important";
   echo "<option value=5>5 - rather important";
   echo "<option value=6>6 - very important";
   echo "</select>";
   echo "</td></tr>";
   }
?>

</table></center>
<p align="center">
<input type=submit value ="submit">
<input type=reset value ="reset">
</p>
</form>

<?php
}
// code below will catch a post request from the form above
else {

echo "<h1>Php/mysql demo - Thanks for the addition</h1>";

// get vars from the form
$login = $_POST['login'];
$password = $_POST['password'];
$fullname = $_POST['fullname'];
$url = $_POST['url'];
$food = $_POST['food'];
$work = $_POST['work'];
$love = $_POST['love'];
$leisure = $_POST['leisure'];
$sports = $_POST['sports'];

// include the navigation bar
include("navigation.php");

/* Sanitize input, must be done at least for all text input */

/* sanitize text inputs is MISSING. 

This code remains open for JavaScript and SQL injection. Can you find a solution on the Internet ? E.g. in the php online manual (filter_var) or on Stackoverflow ?

*/

// Remove all illegal characters from a url
$url = filter_var($url, FILTER_SANITIZE_URL);

// Encode password
$test_password=md5($password);

$db = new mysqli ( "localhost", "demouser", "good", "demo") or  die ( "Unable  to  connect  to  SQL  server or to database");
  
// try to insert the values entered by the user into the database
// if the insert succeeds , $result is true, else it is false

$result = $db->query ("INSERT INTO demo1 (login, password, fullname, url, food, work, love, leisure, sports) VALUES ('$login', '$test_password', '$fullname', '$url', '$food', '$work', '$love', '$leisure', '$sports')");

if ($result) {    
echo "<p>Your data (login='$login', test='$test_password', fullname='$fullname', url='$url', food='$food', work='$work', love='$love', leisure='$leisure', sports='$sports') have successfully been registered. Please remember your password: <STRONG>$password</STRONG>";
      }
      else {
      echo "<p>Too bad, the db did not want your data.";
      }
      }
?>
    
<hr>
    <address>
      <a id="Signature"
	 href="http://tecfa.unige.ch/DKS/">D.K.S.</a>, August 2016
    </address>
  </body>
</html>

Homework 2

(1) Create a PHP/MySQL web application that allow users to enter and retrieve data. Data must include both numbers and (short) text.

Create an HTML form that allows entering the data which then is stored in a MSQL table. You can use an HTML plus PHP page or a single PHP page for that.
Create a PHP page that will dump the data

Bonus: Add some extra display feature, e.g. a listing by criteria.

(2) Write a report

It should include your application's purpose, 2 screenshots of data entry and display, a short implementation notice

Use of prior work

You can reuse work done for hw1 as much as you like

Due:
Wednesday week 3 before class

Teaching materials

See PDFs in the worldclassroom
Example code is also in the mysql-simple zip file (world classroom)
SQL and MySQL tutorial (lecture notes)