Welcome to the Treehouse Community

Want to collaborate on code errors? Have bugs you need feedback on? Looking for an extra set of eyes on your latest project? Get support with fellow developers, designers, and programmers of all backgrounds and skill levels here with the Treehouse Community! While you're at it, check out some resources Treehouse students have shared here.

Looking to learn something new?

Treehouse offers a seven day free trial for new students. Get access to thousands of hours of content and join thousands of Treehouse students and alumni in the community today.

Start your free trial

Development Tools

Posted by Ted Sumner
Ted Sumner
Courses Plus Student 17,967 Points

mySQL database project.

I have a tricky project. I am creating a site for my son's Cub Scout Pack where I want to track progress toward earning badges. It is turning into quite a tricky project for me. One of the key pieces of the project is that it will be easy to update when there are program changes. Right now I intend to have a table for each of the badges and a table that contains a list of the scouts. I want to be able to select each scout and click check boxes by each element of each badge requirement and have the information stored per scout. I also want to update just one table when the requirements for the badge change. My problem is figuring out how to store the information for each scout in a way that references another table for the columns. Any ideas are appreciated.

After I get that part solved, I have to figure out how to record something is complete if a certain number of a subset is complete. I have a pretty good idea how to do this, though with fields in the database that tell code what to do.

Christian Andersson
Christian Andersson
8,712 Points

Hey Ted, can elaborate on what you mean by

I want to be able to select each scout and click check boxes by each element of each badge requirement and have the information stored per scout?

From what I understand you want to have a list of scouts (users) and badges with each user having a list of earned badges? Again, I'm not sure what you want to do with the requirement and how that requirement affects the system.

Ted Sumner
Ted Sumner
Courses Plus Student 17,967 Points

I want to be able to track progress toward completion of a badge. For example, the Bobcat badge has 7 requirements. For the Bobcat badge, I want to call the scout and see the requirements for the badge. If any of the requirements are already complete, I want that marked. Then the user can click a check box and indicate a requirement is done.

It is more complicated than just that because the other badges have some requirements and some options. So, the Wolf badge has 7 requirements and one option. The requirements are similar to the Bobcat badge, but the option is different. There are 10 choices of which only one has to be completed. To complete the choice, the scout has to complete multiple steps, some required, some you have to do 2 of 3. I have some ideas on the PHP logic to display and analyze the steps, it is the storage of the progress that I am struggling with.

I am currently thinking one table for each badge and would like to reference the badge table for the columns . The structure (subject to change to make the coding work) is a mandatory column, optional column, and columns that give the information to trigger the PHP code to determine if the proper number of options are complete for each step. I think there will be columns to reference which subset it goes with, but I have not completed my thoughts on that part.

Let me know if this makes sense.

2 Answers

Christian Andersson
Christian Andersson
8,712 Points

To remove your excess comma from your string you can simply use:

rtrim($col, ",")

when you're at the last iteration of your loop.

But again, it's generally a bad practise to dynamically create tables in a database. Google it and you'll see many people advising against it in nearly all cases.

Unfortunately MySQL isn't the best solution to store a tree-like structure of tasks like you have, but there are decent ways of implementing it anyway.

Let's just focus on badges and its tasks for now. First off, I'd create a table that stores all the badges. The columns you'd want here are basic; a unique identifier, a name and perhaps column for the description of the badge. Something along those lines.

