SQL JOINs y u no do

So I'm working on the Flower Town database/script, trying to write queries for more detailed reports, which so happen to include the things that the player hasn't yet done.  So, LEFT JOIN, right?

Well, sorta.  It works, but not completely.

Background: I have a table with two columns: breed_id and color_id.  They're foreign keys to a table of all breeds of the game's flowers and a table of all colors of the game's flowers, respectively.  It's indexed by the combination of the two, which is always unique.  This table contains data that never actually changes, grows, or shrinks; it's used as a reference for a second identically structured table.  This second table's contents reflect what the user has actually accomplished in-game.  Normal usage is totally fine, I can pull out anything about what the user has already done.  So, it would be logical that, using LEFT JOINs, I could easily pull out data on what the user still needs to do, right?

I said "sorta" above for a reason.  So long as both breed_id AND color_id are NULL, it works beautifully.  Related to a few other table columns here and there, I can get data back on things the user has yet to do in-game.

Because most breeds can be grown in multiple colors, though, it doesn't work completely.  I am finding myself unable to get data on which colors the user has yet to grow for a given breed, if they've grown some but not all colors for that breed.  I've mangled the JOINs and table relations and WHERE clause every way I can possibly think of, including trying to split certain parts off into subqueries here and there, all to no avail.

I may know SQL, but I'm no SQL god.  If anyone happens to be reading this who IS an SQL god (or, at the very least, knows more than me), by all means, drop some knowledge in the comments.  Relevant info: it's MySQL, because this is web development and I'm not an RDBMS snob.

Flower Town Progress Tracker

I may or may not have mentioned this a while back, but I wrote a small PHP/SQL application to keep track of what I'd done in Flower Town, since I found the game's journal to be inadequate for presenting me with the kind of information I wanted to see.

Well, after what feels like forever since I finished Flower Town, I remembered about the application I wrote, modified it slightly to make it suitable for distribution, and packaged it on up.

Within the archive you'll find an extremely rough around the edges PHP script and set of MySQL tables.  The important thing, however, is that when combined on a server with Apache, mod_rewrite, PHP, and MySQL installed, they'll work together to let you track your progress in Flower Town.

Unfortunately, it isn't multi-user.  I wanted to make it multi-user, but writing the login system was taking time away from writing the actual application itself, so I hastily made it single-user with basic HTTP authentication.

Anyway, you're either begging for the link, or going "what a nerd, writing this nonsense to track progress in a streetpass game".  If you're of the latter disposition, GTFO.  If you're of the former disposition, however, here you go.  Enjoy.

Note: if the sentence above that mentioned the requirement for an Apache web server with mod_rewrite, PHP, and MySQL installed didn't catch your attention, then perhaps this will: This wasn't designed with the average user in mind.  It was designed by me, for me, on my already pre-configured server.  If the setup process seems like too much for you, then it probably is.  Unless of course you're looking for a learning experience in this sort of thing, in which case, go right ahead!

Edit (2016-03-03): Immediately upon resetting it so I could track progress on my second system, I noticed that it was full of bugs relating to there being either nothing or very little in the database.  I've fixed those on my own copy (not on the download above), but I'm adding some more features and will do a new post once the super shiny version 2.0 is ready for release.  Because, despite what I believed at the time, the version I released in this post wasn't actually ready for release...

What new features?  Well... that's just going to have to be a surprise.  I'll guarantee one thing: they'll be useful.

StreetPass Games Done

That certainly took a while.  Having a HomePass setup definitely made it quicker.  Some people call HomePass cheating, but I don't.  It doesn't reduce the challenge of accomplishing everything, nor does it reduce the amount of work you have to do.  It simply lowers the barrier to entry.

So, everything ever:

