Wrox Programmer Forums
Go Back   Wrox Programmer Forums > PHP/MySQL > BOOK: Professional PHP6
|
BOOK: Professional PHP6
This is the forum to discuss the Wrox book Professional PHP6 by Edward Lecky-Thompson, Steven Nowicki; ISBN: 9780470395097
Welcome to the p2p.wrox.com Forums.

You are currently viewing the BOOK: Professional PHP6 section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old April 4th, 2013, 11:27 AM
Authorized User
 
Join Date: Sep 2012
Posts: 13
Thanks: 3
Thanked 1 Time in 1 Post
Default MySQL SQL For Chapter 05 Student Course Example

I have re-created the code from the student course example from chapter 5.

Code:
-- ------------------------------------------------------
-- Author: Eric Jones <eric@dowridgestudio.com>
-- Date: 2013-04-04
-- Purpose: Wrox Professional PHP 6 uses postgreSQL for
-- the examples in the book.  Here I have re-created
-- the SQL from the book for MySQL 5.5
-- 
-- This if for the examples provided in Chapter 5

-- ------------------------------------------------------
-- --- USE AT YOUR OWN RISK ----- I HOLD NO LIABILITY ---
-- ------------------------------------------------------

-- ------------------------------
-- Drop the database
--
-- Uncomment the statement below
-- to complete drop the database
-- and re-create it.
-- -------------------------------

# DROP DATABASE IF EXISTS `proPHP6`;

-- create the database

CREATE DATABASE IF NOT EXISTS `proPHP6` CHARACTER SET utf8 COLLATE utf8_general_ci;

-- switch to using the newly created database

USE `proPHP6`;

-- student table

CREATE TABLE IF NOT EXISTS `student` (
  `studentid` int(11)     unsigned NOT NULL AUTO_INCREMENT,
  `name`      varchar(45),
  PRIMARY KEY (`studentid`)
) ENGINE=InnoDB CHARSET=utf8;

-- course table

CREATE TABLE IF NOT EXISTS `course` (
  `courseid` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `coursecode` varchar(10),
  `name` varchar(255),
  PRIMARY KEY (`courseid`)
) ENGINE=InnoDB CHARSET=utf8;

-- studentcourse

CREATE TABLE IF NOT EXISTS `studentcourse` (
  `studentid` int(10) unsigned NOT NULL,
  `courseid` int(10) unsigned NOT NULL,
  UNIQUE KEY `idx_studentcourse_unique` (`studentid`,`courseid`),
  KEY `fk_studentcourse_1_idx` (`studentid`),
  KEY `fk_studentcourse_2_idx` (`courseid`),
  CONSTRAINT `fk_studentcourse_1` 
	FOREIGN KEY (`studentid`) 
	REFERENCES `student` (`studentid`) 
		ON DELETE NO ACTION 
		ON UPDATE NO ACTION,
  CONSTRAINT `fk_studentcourse_2` 
	FOREIGN KEY (`courseid`) 
	REFERENCES `course` (`courseid`) 
		ON DELETE NO ACTION 
		ON UPDATE NO ACTION
) ENGINE=InnoDB CHARSET=utf8;

-- ---------------------------------------------
-- The tables that you add this data into should
-- be empty.  If they are not, the final block
-- of insert statements may not work as expected.
-- ----------------------------------------------

-- ----------------------------------------------
-- The statements below will empty the tables
-- of all data so that the data can be inserted
-- and work as expected.
-- 
-- Uncomment if needed.
-- ----------------------------------------------

# TRUNCATE TABLE `studentcourse`;
# TRUNCATE TABLE `course`;
# TRUNCATE TABLE `student`;

-- ------------------------------
-- student data
-- ------------------------------

-- studentid 1

INSERT INTO `student`(name) VALUES ('Bob Smith');

-- studentid 2

INSERT INTO `student`(name) VALUES ('John Doe');

-- studentid 3

INSERT INTO `student`(name) VALUES ('Jane Baker');

-- ------------------------------
-- course data
-- ------------------------------

-- courseid 1

INSERT INTO `course`(`coursecode`, `name`) VALUES ('CS101', 'Intro to Computer Science');

-- courseid 2

INSERT INTO `course`(`coursecode`, `name`) VALUES ('HIST369', 'British History 1945-1990');

-- courseid 3

INSERT INTO `course`(`coursecode`, `name`) VALUES ('BIO546', 'Advanced Genetics');

-- ------------------------------
-- studentcourse data
-- ------------------------------

-- NOTE: This will work only once the above insert statements have run.

INSERT INTO `studentcourse`(`studentid`,`courseid`) VALUES (1,1);
INSERT INTO `studentcourse`(`studentid`,`courseid`) VALUES (1,2);
INSERT INTO `studentcourse`(`studentid`,`courseid`) VALUES (1,3);
INSERT INTO `studentcourse`(`studentid`,`courseid`) VALUES (2,1);
INSERT INTO `studentcourse`(`studentid`,`courseid`) VALUES (2,3);
INSERT INTO `studentcourse`(`studentid`,`courseid`) VALUES (3,2);

-- End Of SQL





Similar Threads
Thread Thread Starter Forum Replies Last Post
MySQL version for sql.phpm [Chapter 7] shegxzyl BOOK: Professional PHP 5 ISBN: 978-0-7645-7282-1 0 December 2nd, 2009 01:57 PM
Chapter 05 CMS: username + pass not working in IIS trieuanhl BOOK: ASP.NET 2.0 Instant Results ISBN: 978-0-471-74951-6 5 March 13th, 2009 04:50 PM
Beginning Visual C# Exercises - Chapter 05 seblake C# 1 July 26th, 2004 07:40 AM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.