PHP Booosta 3 Tutorial

Planning your application

First you have to plan you application. What does it have to do? Which data should be stored?
In this tutorial we will create a web application that administrates a small colleage that offers courses to its students. In this application the students are the users and the management of the colleage are the admin users. The application manages courses, lectors and registrations of students for courses.

Creating the database

For database creation you can use tools like phpMyAdmin or you type in the create commands on the SQL command line. For this tutorial we create 3 tables in our database: lecturer, course and registration:

CREATE TABLE IF NOT EXISTS `lecturer` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  `birthdate` date DEFAULT NULL,
  `gender` enum('m','f') NOT NULL,
  `comment` text,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 ;

CREATE TABLE IF NOT EXISTS `course` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  `lecturer` int(11) NOT NULL,
  `starttime` datetime NOT NULL,
  `endtime` datetime NOT NULL,
  `description` text,
  PRIMARY KEY (`id`),
  KEY `lecturer` (`lecturer`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 ;

CREATE TABLE IF NOT EXISTS `registration` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `course` int(11) NOT NULL,
  `user` int(11) NOT NULL,
  `regdate` date NOT NULL,
  `grade` enum('Excellent','Good','Average','Deficient','Failing','Not attended') NOT NULL,
  PRIMARY KEY (`id`),
  KEY `course` (`course`),
  KEY `user` (`user`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 ;

Now we add some foreign keys to our tables:

ALTER TABLE `registration`
  ADD CONSTRAINT `registration_ibfk_1` FOREIGN KEY (`course`) REFERENCES `course` (`id`) ON DELETE CASCADE,
  ADD CONSTRAINT `registration_ibfk_2` FOREIGN KEY (`user`) REFERENCES `user` (`id`) ON DELETE CASCADE;

ALTER TABLE `course`
  ADD CONSTRAINT `course_ibfk_1` FOREIGN KEY (`lecturer`) REFERENCES `lecturer` (`id`);

Previous - Next