• Puzzle Swap: 1637/1637 pieces (for North America)
• When I originally posted this, there were 1177 puzzle pieces.
• When I got my New 3DS and system transferred to it, there were 1502 puzzle pieces.
• Find Mii and Find Mii 2: All accomplishments (including "Clear Find Mii in 30 Miis or less") and hats/outfits.
• Mii Force: All plaza tickets (this implies all lost treasures collected, all target scores beaten, and all stages beaten without losing a pod).
• Flower Town: All plaza tickets, gold on all jobs, 80/80 breeds, 305/305 colors, gold watering can.
• Warrior's Way: All plaza tickets, Castle rank 20, 9,999,999 troops (set up so that an army of 50k+ infantry, 50k+ cavalry, and a small group of archers can defeat it by spying twice, field conditions allowing).
• Monster Manor: All plaza tickets, beat floor 50 and the true final boss for the hat.
• Ultimate Angler: All plaza tickets (this implies 160/160 species caught)
• Battleground Z: All plaza tickets, all rare zombies defeated
• StreetPass Birthdays: All plaza tickets (this implies 366/366 birthdays)
• Exchange Booth: All hats/outfits and speech balloons claimed, Exchange Booth closed once again

I'll have more work to do with each Puzzle Swap panel that Nintendo releases, and of course if they add any more games.  With each new panel I'll try and remember to keep the piece count updated, as well.  But until the next new panel or game, feels good man.

Side note: if Puzzle Swap and the Find Mii games can detect when there's nothing left for the player to do and decide to not show their exclamation points with each group of StreetPasses, why can't the DLC StreetPass games do the same?

Edit (2015-09-25): Added Ultimate Angler, Battleground Z, and StreetPass Birthdays, just to mark them off as complete.  Because I now have two 3DSes, this post is only representative of my original efforts, which I system transferred from my Old 3DS to my New 3DS.  Any new puzzles will be marked here until I finish everything on my Old 3DS once again, and then this post will be retired and a new one encompassing both systems will be created.

Back to HomePassing

Yeah, that didn't take too long.  I've got the basics up and running.  Nothing fancy, because for that I need to use SQL LEFT JOINs, and they currently hate me and refuse to return the data I want.  I need to sit down and logic out all the stuff, I guess.

Anyway, I've got a simple stats page showing my progress in four different ways: breeds grown , breed-colors grown, and breaking down breed-colors by color and family.

Overall I'm a lot closer to being done than I thought I was.  I have a mere 63 breed-colors left to grow.  I just started in on the big one: I had 16 breeds left that needed to be grown in White.  Next time I get a brown Mii, which happens occasionally thanks to some random Japanese guy, I can plant all four of the breeds that I need in Brown.  The rest of it goes similarly: get a mii with a shirt color that I need, plant a bunch of stuff, grow it all, repeat.

This mess of PHP and MySQL is just to keep track of it all so I can stay headed in the right direction.  For one thing, the statistics I can pull out of the database give me a much better idea of what I have remaining than flipping through the journal does.  My original strategy of buying seeds from Poppy's Seeds, growing them, and then recursing through all the seeds that resulted in new colors got me a fair amount of progress before it suddenly stopped producing seeds that gave new colors.  Growing strictly by color is my new strategy that I'm taking to the end.

In Find Mii 2 news, I got the last hat, so now I have all the hats.  I also beat it for the tenth time, so I'm completely done with it.  In Find Mii 1 news, I'm following a guide I found on GameFAQs for getting the "Clear Find Mii in under 30 Miis" accomplishment.  It's going exactly to plan thus far, so we'll see how things go when all I have to do is hire random level 7s and spam attacks.  Because, you know, the whole "chance to miss" thing.

So now the number of games I need to play with each group of HomePasses is down to just one: Flower Town.  You can add a new Puzzle Swap panel anytime, Nintendo.  Or how about more StreetPass games, plaza tickets, and hats, so I can re-open the Exchange Booth?  It'd be neat to see a StreetPass racing game.  Just saying.

Small Break from HomePassing

I'm at the point where I have relatively little to do in the StreetPass games.  I've got one hat left in Find Mii 2 Secret Quest, and I'm working towards growing all breeds in all possible colors (which I refer to as breed-colors) in Flower Town.

