04_Multiple Tables
CHG
In this “book” we will have:
Databases involving multiple tables.
Inserting into the table from the webpage.
You will need to know how to Query them and Display them as results.
You may want to make a new database just for your join examples
Grade Examples
Set up a new Database, and create the 2 tables in the Grades Example.
I’ll link the slide and the code in the next Slide. The Code is in the Speaker notes.
Try the following queries:
Print all Grades from Peter
Print all Grades from Alice
Print all Chemistry results that are over 90
Join Example #1 (Code in comments)
We are going to have 2 tables set up.
One is a list of students.
One is a list of grade entries for a test and the grade they are given.
There are 2 students, Peter and Alice
The Slide Comments have the code to set up the 2 tables.
Hobbies Example
Run the code in the comments to create a table with people and their hobbies. This will create a database that looks like the one on the right —>
Then add 1 more person and 1 more hobby to that person on PHPMyAdmin
Adding to Hobbies and People with PHP
Here is a link to the example. You should adjust it into your own database.
You’ll need to copy it into your personal folder on the server.
https://drive.google.com/file/d/1iQrsU7CG7rgQSbD6T7ZBzisvDunrgg74/view?usp=sharing
Writing Join Queries
Write a query to display:
Every person and their hobbies.
Every Hobby that Bobby has.
Everyone that has coding as an hobby.
KKC Extracurricular Example
That is a link to a 3-page spreadsheet with the extracurricular activities example.
Import the THREE tables, and then do the queries on the following slide.
Also include a column for the LAST NAMES of teachers.
This is how Mr Chuang has named the tables:
KKC Extracurricular Queries Basic Queries
Have a few common .php files for the following query:
All students CHG is involved with.
All students CAR is involved in
List all students with 3 options, sorting by name, activity and teachers.
KKC Adding Students and Teachers to DB
Make a .php form to allow adding of students and teachers.
You should see that queries update as you add new ones in!
KKC Extracurricular Queries #1
Have a main website that will have 3 main features.
The Teachers feature will allow queries to find extracurricular activities and see who is running them. Support BOTH teacher code searching and name.
The Students feature should allow for queries to find a particular student with a text search.
The Extracurricular Feature to query for ALL the students for the specified extra-curricular activity.
You may want to have some hard-coded ones first, then a page for each text-based search
KKC Extracurricular Queries #2
Implementing adding to the “tic” TABLE and the “students” TABLE
To add a Extracurricular Activity, a teacher name, teacher code and Activity name should be supplied to a form.
To add a Student, they just need the student name and extracurricular activity.
Implement UPDATING Extracurricular Activities to change Teachers for them.
KKC Extracurricular Queries Extension
Now here is all the fun stuff…
Teacher Codes must be 3 letters. How do we ensure for valid inputs?
A student should only be able to be entered if they have a activity that a teacher is offering.
Assignment
MTG Set tracker.
A hobby store is keeping track of their individual cards. Individual cards can have value.
You may also think of this task as someone with an individual collection as well!
They also want to be able to browse and search through the set. As a whole.
Facts of Magic The Gathering
Each card has a colour associated with it. The 5 main colors are:
In the database, all cards will have a colour associated with it.
Some cards will have more than 1 colour and even 0 colours! (Great edge cases!)
About the datafile: https://drive.google.com/file/d/19GCQphfWgQNkJHuoOlP0azud9NJBU8hk/view?usp=sharing
There are missing values in the spreadsheet. Mr Chuang has inserted NULL into them.
There are SEVENTEEN columns.
The next slide will have more details on what the data means and some explanation on the game.
While you might have 0 interests in children card games, being a good programmer is understanding the context! You will have to learn the rules/behaviours of things that will work!
What do the cards actually look like?
Information on the data
Multiverse_id | Unique id for the Magic Card |
Rarity | Cards can be common, uncommon, rare, mythic |
Mana Cost | The mana needed to cast the spell. Generally lower cost spells are “weaker”, higher costs are “stronger” |
CMC | This is “converted mana cost”, which is if we add all the costs and their colours together, this is the result. |
type_line | All cards have monster or spell types. You don’t need to know them, just that people might want to search them. Such as all “dragon” type cards. |
Other information on the Game
The colour of the card is dependent on the Manacost. So if a cost has {R}, it is likely going to be red card.
HOWEVER, cards can be multicoloured!
Colour Identifiers:
R - Red
B - Black
W - White
G - Green
U - Blue (Why is it NOT B?)
Preliminary Tasks
Set up the website so you can browse all the cards in the database.
Allow users to query for type, colours, rarity and CMC.
Allow users to use a text search on BOTH card name and artists.
You can have it just display as a list in a table.
See if you can display the card images in the query as well! Bonus points for arranging it in a nice grid.
Tasks involving Joins
Set up a page to query from a table to show all the cards the store currently owns. It should display the card, the value for the card and the quantity we have.
All cards the shop has 4 or more copies of.
Allow the user to search through the cards we own with similar queries to before. Make sure to sort it sensibly as well!
Display the total value for the cards that the store currently has.
You should use temporary data for this part.
Second Table - Adding to Stock
Define and create a second table to represent the shop stock. Allow the user to add to it, it can be a primitive way where we just have a count and the corresponding ID of the card.
Extension - Changing stock
The store may sell individual cards or buy them from people. Those will affect the stock. Have a page to allow adding to stock, and removing stock.
Think about how you want the user to UPDATE the data! How are they going to find the correct card to adjust?
Extension 2 - Better Display
Look into how you can use your web development knowledge to build the .php page so that when a user hovers over the card name, it will preview the image of the card. This way, we can display the list and not have large rows going down each page!
Challenges #1
That is the advanced search scryfall uses. Mr Chuang has also pulled his data from scryfall.
Make your own Advanced Search.
How many features can you implement?
Challenges #2
Players of the game can build “decks” which contains 60 cards.
Allow users to create and save “decks” and use the stock of the shop to determine how many of each card we are missing for the deck.
To make it even more insane, decklists can be written in their own text format. This will make it easier to import a deck into the database.
Here is an example: https://www.mtggoldfish.com/deck/arena_download/1029081
Is it possible to implement this from only adding input through the website?