As for each task, you could have a table (let's call it Badge_tasks) that stores all the tasks, and sub-tasks for each badge:

id --- unique identifier
bg_id --- points to which badge this task belongs to
parent_id --- points to the parent task if this is a sub-task (null otherwise)
task_type --- stores what kind of task this is (required or elective)
name
description

With this solution, you can store an indefinite amount of branches to each task. Here is how this database table would look like if your example above was implemented:

img

So for example, the steps "do 1" and "do 2" show that they are elective (meaning that you can choose to pick another one from the same branch, AKA same parent-id) and their parent is the step with id 16. Step with ID 16 is the "Step 2 (do one of the following)" step and is a child of the 9th-step. And the iteration goes on. I'm sure this makes sense to you, but just in case, here is another picture:

img

One thing I missed out on in the picture is a column showing how many steps are required to do in each elective branch. So the step "Step 2 (do one of the following)" would have a column indicating that ONE sub-branch needs to be fullfilled.

That should probably work.

Ted Sumner
Ted Sumner
Courses Plus Student 17,967 Points

That should work. And to update, I could search by the appropriate subset, delete the rows, and add the new ones.

Christian Andersson
Christian Andersson
8,712 Points

Hey, you're welcome...

Christian Andersson
Christian Andersson
8,712 Points
Christian Andersson
Christian Andersson
8,712 Points

Alright, I think I have a rough idea now of what you want to do. So here is how I would design the database:

You'd obviously need a table for storing all the individual scouts. You probably wouldn't need more than a user_id and a username.

user_id *
username

You'd probably want the user_id to be the primary key and have auto_increment.

Then I'd create a table to store all the badges:

badge_id *
badge_name

Same as with the user table. Put the id as primary key and have it increment automatically.

Next up, a requirements table:

req_id *
req_name
req_description

You can now have a table that links these together; which scout has what badge, and what badge needs what requirement:

usr_bg_id *
user_id
badge_id

So if user with id 10 has badges 1, 3 and 7 and if user 20 has badges 1, 2, and 9, the table would look like this:

----------------------------------
| usr_bg_id | user_id | badge_id |
----------------------------------
|     1     |   10    |     1    |
|     2     |   10    |     3    |
|     3     |   10    |     7    |
|     4     |   20    |     1    |
|     5     |   20    |     2    |
|     6     |   20    |     9    |
----------------------------------

You'd want to set the following properties to this table: 

ALTER TABLE user_badges ADD CONSTRAINT FK_usr_id FOREIGN KEY (user_id) REFERENCES users(user_id) ON UPDATE CASCADE ON DELETE CASCADE

Do the same thing for the badges column. You'd also have to set all the tables to run the INNO DB engine.

From my understanding, what you describe as "option" is basically the same as a requirement but with the difference that you can decide which option you want to complete. Right?

If so, you could do a similar thing as above and then in php just check that at least one option for that user and for that badge exists in that table.

It's a fairly complicated system you got there, but I think I got a rough idea of what you want to make. If not, or if you have any questions make sure to let me know.

Ted Sumner
Ted Sumner
Courses Plus Student 17,967 Points

I will have to really digest your answer. The numerous variables makes this a very interesting problem and a real stretch for me with what I know. I am learning a ton.

I was thinking about this and thought that maybe use PHP to create a table (if it doesn't already exist) with columns created from the badge requirements and columns and using the scout id as its rows. Then the program creates any necessary table for the badge/scout completion. The scout table has columns for each badge that update with the % complete for the badge based on the entries stored in the badge/scout table. The status of each requirement is stored in the badge/scout table.

Here is the description for the Tiger badge (for 1st graders). They have to do all of the required adventures and one elective. I particularly like it when they say do items 1-3 and one of 4-6. And #5 has complete a and 3 of b through g.

TIGER REQUIRED ADVENTURES

Tiger Adventure: Backyard Jungle

  1. Take a 1-foot hike. Make a list of the living things you find on your 1-foot hike.
  2. Point out two different kinds of birds that live in your area.
  3. Be helpful to plants and animals by planting a tree or other plant in your neighborhood.
  4. Build and hang a birdhouse.
  5. With your adult partner, go on a walk, and pick out two sounds you hear in your “jungle.”

Tiger Adventure: Games Tigers Play

  1. Do the following: a. Play two initiative or team-building games with the members of your den. b. Listen carefully to your leader while the rules are being explained, and follow directions when playing. c. At the end of the game, talk with the leader about what you learned when you played the game. Tell how you helped the den by playing your part.
  2. Make up a game with the members of your den.
  3. Make up a new game, and play it with your family or members of your den or pack.
  4. While at a sporting event, ask a player or coach why he or she thinks it is important to be active.
  5. Bring a nutritious snack to a den meeting. Share why you picked it and what makes it a good snack choice.

Tiger Adventure: My Family’s Duty to God Complete requirement 1 and at least two from requirements 2–4.

  1. With your adult partner, find out what duty to God means to your family.
  2. Find out what makes each member of your family special.
  3. With your family, make a project that shows your family’s beliefs about God.
  4. Participate in a worship experience or activity with your family.

Tiger Adventure: Team Tiger

  1. List the different teams of which you are a part.
  2. With your den, make a den job chart that shows everyone doing something to help. As one of the den jobs, lead the Pledge of Allegiance at a den meeting.
  3. Pick two chores you will do at home once a week for a month.
  4. Make a chart to show three ways that members of your Tiger team are different from each other.
  5. Do an activity to help your community or neighborhood team.

Tiger Adventure: Tiger Bites

  1. Identify three good food choices and three foods that would not be good choices.
  2. Show that you know the difference between a fruit and a vegetable. Eat one of each.
  3. With your adult partner, pick a job to help your family at mealtime. Do it every day for one week.
  4. Show you can keep yourself and your personal area clean.
  5. Talk with your adult partner about what foods you can eat with your fingers. Practice your manners when eating them.
  6. With your adult partner, plan and make a good snack choice or other nutritious food to share with your den.

Tiger Adventure: Tigers in the Wild

  1. With your adult partner, name and collect the Cub Scout Six Essentials you need for a hike. Tell your den leader what you would need to add to your list if it rains.
  2. Go for a short hike with your den or family, and carry your own gear. Show you know how to get ready for this hike.
  3. Do the following: a. Listen while your leader reads the Outdoor Code. Talk about how you can be clean in your outdoor manners. b. Listen while your leader reads the Leave No Trace Principles for Kids. Discuss why you should “Trash Your Trash.” c. Apply the Outdoor Code and Leave No Trace Principles for Kids on your Tiger den and pack outings. After one outing, share what you did to demonstrate the principles you discussed.
  4. While on the hike, find three different kinds of plants, animals, or signs that animals have been on the trail. List what you saw in your Tiger Handbook.
  5. Participate in an outdoor pack meeting or pack campout campfire. Sing a song and act out a skit with your Tiger den as part of the program.
  6. Find two different trees and two different types of plants that grow in your area. Write their names in your Tiger Handbook.
  7. Visit a nearby nature center, zoo, or another outside place with your family or den. Learn more about two animals, and write down two interesting things about them in your Tiger Handbook.

TIGER ELECTIVE ADVENTURES Tiger Elective Adventure: Curiosity, Intrigue, and Magical Mysteries

  1. Learn a magic trick. Practice your magic trick so you can perform it in front of an audience.
  2. Create an invitation to a magic show.
  3. With your den or with your family, put on a magic show for an audience.
  4. Create a secret code.
  5. With the other Scouts in your den or with your family, crack a code that you did not create.
  6. Spell your name using sign language, and spell your name in Braille.
  7. With the help of your adult partner, conduct a science demonstration that shows how magic works.
  8. Share what you learned from your science demonstration.

Tiger Elective Adventure: Earning Your Stripes

  1. Bring in and share with your den five items that are the color orange.
  2. Demonstrate loyalty over the next week at school or in your community. Share at your next den meeting how you were loyal to others.
  3. With your adult partner, decide on one new task you can do to help your family, and do it.
  4. Talk with your den and adult partner about polite language. Learn how to shake hands properly and introduce yourself.
  5. Play a game with your den. Then discuss how your den played politely.
  6. With your adult partner and den, work on a service project for your pack’s meeting place or chartered organization.

Tiger Elective Adventure: Family Stories

  1. Discuss with your adult partner and/or family where your family originated. Discuss their history, traditions, and culture—your family heritage. Share a story or bring something to share with your den about yourself and your family.
  2. Make a family crest.
  3. Visit your public library to find out information about your heritage.
  4. Interview one of your grandparents or another family elder, and share with your den what you found.
  5. Make a family tree.
  6. Share with your den how you got your name or what your name means.
  7. Share with your den your favorite snack or dessert that reflects your cultural heritage.
  8. Learn where your family came from, and locate it on a map. Share this information with your den . With the help of your adult partner, locate and write to a pen pal from that location.

Tiger Elective Adventure: Floats and Boats

  1. Identify five different types of boats.
  2. Build a boat from recycled materials, and float it on the water.
  3. With your den, say the SCOUT water safety chant.
  4. Play the buddy game with your den.
  5. Show that you can put on and fasten a life jacket the correct way.
  6. Show how to safely help someone who needs assistance in the water, without having to enter the water yourself.
  7. Show how to enter the water safely, blow your breath out under the water, and do a prone glide.

Tiger Elective Adventure: Good Knights

  1. Do the following: a. With your den or adult partner, say the Scout Law. Explain to your den one of the 12 points of the Law and why you think a knight would have the same behavior. b. If you have not already done so, make a code of conduct with your den that will describe how each person should act when you are all together. If your den has a code of conduct, discuss with your den the updates it might need. Vote on which actions should go in your den code of conduct.
  2. Create a den shield and a personal shield.
  3. Using recycled materials, design and build a small castle with your adult partner to display at the pack meeting.
  4. Think of one physical challenge that could be part of an obstacle course. Then help your den design a Tiger knight obstacle course. With your adult partner, participate in the course.
  5. Participate in a service project.

Tiger Elective Adventure: Rolling Tigers

  1. With your den or adult partner, discuss two different types of bicycles and their uses.
  2. With your den or adult partner, try on safety gear you should use while riding your bike. Show how to wear a bicycle helmet properly.
  3. With your den or adult partner, learn and demonstrate safety tips to follow when riding your bicycle.
  4. Learn and demonstrate proper hand signals.
  5. With your den or adult partner, do a safety check on your bicycle.
  6. With your den or family, go on a bicycle hike wearing your safety equipment. Follow the bicycling safety and traffic laws.
  7. Learn about a famous bicycle race or famous cyclist. Share what you learn with your den.
  8. Visit your local or state police department to learn about bicycle-riding laws.
  9. Identify two jobs that use bicycles.

Tiger Elective Adventures: Sky Is the Limit

  1. With your den or adult partner, go outside to observe the night sky. Talk about objects you see or might see.
  2. Look at a distant object through a telescope or binoculars. Show how to focus the device you chose.
  3. Observe in the sky or select from a book or chart two constellations that are easy to see in the night sky. With your adult partner, find out the names of the stars that make up the constellation and how the constellation got its name. Share what you found with your den.
  4. Create and name your own constellation. Share your constellation with your den.
  5. Create a homemade constellation.
  6. Find out about two different jobs related to astronomy. Share this information with your den.
  7. Find out about two astronauts who were Scouts when they were younger. Share what you learned with your den.
  8. With your den or family, visit a planetarium, observatory, science museum, astronomy club, or college or high school astronomy teacher. Before you go, write down questions you might want to ask. Share what you learned.

Tiger Elective Adventures: Stories in Shapes

  1. Visit an art gallery or a museum, explore an art website, or visit your library. Do each of the following: a. Look at pictures of some abstract art with your den or family. Decide what you like about the art, and share your ideas with the other Tigers. b. Create an art piece.
  2. Do the following: a. Draw or create an art piece using shapes. b. Use tangrams to create shapes.

Tiger Elective Adventure: Tiger-iffic! Complete 1–3 and one from 4–6.

  1. Play at least two different games by yourself; one may be a video game.
  2. Play a board game or another inside game with one or more members of your den.
  3. Play a problem-solving game with your den.
  4. With your parent’s or guardian’s permission: a. Play a video game with family members in a family tournament. b. List at least three tips that would help someone who was learning how to play your favorite video game. c. Play an appropriate video game with a friend for 30 minutes.
  5. With other members of your den, invent a game, OR change the rules of a game you know, and play the game.
  6. Play a team game with your den.

Tiger Elective Adventure: Tiger: Safe and Smart

  1. Do the following: a. Memorize your address, and say it to your den leader or adult partner. b. Memorize an emergency contact’s phone number, and say it to your den leader or adult partner. c. Take the 911 safety quiz.
  2. Do the following: a. Show you can “Stop, Drop, and Roll.” b. Show you know how to safely roll someone else in a blanket to put out a fire.
  3. Make a fire escape map with your adult partner.
  4. Explain your fire escape map, and try a practice fire drill at home.
  5. Find the smoke detectors in your home. With the help of your adult partner, check the batteries.
  6. Visit an emergency responder station, or have an emergency responder visit you.

Tiger Elective Adventures: Tiger Tag

  1. Choose one active game you like, and tell your den about it.
  2. Do the following: a. Play two relay games with your den and your adult partner. b. Tell your adult partner or the other Tigers what you liked best about each game. c. Have your den choose a relay game that everyone would like to play, and play it several times.
  3. With your adult partner, select an active outside game that you could play with the members of your den. Talk about your game at the den meeting. With your den, decide on a game to play.
  4. Play the game that your den has chosen. After the game, discuss with your den leader the meaning of being a good sport.

Tiger Elective Adventures: Tiger Tales

  1. Create a tall tale with your den.
  2. Create your own tall tale. Share your tall tale with your den.
  3. Read a tall tale with your adult partner.
  4. Create a piece of art from a scene in the tall tale you have read, using your choice of materials. Share it with your den.
  5. Play a game from the past.
  6. Sing two folk songs.
  7. Visit a historical museum or landmark with your adult partner.

Tiger Elective Adventures: Tiger Theater

  1. With your den, discuss the following types of theater: puppet shows, reader’s theater, and pantomime.
  2. As a den, play a game of one-word charades with your adult partners.
  3. Make a puppet to show your den or display at a pack meeting.
  4. Perform a simple reader’s theater. Make a mask afterward to show what your character looks like.
  5. Watch a play or attend a story time at a library.
Christian Andersson
Christian Andersson
8,712 Points

I didn't read through your entire reply, but I think I got the idea for your system.

I would really advice against dynamically creating tables as well as creating tables for each individual badge. The best practise is always to have a static database with static tables and for each change in the system you only have to add one or more rows to the appropriate tables.

I could be of more help if you could simplify the system in your example. Can you give me an example of your system assuming there is only 2 badges and a couple of requirements / options for each badge? It's hard for me to visualize this 100% from only a textual representation of a rather complex system. There are numerous things that you call "Tiger Adventure". To get the tiger badge you have to complete each of these adventures, and for each adventure you have to complete each task?

What's the "elective adventures"?

Ted Sumner
Ted Sumner
Courses Plus Student 17,967 Points

Ok. Lets say there are two badges (there are really only 6). There is no duplication in the steps or choices in the badges.

Badge 1:

Required Task 1 (called adventures)

a. step 1 required

b. step 2 required

Required Task 2

a. step 1 required

b. step 2 complete one of two choices

a. choice 1

b. choice 2

Elective task (complete 2 of three choices)

a. choice 1

  1. Step 1

  2. Step 2

b. choice 2

  1. Step 1

  2. Step 2 (do one of the following)

A. do 1

B. do 2

c. choice 3

  1. Step 1

  2. Step 2

Badge 2: similar structure as badge 1, but none of the steps in badge 2 are the same as those in badge 1.

The structure of the database needs to be balanced with the PHP used to display and record progress. I thought is to design the database so that there is no updating of the PHP required when the program changes. Thus, I was thinking of a column (1 or more depending on how the PHP works out) that is called by the PHP to tell it if there are options and how many option have to be done. That way I can have limited PHP objects that are called in response to what is in the database for any badge. But we also have to know what the relationships are in the database.

This is my current progress on making the tables for each badge. It doesn't work, but I think it is close. The tables are not really dynamic I think because it only creating the table once. I guess I could run a similar code in phpMyAdmin but I don't know that I can run actual PHP. I could really just run this code once to create the tables, and then delete it. Or I could run it to echo out the create command and run the create command in phpMyAdmin to create the tables.

<?php
$reqs = requirements_db ($table); // this pulls the mandatory and optional columns from the table listing those.  That table would have columns used by the PHP to analyze the various options in each badge.
    $number = count($reqs);
    $i = 0;  // artifact from the while loop below
    $col = "";
    foreach ($reqs as $req) {
//      while ($i<$number){
            $col .= '"' . $req['mandatory'] . '"' . ' (VARCHAR 300), ';
//          $i++;
//      }
//      if ($i === $number) {
//          $col .= $req[0] . ' (VARCHAR 300)';
//      }

//      var_dump($req);
        echo '<br>' . $col . '<br><br>';
    }
    unset ($req);
try {
    $sql = "CREATE TABLE IF NOT EXISTS " . $table . "_reqs (" . $col . ")";
    $db->exec($sql);
    echo $table . "_reqs created.";

} catch (Exception $e) {
    echo $e->getMessage() . ' dbCreate badge table creation.';
    exit;
}

One problem I have with the code is there is an extra comma at the end of the columns that I don't know how to get rid of. That was the idea of the while statement, but it wasn't working. I fixed some of the problems but have not retried the while loop. The logic seems wrong with the foreach loop. Also, this code is only for a badge with set requirements and no options. I wanted to get this to work before I tackled options. It also does not have the foreign key with the Cub Scout's id.

The $table variable would have the badge table and create the new table with the badge name_reqs. That table would have scout ids and record boolean (probably) if the specific requirement was complete. the PHP would then evaluate if the badge requirements were met.

The scout table currently has First and Last names, den (used for sorting scouts into age groups), a column for each badge (intended to store % complete on the badge requirements), health issues for the scout, and money earned toward summer camp. I was going to have a separate table for parents and their contact information after considering your previous post.

The real program is described in a 43 page PDF. Clicking here will download the PDF.