With the former, I have an image guide that serves as a checklist, that I've loaded into Photoshop with a ton of layers that I can enable to check off individual things and easily track my progress.  Once I'm done, I'll see about uploading my Photoshop files somewhere.  However, with the latter, the game's best way of letting you gauge progress is flipping through the journal, which doesn't really provide me with the information I need to see what my next priority is.

I've been doing well with the strategy of just growing random plants and saving seeds that'll get me new colors, but it's reached a breakpoint where the returns are diminishing rapidly.  From here on out, I'll need to have Miis with specific shirt colors to plant seeds with.

That's why I'm taking a bit of a break from HomePassing.  During the break, I'm working on a small PHP/MySQL application to help me track which breeds I've grown in which colors.  With a database employed, it'll be really easy to write up a few queries for different reports on my progress, for instance, so I can find out how many brown breed-colors I have left so I can plant them all the next time I get a brown Mii.  Because brown is the rarest Mii color, and anyone who says otherwise doesn't know what they're talking about.

I got kinda sidetracked with the idea of adding user accounts and making it multi-user in case I ever wanted to make it internet-facing, but I'm going to back out from doing that, erase all that lovely login code I wrote, and just stick to HTTP authentication.  It's far simpler, and I don't need the system to be multi-user anyway.

I consider the hard part to be done already.  I went through the Flowers/Items list FAQ on GameFAQs and entered all the breed color information using a form I wrote for the purpose.  One thing that made it a bit harder: I decided to order the colors going counter-clockwise around the wheel because of the nice gradient from white to pink.  The FAQ ordered them going clockwise.  However, being a database, I ran a few queries and am fairly certain I don't have any errantly-flagged breed-colors anywhere.  Or at least, if I do, I have a complimentary error elsewhere that offsets it, and the likelihood of that is fairly low.

It's definitely an uncommon thing to do, making an entire database and set of PHP scripts just to track progress in a game that's designed to be played in short sittings, but... that's how I roll.  This gold watering can is gonna be mine.

Finagling Flower Town

Flower Town seems like it's just about growing flowers, but it's got a fair amount of complexity.  Embarrassingly enough I forgot about one of the breeding mechanics recently, that made getting the seed for my 80th breed take a lot longer than it should have.

The key mechanic to getting new breeds is referred to in-game as "Divergence".  Simply put, when you breed a plant with another plant in the same family, there's a chance of getting a seed that will grow a completely different plant from that family.

However, the color of the plant matters.  You know that color wheel that shows when one of your guests waters your plant and you harvest a seed?  Your plant's color will be lit up, their plant's color will be lit up, and all the colors in between on the shortest path will also light up.  If the two colors are directly across the color wheel from each other, all the colors will light up.  The result plant from that specific seed can be any one of the colors that light up.

If you're going for regular breeds, it's a bit easier since they typically have several possible colors.  However, for the single-color regular breeds, or any one of the rare breeds, you'll need to double-check that you have the correct color.  Especially if you're trying to use Divergence within one of the families of rare breeds.  It might not always be possible.

Another thing that you have to pay close attention to is seed shape.  Each individual breed will always have the same seed shape.  Using this knowledge and looking at the Planter's Preview, you can often tell in advance which of the plants it will grow into.  Don't forget to flip back and forth between the possible plants and the parent plants.  If you're trying to get a new breed, it will help you determine when you've got a seed that will grow that breed if you start with a seed of a different shape.  Consult your journal to see the shape of a seed for a breed that you've already seen one of your guests carrying.  This will also give you a starting point for the plant color.

New breeds always show up in silhouette, with the text "New Breed".  New colors will always have the text "New Color".  The preview for a seed will update with every plant you grow, so check back to see if you have any seeds that you can sell.  Also, each guest can help you grow a seed in the color of their shirt.  If the plant can grow in that color, it will grow in that color.  If it can't it searches along the color wheel for the nearest color.  Either way, it helps you influence the color of the plant.

