COAP:COAP-3110/week2

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

Create a table for your homework project

• I suggest taking the quiz/test from hw1
• Please add a text field where users can enter a name

Tuesday

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

Homework 2

• Create a PHP/MySQL web application that allow users to enter and retrieve data.
• You can expand hw1 for that.