How to find the `.sql` files in this book’s code archive?

SitePoint Library PHP & MySQL: Novice to Ninja, 7th Edition
1

The author says “you can use the .sql files in this book’s code archive as database snapshots” and that “the database.sql file will change depending on which sample you’re viewing.”

I believe the code archive is found at https://github.com/spbooks/phpmysql7

I see that each chapter is represented by a branch, such as https://github.com/spbooks/phpmysql7/tree/MySQL-DeleteJoke. But the database.sql isn’t present (and would have existed a directory up from where the git commit was made, I think).

I have tried downloading a branch as a zip file, and tried cloning the enter repository and switching branches (with git branch … ). But I found no database.sql file.

How do I find the database.sql file(s) in this book’s code archive?

2 Likes
2

@TomB, Do you happen to know where we can find the .sql files in this repo? I can’t seem to find any in the number of branches I looked at.

3

Did anyone get an answer on this? I can’t seem to find any of the sql files anywhere on Github.

4

@camelCase in the even of @TomB not being around, is there anyone else who can help?

1 Like
5

No joy here either.

6

The creation statements, as best i can tell, are all contained on pages 204/205. Tom’s book appears to rely somewhat on the docker image already having the schema and mysql user pre-loaded.

For those unwilling to type out the schema…this, as best I can determine with all of 10 minutes’ reading at 5 in the morning, is the database setup as of the end of Chapter 5.

CREATE DATABASE IF NOT EXISTS `ijdb`;
USE `ijdb`;

DROP TABLE IF EXISTS `joke`;
DROP TABLE IF EXISTS `author`;

CREATE TABLE `joke` (
  `id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  `joketext` TEXT,
  `jokedate` DATE NOT NULL,
  `authorid` INT
) DEFAULT CHARACTER SET utf8 ENGINE=InnoDB;

CREATE TABLE `author` (
  `id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  `name` VARCHAR(255),
  `email` VARCHAR(255)
) DEFAULT CHARACTER SET utf8 ENGINE=InnoDB;

INSERT INTO `author` (`id`,`name`,`email`) VALUES (1,"Kevin Yank","thatguy@kevinyank.com"), (2, "Tom Butler", "tom@r.je");

INSERT INTO `joke` (`joketext`,`jokedate`,`authorid`) VALUES ("Why did the programmer quit his job? He didn't get arrays","2021-04-01",1), ("Why was the empty array stuck outside? It didn't have any keys","2021-04-01",2);

CREATE USER IF NOT EXISTS 'ijdbuser'@'%' IDENTIFIED BY 'mypassword';
GRANT ALL PRIVILEGES ON `ijdb`.* TO 'ijdbuser'@'%';
FLUSH PRIVILEGES;
1 Like