When buying seeds from Poppy's Seeds, you can only buy seeds for breeds you've already grown.  However, the seeds from Poppy's Seeds will always grow in the same style and color.  There are a few jobs where you can simply buy the seed from Poppy's Seeds, grow it, and turn it in to get gold.

Jobs often require plants to have specific attributes.  While some of them, such as romantic, pointy, round, or fragrant, can be discerned just by reading the journal, the height of the plant isn't as easy to discern.  Every plant can grow in a variety of different styles and heights, that you simply have to breed into it.  To discern the plant's height, the first thing you'll want to do is always plant using the terracotta pots, at least initially.  Once you've grown the plant, update its picture in your journal.  Now look at the picture in the journal.  See how the pages have lines on them?  The plant gets scaled to fit neatly into the page, so the taller plants will appear to have a smaller pot than the smaller plants.  Generally speaking, if the pot is at least two lines tall, it's a small plant, and if it's less, it's a tall plant.  It might take two or three attempts to breed the proper breed, style, and color together, so keep at it.

You can sometimes use the sell price of a seed to determine what it will grow into.  Particularly with seeds that show a rare breed as one of the possible plants.  Seeds for rare breeds generally go for a lot more, though it's not always the case.  It's a good indicator of when a seed will grow into a rare breed, but not a good indicator of when a seed will not grow into a rare breed.

Using all of this breeding knowledge, it would theoretically be possible to get all 80 breeds grown with your first 80 seeds planted.  You'd have to get lucky, especially with the rare breeds, but it could happen.  Having a second 3DS that's already got all 80 breeds will significantly increase the chances, since you can just grow whatever you want on that 3DS and hold the plant when you want to get a seed on the first 3DS.

As far as making money goes, selling fully grown plants will turn you a profit.  You might find it worthwhile to grow a few seeds that won't get you anything new, just to sell the plant once it grows.  At a certain point in the game, you get a free shopping spree from Garden Grace, that can be exploited to get a pure profit.  Simply pick all the most expensive items that are available, and then after you check out, go back to Garden Grace and sell them all.

StreetPass Games Revisited: Flower Town

I have every single plaza ticket available in Flower Town.  Getting them isn't incredibly difficult, and you can finish them long before finishing all the jobs.  That said, I'm still playing the game, because I'm going for journal completion.

Flower Town is definitely the underdog of the four additional StreetPass games.  On the surface, it looks like it's just a simple game where all you do is grow flowers, but once you get into it, there's a lot of thought and logic you'll have to put into what you're growing in order to get what you want and/or need.  Learning how to read the Planter's Preview and interpret seed shapes as well as their resale value is critical for getting the breeds you want.  The percentages it shows are mostly for show, because the seed has basically already "picked" one of the potential results and will grow into that plant with 100% certainty.

Likewise, once you get to the jobs from Flower Power, you'll find yourself needing to get plants that are either short or tall, and the game has absolutely no indication of what constitutes a short plant versus a tall plant.  Every place where you can look at a plant will adjust the camera or the scale of the plant's picture to fit it into view.  You'll have to use the size of the pot shown in the picture to determine how much the image of the plant has been scaled.  You'll often find yourself having to buy a seed from Poppy's Seeds and breed it with a plant that has the height you want, and then grow the result seeds to get the breed you want with the height you need.  Sometimes you need a plant of a specific color, but that's comparitively simple since it just requires that you StreetPass a Mii with that shirt color.  You then select that Mii and ask them to plant a seed with you, and they'll inject some magical plant-DNA-altering dye into the pot after you've planted the seed.

Much like Mii Force, Flower Town will simply ignore that you're holding R at some points.  Also, here, I feel as though the speed of the game when you're holding R should be the base speed, and holding R should make it go even faster.

