Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > PHP/MySQL > BOOK: Professional PHP6
Password Reminder
Register
Register | FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read
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 tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developersí questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
 
 
Thread Tools Display Modes
  #1 (permalink)  
Old April 4th, 2013, 11:27 AM
Authorized User
Points: 69, Level: 1
Points: 69, Level: 1 Points: 69, Level: 1 Points: 69, Level: 1
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Sep 2012
Location: Indiana, United States
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
 


Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off

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 12: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



All times are GMT -4. The time now is 03:40 AM.


Powered by vBulletin®
Copyright ©2000 - 2017, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.