This game is unique in that you can start it up and do things in it without having to StreetPass anyone or spend play coins.  The only thing restricted to StreetPasses or spending play coins is actually growing the flowers.  You can visit the shops and buy and sell things all you want.  You can go rearrange and decorate your gardens all you want.  You can take a job and turn in a plant for that job.  Or if for whatever reason you want to talk to Mr. Mendel, you can do that too.  The ability to play portions of the game without StreetPasses might not be a regularly-used thing, but it's important to note that it's there.

The goal of the game shifts.  Initially, the goal is to grow 20 different breeds of plants, but that's just the beginning.  Normally in these games, you get their second hat after you beat them, but you get Flower Town's second hat when you grow your 20th breed, and the game's far from over at that point.  After you grow your 20th breed, the next task is to take and complete jobs from Flower Power.

A small handful of jobs will present you with a choice of seeds, one of which will always complete the job.  For most jobs, however, you'll need to get the seed on your own, which involves either breeding it, or buying it from Poppy's Seeds.  Sometimes, hilariously enough, you can use a seed purchased directly from Poppy's Seeds to complete a job, without having to breed a specific height or ensure a specific color, including the job that you get from Poppy later on.  Couldn't she just grow one of her own seeds?  Not that I mind getting the job completion money...

To be able to buy a seed for a specific breed from Poppy's Seeds, first you must grow the breed in question.  Just once, any height, any color.  I recommend filling in a good portion of the journal before getting heavily into the jobs, just so you're more likely to be able to just buy what you need to complete the job.  You can earn a good chunk of money just by growing seeds harvested from plants and then selling the result plants.  Rare breeds in particular will go for a lot, should you choose to sell one.

Also, since the game thrusts the free Garden Grace shopping spree on you relatively early on, here's a hint: Go through the items and take the most expensive ones.  You know, because they're free.  Then, after you've checked out and you have an incredibly gaudy looking garden, go back to Garden Grace and sell that stuff.  Surprisingly, this works, and it will net you a fair amount of money if you chose pricey enough items.  You can only do this once, though.

The final phase of the game is after you've completed all the jobs and you just have miscellaneous things left in your journal to fill in.  This phase is of course entirely optional, since it involves growing every plant in every single color that each individual plant can grow in, and each plant has specific colors it can be.

Pacing-wise, this game depends heavily on StreetPasses.  If you don't get very many, it will take a lot longer to do anything.  Each plant needs to be watered multiple times before it will bloom.  Every time you StreetPass people, your Mii will water it once, and then each of the people you StreetPassed will water it once.  In addition, for each seed you want to harvest from a plant that's bloomed, it needs to be watered once by a Mii you've gotten via StreetPass.  I recommend finding yourself a Nintendo Zone that will cooperate and give you six StreetPasses every time, which for me at least was somewhat of a point of contention until I set up SpillPass-Pi.  Six StreetPasses is enough to grow almost everything that isn't a rare breed, and enough to harvest the majority of the seeds from a plant.

When StreetPassing someone else who owns Flower Town, they'll be holding whatever plant they're currently growing, and you can select them in the courtyard or shopping mall and see their garden.  The garden you see is the one they've selected as their favorite.

"New" StreetPass Games

They're not exactly new anymore, but they're newer than Puzzle Swap and Find Mii.  A fair amount of people haven't even bothered to get them simply because they're paid DLC for the StreetPass Mii Plaza, and I suspect people generally want to put their money towards bigger titles.  But man, they're missing out.

Available for purchase are Mii Force, Flower Town, Warrior's Way, and Monster Manor.  They're $4.99 each, or you can get all four of them in a bundle for $14.99.  I grabbed the bundle.

Mii Force

In Mii Force, you're galactic police going after space pirates.  It's a sidescrolling shooter, and an interesting one at that.  The people you streetpass give you different weapons based on the color of their shirt, and you can attach them in one of four places on your ship (three front, one back).  In addition, you can stick other people behind them to power their weapons up.  This game plays best when you get a lot of streetpasses.  The Special Miis that Nintendo sends out periodically have an extra gold pod that fires additional shots.

It has the interesting mechanic of being able to rotate your weapons around your ship with the L and R buttons during the stage.  The stages themselves will change direction from time to time, from horizontal to vertical and back, thus necessitating rotating your weapons.  The variety of weapons is amazing and it's incredibly fun to play.  The goal?  Complete all the stages and wipe out the pirates!

You can also spend play coins to recruit troops (including the ability to hire old allies!) and get extra weapon pods, if you don't have a lot of streetpasses available.  The game is fairly challenging with just one pod, enough so that some gamers may want to try it that way and see how far they get.

Worth it?  Definitely.

Flower Town

In Flower Town, you grow flowers, breed new flowers, and expand your garden on your quest to become a master gardener.  This game is full of bright colors and is fairly relaxing to play.  You can also buy decorations for your garden plots, and even take jobs from the local mall to breed specific plants for people.  You can use play coins to invite people you've previously met back to your garden.

Every streetpass you get will help your current flower grow, and once it's fully grown, will help you get seeds that can potentially grow entirely new flowers.  The goal is to get 20 breeds of flowers.

It's definitely not for everyone, but I find it to be interesting.

Worth it?  If you like flowers or want a nice relaxing game, sure.  Otherwise, you may want to hold off, unless you really really want to get the most out of every streetpass.

Warrior's Way

In Warrior's Way, you're a monarch building your army and trying to take over the world.  This is simplified into a rock-paper-scissors strategy game where each troop type is strong against one other and weak against one other.  Every few countries you take over will give you building materials you can use to upgrade your castle to get the ability to spy, or simply even the ability to hire more mercenaries with play coins.  The ability to spy in battle is useful for your strategy, as it tells you what troop type your enemy will attack with next.

This game is unique in that it depends on a portion of the world's owners of 3DSes not purchasing it.  When you streetpass someone who hasn't bought it, they're a general with an army (based on their StreetPass Plaza size), and they ally with you and boost your numbers.  If you streetpass someone who plays the game, though, they're a monarch and you have a choice between greeting them peacefully or fighting their army.  You want to have a fair number of streetpasses for this game, because more streetpasses = more troops.  The Special Miis that Nintendo sends out periodically add a nice chunk of troops.

The goal is simple enough that I've already stated it: take over the world!

Worth it?  Definitely.

Monster Manor

In Monster Manor, you're exploring a haunted mansion.  Each person you streetpass gives you pieces that you can use to reveal parts of the current floor of the mansion.  Join enough pieces of the same color together to make a room with chests (containing items, of course!) in it.  Throughout the mansion you'll encounter ghosts and have to fight them off.

This game has a nice blend of puzzles, exploration, combat, and supernatural horror.  You can use play coins to hire extra investigators to help with the exploration of the mansion.  The goal is to get to the top of the mansion and defeat everything that stands in your way.

Worth it?  Sure!

Overall

The newer streetpass games give you a lot more to do with each streetpass, as well as a lot of new and good uses for play coins.  If you've finished all the puzzles and Find Mii 1 and 2, definitely give them a shot.  Heck, give them a shot even if you haven't.  If you have to choose just one, I would say pick either Mii Force or Warrior's Way.

Price-wise, it appears at first glance like it's a whole two cents cheaper to buy only three of the games ($4.99 * 3 = $14.97) than to buy the bundle ($14.99), but the bundle makes all the games $3.75 each instead of $4.99 each.  This means you effectively end up spending more by buying three games at the full price than buying the bundle!  The difference?  $3.75 * 3 = $11.25, $14.97 - $11.25 = $3.72.  With buying three games costing almost an entire game more than simply buying the bundle, it makes no sense to buy anything other than the bundle.

Also, there's a hidden ranking in my "Worth it?" lines above.  Definitely > Sure! > Flower Town.  Flower Town isn't bad, it's just not for everyone.  All the games are good enough to be worth the price, so if you're not going to grab the bundle, it all comes down to your gaming preferences.